Don't Get Lost in Translation: Demystifying Semicolons in T-SQL

In T-SQL (Transact-SQL), the primary purpose of semicolons (;) is to mark the end of a SQL statement. They act as dividers, signifying that the database engine has processed a complete instruction and should be ready to interpret the next one.

Required vs. Recommended Usage

• Mandatory:
  • Common Table Expressions (CTEs): If a CTE isn't the first statement in your T-SQL script, you must terminate the preceding statement with a semicolon.
  • MERGE statement: This statement, which combines INSERT, UPDATE, and DELETE operations, always requires a semicolon at the end.
• Highly Recommended:

Example:

-- Without semicolons (not recommended)
SELECT * FROM Customers
UPDATE Customers SET Name = 'New Name' WHERE CustomerID = 1

-- With semicolons (recommended)
SELECT * FROM Customers;
UPDATE Customers SET Name = 'New Name' WHERE CustomerID = 1;

-- Selecting customer details and order count
SELECT * FROM Customers;

SELECT COUNT(*) AS TotalOrders FROM Orders WHERE CustomerID = 1;

DML statements (INSERT, UPDATE, DELETE):

-- Inserting a new customer
INSERT INTO Customers (Name, Email) VALUES ('John Doe', '[email protected]');

-- Updating customer email
UPDATE Customers SET Email = '[email protected]' WHERE CustomerID = 2;

-- Deleting a customer (assuming proper checks are in place)
DELETE FROM Customers WHERE CustomerID = 3;

Control Flow statements (IF, WHILE, etc.):

DECLARE @age INT = 25;

IF @age >= 18
BEGIN
  -- Grant access to adult content
  PRINT 'Access granted';
END
ELSE
BEGIN
  -- Restrict access
  PRINT 'Access denied';
END;

User-defined functions:

CREATE FUNCTION GetCustomerNameByID(@id INT)
RETURNS NVARCHAR(50)
AS
BEGIN
  DECLARE @name NVARCHAR(50);
  SELECT TOP 1 @name = Name FROM Customers WHERE CustomerID = @id;
  RETURN @name;
END;

1. GO Command (SQL Server Management Studio only):

   • SQL Server Management Studio (SSMS) is a graphical tool for managing SQL Server databases.
   • Within SSMS, you can use the GO command to separate T-SQL statements. The GO command doesn't replace semicolons within your script, but it instructs SSMS to execute the preceding statements as a batch.

   Note: This approach is specific to SSMS and has no effect when executing scripts directly on the server. It's not suitable for writing portable T-SQL code.

2. Changing Statement Terminator (Not recommended):

   • T-SQL allows setting a custom statement terminator using the SET command, like SET STATEMENT_TERMINATOR = '@@'. This would allow you to use "@@" instead of semicolons to mark the end of statements.

   However, this is strongly discouraged for several reasons:

   • Non-standard: It deviates from the standard T-SQL syntax and reduces code portability.
   • Potential Errors: Incorrect usage might lead to unexpected behavior or errors.
   • Configuration Required: This setting needs to be applied on each session, making it inconvenient for collaboration or deployment.