Post
Ask Question

Using GO and Semi-Colon in SQL Server 2012

This article contains the most commonly used GO command and semicolon in SQL Server with examples. The GO command is used as a batch separator in the SQL Server Management Studio tool. This article also defines how you can change GO using SSMS. The semicolons are used to terminate SQL statements. So let's have a look at a practical example of how to use GO and semicolon (;) in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  
 

Creating a table in SQL Server

 
Now we create a table named employee:
  1. Create table Employee  
  2. (  
  3.     EmpID int,  
  4.     EmpName varchar(30),  
  5.     EmpSalary int  
  6. )  
The following is the sample data for the employee Table:
 
SQL-Employee-table.jpg 
 

Using GO in SQL Server

 
The GO Statement must be written in a new line as it is not a T-SQL command. GO is not a SQL keyword. It's a batch separator used by the SQL Server Management Studio code editor tool for when more than one SQL Statement is entered in the Query window. Then Go separates the SQL statements. We can say that Go is used as a separator between transact SQL Statements.
Transact SQL Statements Without GO in SQL Server
  1. SELECT TOP 1000 [EmpID]  
  2.       ,[EmpName]  
  3.       ,[EmpSalary]  
  4. FROM [master].[dbo].[Employee]  
  5.   -- Insert Statement  
  6. Insert into [Employee] values('8','Monu',3086)  
  7.    -- Update Statement  
  8. Update  [Employee]  set EmpName='Loren' Where EmpID ='2'  
Now press F5 to execute the above code.  The output will be:
 
Output
 
Without-Go-Transact-statement-in-SQL-Server.jpg
 
Transact SQL Statements With GO in SQL Server
  1. SELECT TOP 1000 [EmpID]  
  2.       ,[EmpName]  
  3.       ,[EmpSalary]  
  4. FROM [master].[dbo].[Employee]  
  5. go  
  6.   -- Insert Statement  
  7. Insert into [Employee] values('8','Monu',3086)  
  8. go  
  9.    -- Update Statement  
  10. Update  [Employee]  set EmpName='Loren' Where EmpID ='2'  
  11. go  
Output
 
With-Go-Transact-statement-in-SQL-Server.jpg
 
How you can change GO?
 
GO is a batch terminator, you can however change it to whatever you want:
 
Tool -> Options
 
changingt-Go-in-SQL-Server.jpg
 
Now click on the options and select Query Execution Tab.
 
changingt-Go-with-new-name-in-SQL-Server.jpg
 

Using semicolon (;) in SQL Server

 
A semicolon is a statement terminator. This is purely used to identify where a particular statement ends. In most cases, the statement syntax itself is enough to determine the end of a statement. Common Table Expression (CTE) is the situation in which you must use the semicolon.
  • By default, SQL statements are terminated with semicolons. You use a semicolon to terminate statements unless you've (rarely) set a new statement terminator. If you're sending just one statement, technically you can dispense with the statement terminator; in a script, as you're sending more than one statement, you need it.
  • CTE's however, demand that the WITH is the first statement so you need a semicolon before the WITH.
SQL Common Table Expression (CTE)
 
SQL Server has a very powerful feature that has been added for the programmer's benefit: Common Table Expression (CTE).  Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query.
 
We have a simple table Employee in our database.
 
Employee-table-in-Sql-Server.jpg
 
Example
  1. ;WITH EmployeeCTE  AS  
  2. ( SELECT  [EmpID]  
  3.       ,[EmpName]  
  4.       ,[EmpSalary]  
  5. FROM [master].[dbo].[Employee]  
  6.   WHERE [EmpSalary]>4000  
  7. )  
  8. SELECT * FROM EmployeeCTE  
Now press F5 to execute.
 
Output
 
CTE-in-Sql-Server.jpg


Similar Articles