## Standard Metadata

- Area: SQL
- Owner: Backend Steering group
- Last reviewed: 2026-09-09
- Next review due from: 2026-12-01
- Enforcement: Code review
- Status: Active

# SQL · `SQL`

# Formatting · SQL-01
## Keywords must be capitalised · SQL-01.1 · **MUST**

## Variable names must be camel case · SQL-01.2 · **MUST**

## Use single quotes for string constants · SQL-01.3 · **MUST**

## The following keywords should be on new lines · SQL-01.4 · **SHOULD**

* `SELECT`
* `FROM`
* `WHERE`
* `JOIN` (and all variants)
* `GROUP BY`
* `ORDER BY`
* `DECLARE`
* `DELETE`
* `INSERT`
* `WITH`
* `AND`
* `OR`
* `ON`
* `CASE`
* `WHEN`
* `ELSE`
* `BEGIN`
* `END`

## The following keywords should be indented · SQL-01.5 · **SHOULD**

* `AND`
* `OR`
* `ON`
* `WHEN`
* `ELSE`

## Most other keywords should stay on the same line · SQL-01.6 · **SHOULD**

## Use new lines for readability · SQL-01.7 · **COULD**

If wrapping the line would improve readability, split it so that a keyword is the first thing on the new line

## Column names defined in a `SELECT` statement should be on a new line and indented · SQL-01.8 · **SHOULD**

e.g:

```sql
DECLARE
    @authorId INT = 5,
    @title VARCHAR(50) = 'Book Title';

SELECT
    a.FirstName,
    a.LastName,
    b.Title
FROM dbo.Books b
JOIN dbo.Authors a
	ON b.AuthorId = a.AuthorId
WHERE b.AuthorId = @authorId
	AND b.Title = @title;
```

***

# Queries · SQL-02

## Terminate all statements with a semicolon (`;`). · SQL-02.1 · **MUST**

## Use full object names (`;`) · SQL-02.2 · **MUST**

e.g. use `dbo.Books` rather than just `Books`.

## Use table aliases · SQL-02.3 · **MUST**

Use table aliasing when the SQL statement involves multiple sources. This helps improve readability. e.g.
```sql
SELECT
    a.FirstName,
    a.LastName,
    b.Title
FROM dbo.Books b
JOIN dbo.Authors a
	ON b.AuthorId = a.AuthorId
WHERE b.AuthorId = @authorId
	AND b.Title = @title;
```

## Aliases (table and column) must not use SQL Server keywords · SQL-02.4 · **MUST**

## Specify columns in `INSERT` statements · SQL-02.5 · **MUST**

Use a column list in `INSERT` statements. This protects your query from breaking if the table changes.

e.g.
```sql
INSERT INTO (FirstName, LastName) dbo.Authors VALUES
('Bob', 'Example');
```
instead of 
```sql
INSERT INTO dbo.Authors VALUES
('Bob', 'Example');
```

## Use transactions · SQL-02.6 · **MUST**

When running a query for the first time, you must wrap it in a transaction (using `BEGIN TRAN` and `ROLLBACK TRAN`). This will allow you to check that it affects the correct number of rows. Once you have confirmed that it is correct, you can replace `ROLLBACK TRAN` with `COMMIT TRAN`. This is especially important on live databases.

## Use `TRY`-`CATCH` block for error handling · SQL-02.7 · **SHOULD**

