Getting started with Microsoft SQL Server Introduction Topic

Examples

• 32

  Basic DML Operations in MS SQL Server

  edit move example delete share

  32

  DML ("Data Manipulation Language") includes operations such as INSERT, UPDATE and DELETE:

  -- Create a table variable, which can be treated like a normal table
  
  DECLARE @HelloWorld TABLE (
      ID INT IDENTITY,
      Description VARCHAR(1000)
  )
  
  
  -- DML Operation - Insert, Inserting a row into the table
  INSERT INTO @HelloWorld (Description) VALUES ('Hello, World!')
  
  
  -- Displaying the table 
  SELECT * FROM @HelloWorld  
  
  -- Select a specific column from table
  SELECT Description FROM @HelloWorld
  
  
  -- Display number of records in the table
  SELECT Count(*) FROM @HelloWorld
  
  
  -- DML Operation - Update, Updating a specific row in the table
  UPDATE @HelloWorld SET Description = 'Hello World!' WHERE ID = 1
  
  
  -- Selecting the table. See how the DESCRIPTION changed after the UPDATE?
  SELECT * FROM @HelloWorld
  
  
  -- DML Operation - Delete, Deleting a row from the table
  DELETE FROM @HelloWorld WHERE ID = 1
  
  
  -- Selecting the table. See table content after DELETE operation 
  SELECT * FROM @HelloWorld
  

  In this script we're creating a table variable to demonstrate some basic queries.

  The @ symbol in the example above needs to be used as a prefix for all variables you declare in T-SQL. Its purpose is to distinguish between a variable and a table, e.g.

  USE Northwind;
  GO
  SELECT TOP 10 * FROM Customers 
  ORDER BY CompanyName
  

  will select the first 10 records of the Customer table, ordered by the column CompanyName from the database Northwind:

  

  Note that Use Northwind; changes the default database for all subsequent queries. You can still reference the database by using the fully qualified syntax:

  SELECT TOP 10 * FROM Northwind.dbo.Customers 
  ORDER BY CompanyName
  
  SELECT TOP 10 * FROM Pubs.dbo.Authors
  ORDER BY City
  

  This is useful if you're querying data from different databases. Note that dbo, specified "in between" is called a schema and needs to be specified always when using the fully qualified syntax. If it is not changed, dbo is the default schema. The default schema may be omitted. All other user defined schemas need to be specified.

  If the database table contains columns which are named like reserved words, e.g. Date, you need to enclose the column name in brackets, like so:

  -- descending order
  SELECT TOP 10 [Date] FROM dbo.MyLogTable
  ORDER BY [Date] DESC
  

  The same applies if the column name contains spaces in its name (which is not recommended). An alternative syntax is to use double quotes instead of square brackets, e.g.:

  -- descending order
  Select top 10 "Date" from dbo.MyLogTable
  order by "Date" desc 
  

  is equivalent but not so commonly used. Notice the difference between double quotes and single quotes: Single quotes are used for strings, i.e.

  -- descending order
  Select top 10 "Date" from dbo.MyLogTable
  order by "Date" desc 
  where UserId='johndoe'
  

  is a valid syntax. Notice that T-SQL has a N prefix for NChar and NVarchar data types, e.g.

  SELECT TOP 10 * FROM Northwind.dbo.Customers 
  ORDER BY CompanyName
  WHERE CompanyName like N'AL%'
  

  returns all companies having a company name starting with AL (% is a wild card, use it like you would use the asterisk in a DOS command line, e.g. DIR AL*).

  edited Dec 29 '16 at 13:46

  

  

  

  

  

  +13
