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.7 · SHOULD

e.g:

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.

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.3 · MUST

Specify columns in `INSERT` statements · SQL-02.4 · MUST

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

e.g.

INSERT INTO (FirstName, LastName) dbo.Authors VALUES
('Bob', 'Example');

instead of

INSERT INTO dbo.Authors VALUES
('Bob', 'Example');

Use transactions · SQL-02.5 · 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.6 · SHOULD

See here for more information

Set `NOCOUNT` on · SQL-02.7 · 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.8 · SHOULD

Avoid using dynamic SQL · SQL-02.9 · SHOULD

Wrap stored procedures in `BEGIN`-`END` · SQL-02.10 · SHOULD

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

Use table variables · SQL-02.11 · SHOULD

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

Avoid using `GOTO` · SQL-02.12 · SHOULD

Add comments using `--` · SQL-02.13 · SHOULD

Avoid `JOIN`s with subqueries · SQL-02.14 · SHOULD

Use CTEs with complex queries · SQL-02.15 · SHOULD

Use CTEs (Common Table Expression) when creating complex queries.

Avoid using `SELECT *` · SQL-02.16 · 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.17 · 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.

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

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

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

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

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

HTML and CSS

TypeScript