[See here for more information](https://sqlwithmanoj.com/2010/06/16/try-catch-exception-handling/)

## Set `NOCOUNT` on · SQL-02.8 · **SHOULD**

Use `SET NOCOUNT ON` at the beginning of a stored procedure to do this. This removes the count of rows affected, which is normally be sent back to your Application code. Setting `NOCOUNT` on therefore reduces unnecessary network data.

## Avoid using `CURSORS` · SQL-02.9 · **SHOULD**

## Avoid using dynamic SQL · SQL-02.10 · **SHOULD**

## Wrap stored procedures in `BEGIN`-`END` · SQL-02.11 · **SHOULD**

Use `BEGIN` at the start of a stored procedure and `END` at the end.

## Use table variables · SQL-02.12 · **SHOULD**

Use table variables rather than temporary tables as this is usually more performant.

## Avoid using `GOTO` · SQL-02.13 · **SHOULD**

## Add comments using `--` · SQL-02.14 · **SHOULD**

## Avoid `JOIN`s with subqueries · SQL-02.15 · **SHOULD**

## Use CTEs with complex queries · SQL-02.16 · **SHOULD**

Use CTEs ([Common Table Expression](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql/)) when creating complex queries.

## Avoid using `SELECT *` · SQL-02.17 · **SHOULD**

Always write the required column names. This reduces disk I/O and increases performance, especially on large tables.

## Avoid using SQL Triggers · SQL-02.18 · **SHOULD**

Improperly used, SQL Triggers can cause significant overhead. They are executed in a way that is invisible to client applications. If a trigger is the cause of a problem, it can be extremely difficult to diagnose and fix. [This article explains in more detail](https://www.itprotoday.com/development-techniques-and-management/reasons-avoid-triggers).

Note that some of the **SHOULD** items on this list have exceptions.

***

# Object names · SQL-03
## Use Pascal Case when naming objects · SQL-03.1 · **MUST**

## Use `Cte` prefix for CTEs · SQL-03.2 · **MUST**

CTE names must be prefixed with `Cte`. This is to remind the developer that they are joining onto is not a table, view, or table-valued function.

## New CTEs must be on a new line · SQL-03.3 · **MUST**

## Stored procedure names must not be prefixed · SQL-03.4 · **MUST**

Stored procedure names must not be prefixed with `SP_`, `sp_` or any variation thereof. 

e.g. `Assets.GetAssetAudits`.

The `sp` prefix is reserved for system stored procedures that ship with SQL Server. When SQL Server encounters a stored procedure prefixed by `sp_`, it first tries to look for the procedure in the master database before looking at other qualifiers. Not prefixing therefore saves a lot of time.

## Name objects descriptively · SQL-03.5 · **SHOULD**

# Examples
Below are some examples which follow the defined standards.

## Simple Query

```sql
DECLARE
    @authorId INT = 5,
    @title VARCHAR(50) = 'Book Title';

SELECT
    a.FirstName,
    a.LastName,
    b.Title
FROM dbo.Books b
JOIN dbo.Authors a
	ON b.AuthorId = a.AuthorId
WHERE b.AuthorId = @authorId
	AND b.Title = @title;
```

## Stored Procedure

```sql
CREATE PROCEDURE [dbo].[GetLatestAmendment]
	@userId INT
AS
BEGIN

DECLARE @amendmentId INT;
DECLARE @approvedStatus INT = 300;

SELECT TOP 1
	@amendmentId = PersonalInformationAmendmentId
FROM Clients.PersonalInformationAmendments
WHERE UserId = @userId
	AND Deleted IS NULL
ORDER BY Created DESC;

SELECT
	a.PersonalInformationAmendmentId,
	a.UserId,
	CONVERT(VARCHAR(MAX), a.RequestJson) AS RequestJson,
	a.[Status],
	a.RejectedReason,
	a.Rejected,
	CASE
		WHEN a.RejectedById IS NULL	THEN NULL
		ELSE u.FirstName + ' ' + u.LastName
	END AS RejectedBy,
	a.Created,
	CASE
		WHEN a.CreatedById IS NULL THEN NULL
		ELSE u2.FirstName + ' ' + u2.LastName
	END AS CreatedBy,
	a.SiteId
FROM Clients.PersonalInformationAmendments a
LEFT JOIN Security.Users u
	ON a.RejectedById = u.Id
LEFT JOIN Security.Users u2
	ON a.CreatedById = u2.Id
WHERE PersonalInformationAmendmentId = @amendmentId
	AND [Status] != @approvedStatus;

END
GO
```

## View

```sql
CREATE VIEW [TransitPlanning].[JourneySummary]
AS
WITH CteWaypoints AS (
	SELECT
		jw.JourneyId,
		jw.Ordinal,
		CASE
			WHEN jw.AssetId IS NULL THEN w.[Name]
			ELSE a.[Name]
		END AS [Name]
	FROM TransitPlanning.JourneyWaypoints jw
	LEFT JOIN TransitPlanning.Waypoints w
		ON jw.WaypointId = w.WaypointId
	LEFT JOIN Assets.Assets a
		ON jw.AssetId = a.AssetId
),
CtePertinentWaypoints AS (
	SELECT
		JourneyId,
		MIN(Ordinal) AS FirstOrdinal,
		MAX(Ordinal) AS LastOrdinal
	FROM CteWaypoints
	GROUP BY JourneyId
)
SELECT
	j.JourneyId,
	j.SiteId,
	j.[Name],
    j.Direction,
	fw.[Name] AS StartLocation,
	lw.[Name] AS EndLocation
FROM TransitPlanning.Journeys j
JOIN PertinentWaypoints pw
	ON j.JourneyId = pw.JourneyId
JOIN CteWaypoints fw
	ON pw.JourneyId = fw.JourneyId
	AND pw.FirstOrdinal = fw.Ordinal
JOIN CteWaypoints lw
	ON pw.JourneyId = lw.JourneyId
	AND pw.LastOrdinal = lw.Ordinal
GO
```

## CTE

```sql
WITH CteBooksInStock AS (
    SELECT
        book.Id,
        book.Name,
        book.AuthorId
    FROM dbo.Books book
    WHERE book.InStock = 1
),
CteAuthorsOnSale AS (
    SELECT
        a.Id,
        a.Name,
        a.IsOnSale
    FROM dbo.Authors a
    WHERE a.IsOnSale = 1
),
CteBooksInStockAndOnSale AS (
    SELECT
        book.Name BookName,
        author.Name AuthorName
    FROM CteAuthorsOnSale author
    JOIN CteBooksInStock book
		ON author.Id = book.AuthorId
)
SELECT
    BookName,
    AuthorName
FROM CteBooksInStockAndOnSale
```