• 10

  SELECT all rows and columns from a table

  edit move example delete share

  10

  Syntax:

  SELECT *
  FROM table_name
  

  Using the asterisk operator * serves as a shortcut for selecting all the columns in the table. All rows will also be selected because this SELECT statement does not have a WHERE clause, to specify any filtering criteria.

  This would also work the same way if you added an alias to the table, for instance e in this case:

  SELECT *
  FROM Employees AS e
  

  Or if you wanted to select all from a specific table you can use the alias + " .* ":

  SELECT e.*, d.DepartmentName
  FROM Employees AS e
      INNER JOIN Department AS d 
          ON e.DepartmentID = d.DepartmentID
  

  Database objects may also be accessed using fully qualified names:

  SELECT * FROM [server_name].[database_name].[schema_name].[table_name]
  

  This is not necessarily recommended, as changing the server and/or database names would cause the queries using fully-qualified names to no longer execute due to invalid object names.

  Note that the fields before table_name can be omitted in many cases if the queries are executed on a single server, database and schema, respectively. However, it is common for a database to have multiple schema, and in these cases the schema name should not be omitted when possible.

  edited Jul 27 '16 at 21:07

  

  

  

  

  

  +1
• 7

  UPDATE Specific Row

  edit move example delete share

  7

  UPDATE HelloWorlds
  SET HelloWorld = 'HELLO WORLD!!!'
  WHERE Id = 5
  

  The above code updates the value of the field "HelloWorld" with "HELLO WORLD!!!" for the record where "Id = 5".

  Note: In an update statement, It is advised to use a "where" clause to avoid updating the whole table unless and until your requirement is different.

  edited Dec 30 '16 at 9:17

  

  

  

  

  
• 4

  Comments in code

  edit move example delete share

  4

  Transact-SQL supports two forms of comment writing. Comments are ignored by the database engine, and are meant for people to read.

  Comments are preceded by -- and are ignored until a new line is encountered:

  -- This is a comment
  SELECT *
  FROM MyTable -- This is another comment
  WHERE Id = 1;
  

  Slash star comments begin with /* and end with */. All text between those delimiters is considered as a comment block.

  /* This is
  a multi-line
  comment block. */
  SELECT Id = 1, [Message] = 'First row'
  UNION ALL
  SELECT 2, 'Second row'
  /* This is a one liner */
  SELECT 'More';
  

  Slash star comments have the advantage of keeping the comment usable if the SQL Statement loses new line characters. This can happen when SQL is captured during troubleshooting.

  Slash star comments can be nested and a starting /* inside a slash star comment needs to be ended with a */ to be valid. The following code will result in an error

  /*
  SELECT *
  FROM CommentTable
  WHERE Comment = '/*'
  */
  

  The slash star even though inside the quote is considered as the start of a comment. Hence it needs to be ended with another closing star slash. The correct way would be

  /*
  SELECT *
  FROM CommentTable
  WHERE Comment = '/*'
  */  */
  

  edited Aug 3 '16 at 17:48

  

  

  

  
• 4

  PRINT

  edit move example delete share

  4

  Display a message to the output console. Using SQL Server Management Studio, this will be displayed in the messages tab, rather than the results tab:

  PRINT 'Hello World!';
  

  edited Sep 29 '16 at 7:08

  

  

  

  
• 4

  Select rows that match a condition

  edit move example delete share

  4

  Generally, the syntax is:

  SELECT <column names>
  FROM <table name>
  WHERE <condition>
  

  For example:

  SELECT FirstName, Age
  FROM Users
  WHERE LastName = 'Smith'
  

  Conditions can be complex:

  SELECT FirstName, Age
  FROM Users
  WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')
  

  created Jul 21 '16 at 15:19

  
• 2

  DELETE All Rows

  edit move example delete share

  2

  DELETE
  FROM Helloworlds
  

  This will delete all the data from the table. The table will contain no rows after you run this code. Unlike DROP TABLE, this preserves the table itself and its structure and you can continue to insert new rows into that table.

  Another way to delete all rows in table is truncate it, as follow:

  TRUNCATE TABLE HelloWords
  

  Difference with DELETE operation are several:

  1. Truncate operation doesn't store in transaction log file
  2. If exists IDENTITY field, this will be reset
  3. TRUNCATE can be applied on whole table and no on part of it (instead with DELETE command you can associate a WHERE clause)

  edited Dec 29 '16 at 13:46

  

  

  

  

  
• 1

  Create new table and insert records from old table

  edit move example delete share

  1

  SELECT * INTO NewTable FROM OldTable
  

  Creates a new table with structure of old table and inserts all rows into the new table.

  created Aug 3 '16 at 17:48

  
