SQL

Last reviewed: 2026-09-17

Purpose: SQL querying and management patterns — paged stored procedures, join strategies, string manipulation, and table creation with sensible indexing, using Microsoft SQL Server (T-SQL) for the examples.

Overview

Most SQL work falls into a handful of recurring shapes: reading a page of a large result set, combining tables, reshaping strings, and creating tables that queries can use efficiently. The patterns below are portable in outline to PostgreSQL, MySQL and SQLite; syntax notes call out where T-SQL differs.

Stored Procedures

Paged results with OFFSET / FETCH

Keyset-free paging is the quickest way to expose a large table through an API. OFFSET/FETCH requires an ORDER BY, and a total count is normally returned alongside the page so the caller can render pagination controls.

CREATE OR ALTER PROCEDURE dbo.GetEventsPaged
    @PageNumber  INT = 1,
    @PageSize    INT = 50,
    @TotalRows   INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @TotalRows = COUNT(*) FROM dbo.Event;

    SELECT
        e.EventId,
        e.FeatureName,
        e.HostName,
        e.EventTime
    FROM dbo.Event AS e
    ORDER BY e.EventTime DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

DECLARE @total INT;
EXEC dbo.GetEventsPaged @PageNumber = 2, @PageSize = 25, @TotalRows = @total OUTPUT;
SELECT @total AS TotalRows;

Guard the parameters — a negative page number or an unbounded page size turns a friendly endpoint into a full table scan:

IF @PageNumber < 1 SET @PageNumber = 1;
IF @PageSize   < 1 SET @PageSize = 25;
IF @PageSize   > 500 SET @PageSize = 500;

Notes on paging performance

Approach	Behaviour
OFFSET / FETCH	Simple; cost grows with the offset because skipped rows are still read
Keyset (seek) paging	`WHERE (EventTime, EventId) < (@lastTime, @lastId)` — constant cost per page, needs a matching index
ROW_NUMBER()	Portable to older engines (and to PostgreSQL/MySQL 5.x) but requires a sort of the full set

Keyset paging is the right answer for deep pagination over large tables; OFFSET is fine for the first few pages.

Dynamic filtering

When optional filters make a large permutation of predicates, build the filter once and reuse it, rather than writing a procedure per combination:

DECLARE @sql NVARCHAR(MAX) = N'
    SELECT TOP (@PageSize) EventId, FeatureName, HostName, EventTime
    FROM dbo.Event
    WHERE 1 = 1';
IF @FeatureName IS NOT NULL SET @sql += N' AND FeatureName = @FeatureName';
IF @FromTime    IS NOT NULL SET @sql += N' AND EventTime >= @FromTime';
SET @sql += N' ORDER BY EventTime DESC';

EXEC sp_executesql @sql,
     N'@PageSize INT, @FeatureName NVARCHAR(100), @FromTime DATETIME2',
     @PageSize, @FeatureName, @FromTime;

Always pass values as parameters to sp_executesql — string-concatenated values are the classic injection hole.

Joins

Join	Returns
INNER JOIN	Only rows matching on both sides
LEFT JOIN	All left rows, NULLs where the right side has no match
RIGHT JOIN	Mirror of LEFT (rarely used — prefer reordering)
FULL OUTER JOIN	All rows from both sides, NULLs on either side
CROSS JOIN	Cartesian product — useful for generating combinations or date spines

-- Rows with their parent metadata, keeping rows that have no parent
SELECT
    u.FeatureName,
    u.HostName,
    m.OwnerTeam
FROM dbo.UsageEvent AS u
LEFT JOIN dbo.FeatureMeta AS m
       ON m.FeatureName = u.FeatureName
WHERE u.EventTime >= '2026-01-01';

-- Rows that are missing their parent — the anti-join pattern
SELECT u.*
FROM dbo.UsageEvent AS u
LEFT JOIN dbo.FeatureMeta AS m ON m.FeatureName = u.FeatureName
WHERE m.FeatureName IS NULL;

NOT EXISTS is usually the clearer and often faster way to express the anti-join:

