Transact‑SQL Syntax Conventions

This page provides the recommended conventions when writing Transact‑SQL (T‑SQL) statements for Microsoft SQL Server. Following these guidelines improves readability, maintainability, and reduces errors.

Identifiers

Use descriptive and consistent naming for database objects. Follow these rules:

• Use PascalCase for tables, views, and procedures (e.g., CustomerOrders).
• Use camelCase for column names (e.g., orderDate).
• Prefix temporary tables with # or ## as appropriate.
• Avoid reserved keywords unless they are delimited with square brackets.

Keywords

Uppercase all T‑SQL keywords to make them stand out.

SELECT TOP (10) *
FROM dbo.Customers
WHERE Country = N'USA'
ORDER BY CustomerID DESC;

Literals

Use the appropriate prefix for string and binary literals:

• Unicode strings: N'string'
• Binary literals: 0xFFEE
• Date literals: 'YYYY-MM-DD' (ISO‑8601 format)

Comments

Prefer -- for single‑line comments and /* ... */ for block comments. Keep comments concise and relevant.

-- Retrieve the top 5 customers by sales
SELECT TOP (5) CustomerID, SUM(Amount) AS TotalSales
FROM dbo.Sales
GROUP BY CustomerID
ORDER BY TotalSales DESC;

/* 
   This procedure calculates yearly revenue.
   It accepts a fiscal year as input.
*/
CREATE PROCEDURE dbo.usp_CalculateRevenue
    @FiscalYear INT
AS
BEGIN
    -- implementation
END;

Formatting

Adopt a consistent formatting style:

• Indent nested clauses by 4 spaces.
• Place each clause (SELECT, FROM, WHERE, etc.) on its own line.
• Align column lists vertically.

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE OrderDate >= '2024-01-01'
  AND Status = N'Completed'
ORDER BY OrderDate DESC;

Example Scripts

Below are practical examples that illustrate the conventions described above.

1. Creating a Table

CREATE TABLE dbo.ProductCatalog (
    ProductID   INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(120) NOT NULL,
    CategoryID  INT NOT NULL,
    Price       MONEY NOT NULL,
    CreatedDate DATETIME2(0) CONSTRAINT DF_ProductCatalog_CreatedDate DEFAULT SYSDATETIME()
);

2. Inserting Data

INSERT INTO dbo.ProductCatalog (ProductName, CategoryID, Price)
VALUES
    (N'Wireless Mouse', 3, 24.99),
    (N'Gaming Keyboard', 3, 79.95),
    (N'HD Monitor', 2, 199.00);

3. Updating with a CTE

WITH UpdatedPrices AS (
    SELECT
        ProductID,
        Price * 1.10 AS NewPrice
    FROM dbo.ProductCatalog
    WHERE CategoryID = 3
)
UPDATE pc
SET pc.Price = up.NewPrice
FROM dbo.ProductCatalog pc
JOIN UpdatedPrices up ON pc.ProductID = up.ProductID;

For more guidance, see the Best Practices page.