• 1

  Retrieve Basic Server Information

  edit move example delete share

  1

  SELECT @@VERSION
  

  Returns the version of MS SQL Server running on the instance.

  SELECT @@SERVERNAME
  

  Returns the name of the MS SQL Server instance.

  SELECT @@SERVICENAME
  

  Returns the name of the Windows service MS SQL Server is running as.

  edited Dec 7 '16 at 5:24

  

  
• 1

  UPDATE All Rows

  edit move example delete share

  1

  A simple form of updating is incrementing all the values in a given field of the table. In order to do so, we need to define the field and the increment value

  The following is an example that increments the Score field by 1 (in all rows):

  UPDATE Scores
  SET score = score + 1  
  

  This can be dangerous since you can corrupt your data if you make accidentally an UPDATE for a specific Row with an UPDATE for All rows in the table.

  edited Aug 16 '16 at 12:45

  

  

  

  
• 0

  Getting Table Row Count

  edit move example delete share

  0

  The following example can be used to find the total row count for a specific table in a database if table_name is replaced by the the table you wish to query:

  SELECT COUNT(*) AS [TotalRowCount] FROM table_name;
  

  It is also possible to get the row count for all tables by joining back to the table's partition based off the tables' HEAP (index_id = 0) or cluster clustered index (index_id = 1) using the following script:

  SELECT  [Tables].name                AS [TableName],
          SUM( [Partitions].[rows] )    AS [TotalRowCount]
  FROM    sys.tables AS [Tables]
  JOIN    sys.partitions AS [Partitions]
      ON  [Tables].[object_id]    =    [Partitions].[object_id]
      AND [Partitions].index_id IN ( 0, 1 )
  --WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
  GROUP BY    [Tables].name;
  

  This is possible as every table is essentially a single partition table, unless extra partitions are added to it. This script also has the benefit of not interfering with read/write operations to the tables rows'.

  created Aug 3 '16 at 17:49

  
• 0

  TRUNCATE TABLE

  edit move example delete share

  0

  TRUNCATE TABLE Helloworlds 
  

  This code will delete all the data from the table helloworlds. Truncate table is almost similar to Delete from Table code. The difference is that you can not use where clauses with Truncate. Truncate table is considered better than delete because it uses less transaction log spaces.

  Note that if an identity column exists, it is reset to the initial seed value (for example, auto-incremented ID will restart from 1). This can lead to inconsistency if the identity columns is used as a foreign key in another table.

  edited Dec 7 '16 at 5:24

  

  

  

  
• 0

  Using Transactions to change data safely

  edit move example delete share

  0

  Whenever you change data, in a Data Manipulation Language(DML) command, you can wrap your changes in a transaction. DML includes UPDATE, TRUNCATE, INSERT and DELETE. One of the ways that you can make sure that you're changing the right data would be to use a transaction.

  DML changes will take a lock on the rows affected. When you begin a transaction, you must end the transaction or all objects being changed in the DML will remain locked by whoever began the transaction. You can end your transaction with either ROLLBACK or COMMIT. ROLLBACK returns everything within the transaction to its original state. COMMIT places the data into a final state where you cannot undo your changes without another DML statement.

  Example:

  --Create a test table
  
  USE [your database]
  GO
  CREATE TABLE test_transaction (column_1 varchar(10))
  GO
  
  INSERT INTO 
   dbo.test_transaction
          ( column_1 )
  VALUES
          ( 'a' )
  
  BEGIN TRANSACTION --This is the beginning of your transaction
  
  UPDATE dbo.test_transaction
  SET column_1 = 'B'
  WHERE column_1 = 'A'
  
  SELECT * FROM dbo.test_transaction  --This line will show you what changed
  
  ROLLBACK TRANSACTION  --Rollback will undo your changes
             --Alternatively, use COMMIT to save your results
  
  SELECT * FROM dbo.test_transaction   --View the table after your changes have been run
  
  DROP TABLE dbo.test_transaction
  

  created Jul 29 '16 at 1:06