SELECT u.*
FROM dbo.UsageEvent AS u
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.FeatureMeta AS m WHERE m.FeatureName = u.FeatureName
);

Filtering a LEFT JOIN's right-hand table in the WHERE clause silently converts it into an INNER JOIN — put such predicates in the ON clause when the outer rows must survive.

String Operations

Modern T-SQL provides STRING_SPLIT for the common case of decomposing a delimited column:

SELECT value AS Token
FROM STRING_SPLIT('alpha,beta,gamma', ',');

Because STRING_SPLIT returns a set rather than a value, use STRING_AGG to reverse the operation:

SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY value) AS Rejoined
FROM STRING_SPLIT('alpha,beta,gamma', ',');

Where a positional value is needed from a delimited string, a small inline function avoids repeated CHARINDEX/SUBSTRING arithmetic:

CREATE OR ALTER FUNCTION dbo.SplitPart
    (@Input NVARCHAR(4000), @Delimiter NCHAR(1), @Part INT)
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @pos INT = 1, @idx INT = 1, @start INT = 1;
    WHILE @idx < @Part
    BEGIN
        SET @pos = CHARINDEX(@Delimiter, @Input, @pos);
        IF @pos = 0 RETURN NULL;
        SET @pos += 1; SET @idx += 1;
    END;
    DECLARE @end INT = CHARINDEX(@Delimiter, @Input, @pos);
    RETURN SUBSTRING(@Input, @pos, CASE WHEN @end = 0 THEN 4000 ELSE @end - @pos END);
END;

On PostgreSQL the equivalent is string_to_array(), split_part() and regexp_split_to_table(); on MySQL 8, JSON_TABLE or a recursive CTE. Trailing and leading whitespace causes more join mismatches than casing — apply TRIM() on both sides of a comparison where data quality is uncertain.

Table Creation & Indexing

CREATE TABLE dbo.UsageEvent (
    EventId       BIGINT IDENTITY(1,1) NOT NULL,
    FeatureName   NVARCHAR(100)        NOT NULL,
    HostName      NVARCHAR(200)        NOT NULL,
    UserName      NVARCHAR(100)        NULL,
    LicCount      FLOAT                NOT NULL DEFAULT 1,
    DurationSecs  FLOAT                NULL,
    EventTime     DATETIME2(3)         NOT NULL,
    CONSTRAINT PK_UsageEvent PRIMARY KEY CLUSTERED (EventId),
    CONSTRAINT UQ_UsageEvent UNIQUE (FeatureName, HostName, EventTime)
);

-- Index the columns used in the WHERE and ORDER BY clauses
CREATE INDEX IX_UsageEvent_EventTime
    ON dbo.UsageEvent (EventTime DESC)
    INCLUDE (FeatureName, HostName, LicCount);

Guideline	Reason
Cluster on the narrowest ever-increasing key	Keeps inserts sequential and pages dense
Index foreign-key columns explicitly	SQL Server does not create them automatically
Cover frequent queries with INCLUDE	Lets the engine answer without key lookups
Keep indexes narrow	Wide keys bloat every non-clustered index
Partition huge append-only tables	Enables cheap archiving by dropping partitions

Query Hygiene

Filter with SARGable predicates — WHERE EventTime >= @d uses an index; WHERE CAST(EventTime AS DATE) = @d usually does not.
Select only the columns needed; SELECT * breaks covering indexes and inflates network cost.
Prefer EXISTS over IN for subqueries against large tables.
Use SET NOCOUNT ON in procedures to suppress per-statement row counts.
Check the execution plan for scans over large tables — the missing-index hint is usually the fastest fix available.

Topics Covered

Paged stored procedures with OFFSET/FETCH, output parameters and keyset alternatives
Dynamic, parameterised filtering with sp_executesql
Join types, anti-joins and the LEFT JOIN-predicate pitfall
String splitting and rejoining with STRING_SPLIT and STRING_AGG
Table creation with primary keys, constraints and covering indexes
Query hygiene: SARGable predicates, plan review and column projection

SQL

Table of Contents