WM Projects

This SQL code style guide is a set of guidelines and opinionated suggestions on how to write code for SQL Server.

General Guidelines:

Terminate instructions with a semicolon.
Indent code but avoid nesting when possible.
Reserved keywords should be uppercase.
If an ANSI function exists, use it over a databases' specific version, eg. COALESCE() is ANSI, ISNULL() is T-SQL.
White spacing between operators, and after commas.
Avoid hard-coding values whenever possible.

Naming:

Use consistent and descriptive identifiers and names; using letters, numbers, underscore in names. Avoid using dashes.
Use Snake Case. snake_case makes code easier to read.
Always assume collation is case-sensitive. Use uppercase letters for keywords.
Use table aliases on joins, including the "AS" keyword.
Use schemas to represent business units or functional scope. Always use a fully qualified name, eg. [dbo].[Foo]
Always name indexes. Naming conventions should follow the index type, eg. NON CLUSTERED INDEX should start with nci_; UNIQUE as unci_; HASH as hi_, etc. Followed by the snake case table name, and enough column name to make it unique. If there is ever an index-related error, SQL Server can only bubble up the index name, so that may be all you have to dig into the issue. nci_Foo_Column1_Column2
Optional: bracket reserved names, table objects and schemas. SELECT Column1 FROM [dbo].[Foo];

Commenting:

Do not litter the sql code with useless comments. Comments should only explain the "why" in a vague code block. Use README.md in the projects or the commit memo to explain any business logic, or changes.

Design:

Keep data types consistent with upstream systems, and/or try to compliment the applications it will be serving.
Do not prefix sql objects with their type, ie. stored procedures with sp or tables with tbl , etc.
Name your keys, and constraints.
Design functions and stored procedures to use table-valued parameter (TVP) inputs versus single parameter inputs.