Getting started with Microsoft SQL Server

Examples

• 18

  Basic "Hello World" Operations

  edit move example delete share

  18

  A basic "Hello World" example, making use of a number of SQL operations such as INSERT, SELECT and DELETE:

  -- Create a table variable as an example, 
  -- after this it is treated like any other normal table
  DECLARE @HelloWorld TABLE (
      ID INT IDENTITY,
      Description VARCHAR(1000)
  )
  

  -- Insert a row into the table
  INSERT 
      INTO @HelloWorld (Description) 
      VALUES ('Hello, World!')
  

  -- Select all columns from table in ascending order by field ID
  SELECT * 
      FROM @HelloWorld 
      ORDER BY ID ASC;
  

  -- Select a specific column from table
  SELECT Description 
      FROM @HelloWorld
  

  -- Select the number of records in the table
  SELECT Count(*)
      FROM @HelloWorld
  

  -- Update a specific row in the table
  UPDATE @HelloWorld 
      SET Description = 'Hello World!' 
      WHERE ID = 1
  

  -- Delete a row from the table
  DELETE FROM @HelloWorld 
      WHERE ID = 1
  

  -- No more rows 
  SELECT * 
       FROM @HelloWorld
  

  edited 22 hours ago

  

  

  

  

  

  +6
• 3

  SELECT all rows and columns from a table

  edit move example delete share

  3

  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 at 21:07

  

  

  

  

  

  +1
• 3

  UPDATE Specific Row

  edit move example delete share

  3

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

  In this case when you use UPDATE like this, we will update value of column HelloWorld for record with Id = 5, after you execute this statement new value for record with Id = 5 will be: "HELLO WORLD!!!"

  edited Jul 27 at 6:22

  

  
• 2

  Comments in code

  edit move example delete share

  2

  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.

  edited Jul 29 at 1:16

  

  

  
• 2

  PRINT

  edit move example delete share

  2

  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 Jul 27 at 21:07

  

  

  
• 2

  Select rows that match a condition

  edit move example delete share

  2

  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 at 15:19

  
• 1

  DELETE All Rows

  edit move example delete share

  1

  DELETE
  FROM Helloworlds
  

  This will delete all the data from the table. The table will hold no data after you run this code.

  edited yesterday

  

  

  
• 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.

  created Jul 29 at 1:04

  
• 1

  TRUNCATE TABLE

  edit move example delete share

  1

  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. This can lead inconsistency if the identity columns is used as a foreign key in another table.

  edited yesterday

  

  
• 1

  UPDATE All Rows

  edit move example delete share

  1

  Example Add one point to all:

  UPDATE Scores
  SET score=score+1  
  

  If want to set value for all records in table you can do it without WHERE clause. This can be used only when you want to set value for column or columns for all the records but This is very dangerous and using command like this you can corrupt your data if you make mistake between UPDATE for specific Row and UPDATE for All rows in table.

  edited yesterday

  

  
• 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 at 1:06