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:

Create table Employee

(

EmpID int,

EmpName varchar(30),

EmpSalary int

)

The following is the sample data for the employee Table:

 

 

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

SELECT TOP 1000 [EmpID]

      ,[EmpName]

      ,[EmpSalary]

FROM [master].[dbo].[Employee]

  -- Insert Statement

Insert into [Employee] values('8','Monu',3086)

   -- Update Statement

Update  [Employee]  set EmpName='Loren' Where EmpID ='2'

Now press F5 to execute the above code.  The output will be:

Output

Transact SQL Statements With GO in SQL Server

SELECT TOP 1000 [EmpID]

      ,[EmpName]

      ,[EmpSalary]

FROM [master].[dbo].[Employee]

go

  -- Insert Statement

Insert into [Employee] values('8','Monu',3086)

go

   -- Update Statement

Update  [Employee]  set EmpName='Loren' Where EmpID ='2'

go

Output

How you can change GO?

GO is a batch terminator, you can however change it to whatever you want:

Tool -> Options

Now click on the options and select Query Execution Tab.

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.

Example

;WITH EmployeeCTE  AS

( SELECT  [EmpID]

      ,[EmpName]

      ,[EmpSalary]

FROM [master].[dbo].[Employee]

  WHERE [EmpSalary]>4000

)

SELECT * FROM EmployeeCTE

Now press F5 to execute.

Output