T-SQL (Transact-SQL) is Microsoft SQL Server’s extension of standard SQL. It adds variables, control flow, error handling, procedural logic, and server-side objects — essential for stored procedures, ETL, and complex reporting.

Batches and GO

T-SQL executes in batches separated by GO (a client directive, not T-SQL):

  USE MyApp;
GO

DECLARE @x INT = 1;  -- Must be first in batch
SELECT @x;
GO

-- New batch — @x is out of scope here

GO 10 repeats the batch 10 times (SSMS/sqlcmd only).

Variables and Assignment 

  DECLARE @user_id INT = 42;
DECLARE @name NVARCHAR(100);
DECLARE @total DECIMAL(18,2);

SELECT @name = Name, @total = Balance
FROM dbo.Accounts
WHERE AccountID = @user_id;

SELECT @user_id AS user_id, @name AS name, @total AS balance;

Use SET for single assignment; SELECT can assign multiple variables in one statement.

Conditional Logic 

  DECLARE @stock INT = 5;

IF @stock > 0
    PRINT N'In stock';
ELSE
    PRINT N'Out of stock';

IF @stock > 100
    SELECT N'High' AS level;
ELSE IF @stock > 0
    SELECT N'Low' AS level;
ELSE
    SELECT N'Empty' AS level;

PRINT outputs to Messages tab — not returned to application clients. Use SELECT for result sets.

WHILE Loops 

  DECLARE @i INT = 1;

WHILE @i <= 5
BEGIN
    INSERT INTO dbo.AuditLog (Message)
    VALUES (N'Iteration ' + CAST(@i AS NVARCHAR(10)));

    SET @i = @i + 1;
END;

Avoid row-by-row loops on large datasets — set-based operations scale better.

CASE Expressions 

  SELECT
    Name,
    Age,
    CASE
        WHEN Age < 18 THEN N'Minor'
        WHEN Age < 65 THEN N'Adult'
        ELSE N'Senior'
    END AS age_group,
    CASE Status
        WHEN N'A' THEN N'Active'
        WHEN N'I' THEN N'Inactive'
        ELSE N'Unknown'
    END AS status_label
FROM dbo.Users;

Simple CASE matches equality; searched CASE evaluates predicates.

Common Functions 

  SELECT
    SYSUTCDATETIME() AS utc_now,
    NEWID() AS guid,
    ISNULL(Email, N'N/A') AS email,
    LEN(Name) AS name_len,
    LEFT(Name, 10) AS name_prefix,
    CONCAT(FirstName, N' ', LastName) AS full_name,
    FORMAT(CreatedAt, N'yyyy-MM-dd') AS created_date
FROM dbo.Users;
Category Examples
Date/time GETDATE(), DATEADD, DATEDIFF, EOMONTH
String TRIM, STRING_SPLIT, REPLACE, CHARINDEX
Conversion CAST, CONVERT, TRY_CAST, TRY_CONVERT
Aggregate COUNT, SUM, AVG, STRING_AGG (2017+)

TRY/CATCH Error Handling 

  BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    SELECT
        ERROR_NUMBER() AS error_number,
        ERROR_SEVERITY() AS severity,
        ERROR_STATE() AS state,
        ERROR_PROCEDURE() AS procedure_name,
        ERROR_LINE() AS line,
        ERROR_MESSAGE() AS message;
END CATCH;

Use THROW; (SQL Server 2012+) to re-raise without losing original error context.

Table Variables vs Temp Tables 

  -- Table variable — statistics limited, good for small sets
DECLARE @recent TABLE (
    OrderID INT PRIMARY KEY,
    Total DECIMAL(18,2)
);

INSERT INTO @recent (OrderID, Total)
SELECT TOP 10 OrderID, Total
FROM dbo.Orders
ORDER BY CreatedAt DESC;

SELECT * FROM @recent;

-- Temp table — statistics, indexes, better for larger intermediate results
CREATE TABLE #staging (
    ProductID INT,
    Qty INT,
    INDEX IX_product (ProductID)
);

INSERT INTO #staging
SELECT ProductID, SUM(Quantity)
FROM dbo.OrderItems
GROUP BY ProductID;

SELECT * FROM #staging;
DROP TABLE #staging;
Feature Table Variable Temp Table (#)
Statistics Row estimate often 1 Full statistics
Indexes PK only (inline since 2014) Full index support
Scope Batch/procedure Session
Best for Small lookups Large intermediate sets

Cursors (Use Sparingly) 

  DECLARE @id INT, @name NVARCHAR(100);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT CustomerID, Name FROM dbo.Customers WHERE Active = 1;

OPEN cur;
FETCH NEXT FROM cur INTO @id, @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Prefer set-based logic instead
    FETCH NEXT FROM cur INTO @id, @name;
END;

CLOSE cur;
DEALLOCATE cur;

Cursors serialize work — refactor to JOINs or window functions when possible.

Dynamic SQL 

  DECLARE @table NVARCHAR(128) = N'dbo.Orders';
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) FROM ' + QUOTENAME(@table) + N';';

EXEC sp_executesql @sql;

Always use sp_executesql with parameters — never concatenate user input directly (SQL injection risk).

Common Mistakes

Mistake Problem Fix
Missing SET NOCOUNT ON in procedures Extra DONE_IN_PROC messages to apps Add at procedure start
SELECT @var = col with multiple rows Undefined which row assigns Use TOP 1 with ORDER BY
Comparing NULL with = Never matches Use IS NULL / IS NOT NULL
Implicit conversion in WHERE Index scans Match parameter type to column
Huge loops Hours of runtime Rewrite set-based

Performance Tips

  • Prefer set-based INSERT/UPDATE over cursors
  • Use temp tables when intermediate row counts exceed ~100 rows
  • Avoid scalar UDFs in SELECT lists (pre-2019 — forced serial plans)
  • Mark read-only procedures when possible for optimization hints

Production Scenario: Idempotent Seed Script 

  MERGE dbo.Settings AS target
USING (VALUES (N'max_retries', N'3')) AS source (KeyName, Value)
ON target.KeyName = source.KeyName
WHEN MATCHED AND target.Value <> source.Value THEN
    UPDATE SET Value = source.Value, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED THEN
    INSERT (KeyName, Value) VALUES (source.KeyName, source.Value);

Troubleshooting

Issue Diagnostic
Variable NULL unexpectedly Assignment failed — no matching row
Batch errors on DECLARE DECLARE must start batch
Wrong date results datetime vs datetime2; timezone awareness
Procedure not found Schema qualify: EXEC dbo.MyProc 
  -- Find procedure definition
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.MyProc'));

T-SQL bridges querying and procedural logic — master it before diving into stored procedures, triggers, and advanced transaction control.