Part of a series of “Today I Learned”s

T-SQL and the GO command

What is T-SQL

• SQL is a standard, but database vendors don’t implement it identically. Each adds its own extensions and syntax on top of the core spec
• T-SQL (Transact-SQL) is Microsoft’s dialect of SQL, used in SQL Server and Azure SQL Database
  • It extends standard SQL with procedural programming constructs: variables, loops, conditionals, error handling, and stored procedures

-- this is valid T-SQL (and also valid standard SQL)
SELECT name, age FROM users WHERE age > 30;

-- this uses T-SQL-specific syntax: a variable and a conditional
DECLARE @threshold INT = 30;
IF @threshold > 25
    SELECT name FROM users WHERE age > @threshold;

What GO actually is

• GO is not a T-SQL command at all. The SQL Server engine itself has never heard of it
• It’s a batch separator recognised by client tools
  • When you write a script with GO in it, the client tool reads through the file and splits it into chunks wherever it sees GO
  • Each chunk is sent to the server as a separate batch - a single unit of execution
  • The server never sees the GO; it only ever sees the SQL on either side of it

CREATE TABLE orders (id INT, amount DECIMAL);
GO
-- everything above is sent as one batch

INSERT INTO orders VALUES (1, 99.50);
GO
-- this insert is sent as a separate batch

• This matters because some statements in SQL Server have to be the first statement in a batch
  • CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION, and a few others carry this restriction
  • Without GO separating them from previous statements, the server raises an error even if the logic is sound
  • GO is also how you reset the scope of local variables - a DECLAREd variable only lives within its batch

-- this fails: CREATE VIEW must be the first statement in a batch
SELECT 1;
CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1;

-- this works
SELECT 1;
GO
CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1;
GO