sqlserver-coding

SQL Server T-SQL Coding Standards

Naming Conventions

Element	Convention	Example
Table	PascalCase, no prefix	Orders, OrderItems
View	vw_ prefix, PascalCase	vw_ActiveCustomers
Stored Procedure	usp_ prefix, PascalCase	usp_GetOrderById
Function	fn_ prefix, PascalCase	fn_CalculateTotal
Column	PascalCase	FirstName, CreatedAt
Index	IX_<Table>_<Column(s)>	IX_Orders_CustomerId
Primary Key	PK_<Table>	PK_Orders
Foreign Key	FK_<Child>_<Parent>	FK_OrderItems_Orders
Default	DF_<Table>_<Column>	DF_Orders_CreatedAt
Check	CK_<Table>_<Column>	CK_Orders_Total

Stored Procedure Pattern

CREATE OR ALTER PROCEDURE [dbo].[usp_GetOrderById]
    @OrderId INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        SELECT
            o.Id,
            o.CustomerName,
            o.Total,
            o.CreatedAt
        FROM dbo.Orders o
        WHERE o.Id = @OrderId;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
END;

Error Handling

• Always use TRY...CATCH in stored procedures and multi-statement scripts
• Use THROW (not RAISERROR) for re-throwing in new code
• For custom errors, use THROW with error number >= 50000
• Log errors before re-throwing when appropriate

BEGIN TRY
    BEGIN TRANSACTION;
    -- operations here
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    -- Optionally log error
    INSERT INTO dbo.ErrorLog (ErrorMessage, ErrorProcedure, ErrorLine)
    VALUES (ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE());
    THROW;
END CATCH;

Transaction Patterns

• Use explicit transactions for multi-statement operations
• Always check @@TRANCOUNT before ROLLBACK
• SET XACT_ABORT ON at the start of stored procedures
• Use appropriate isolation levels — READ COMMITTED default

Table Creation Pattern

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Orders' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE [dbo].[Orders] (
        [Id]           INT            IDENTITY(1,1) NOT NULL,
        [CustomerName] NVARCHAR(200)  NOT NULL,
        [Total]        DECIMAL(18,2)  NOT NULL DEFAULT 0,
        [Status]       NVARCHAR(50)   NOT NULL DEFAULT 'Pending',
        [CreatedAt]    DATETIME2(7)   NOT NULL DEFAULT SYSUTCDATETIME(),
        [CreatedBy]    NVARCHAR(100)  NOT NULL,
        [ModifiedAt]   DATETIME2(7)   NULL,
        [ModifiedBy]   NVARCHAR(100)  NULL,
        CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([Id]),
        CONSTRAINT [CK_Orders_Total] CHECK ([Total] >= 0)
    );
END;

Indexing

• Clustered PK on identity columns by default
• Non-clustered on FK columns and common WHERE/JOIN columns
• Use INCLUDE for covering indexes
• Use filtered indexes for partial datasets

CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId]
    ON [dbo].[Orders] ([CustomerId])
    INCLUDE ([CustomerName], [Total])
    WHERE [Status] != 'Deleted';

Views

CREATE OR ALTER VIEW [dbo].[vw_ActiveCustomers]
AS
    SELECT
        c.Id,
        c.Name,
        c.Email,
        COUNT(o.Id) AS OrderCount
    FROM dbo.Customers c
    LEFT JOIN dbo.Orders o ON c.Id = o.CustomerId AND o.Status != 'Cancelled'
    GROUP BY c.Id, c.Name, c.Email;

Security

• Parameterized queries always — never concatenate user input into SQL
• Use QUOTENAME() for dynamic identifiers
• Use sp_executesql for parameterized dynamic SQL
• Create application roles with least privilege