Skip to content

Latest commit

 

History

History
228 lines (161 loc) · 6.73 KB

README.md

File metadata and controls

228 lines (161 loc) · 6.73 KB

SQL conventions

Using a convention when writing SQL allow us to be more productive as a team.

Table of contents

Naming

Singular table name

We use singular table names and not plural.

Prefer:

Invoice

Avoid:

Invoices

Avoid using product names

Products get renamed and get acquired by other vendors. Instead try to convey the business meaning.

Avoid using abbreviations

Abbreviations can sometimes be confusing. It is acceptable to use well-known acronyms.

Primary key as Id and Code

Primary keys should be named Id when they are an exact-number type or a uniqueidentifier type.

In the expression Entity.Id, the Entity table has Id as a Primary Key. Another table referencing the entity Id as a Foreign Key could name the column as EntityId.

When the primary key is an alphanumeric (often provided by another system), use the term Code. For example a Product could be designed by its Code.

Favour natural keys over surrogate keys

If you already have a unique identifier provided by another system, use it instead of introducing a surrogate key. For example if a Product is identified by a Code, use the Code as a natural key instead of introducing a surrogate key in the form of an exact-number Primary Key.

On the other end, if an entity does not have a natural key, introduce a surrogate key so that you can identify each row uniquely and easily.

Use the most appropriate type

numeric over money

Favour numeric instead of money to avoid unintentional loss of precision due to rounding errors during calculations.

varchar over char for variable length columns

Favour varchar instead of char when the column data entries vary in length.

datetime2 over datetime

datetime2 is more precise than datetime and you can decide how much precision you want. Read On the Advantages of DateTime2(n) over DateTime for more details.

exact-number or uniqueidentifier for Primary Keys

Primary keys should preferably use an exact-number type. The exact-number type (bigint, int, smallint or tinyint) should be selected based on the expected number of rows in the table.

Alternatively a uniqueidentifier type can be used when multiple providers are inserting rows concurrently. Keep in mind that exact-number types make for shorter and more readable URIs.

Avoid square brackets

Do not use square brackets when they're not required.

Prefer:

SELECT
    TOP (1)
    Id
FROM dbo.Entity;

Avoid:

SELECT
    TOP (1)
    [Id]
FROM [dbo].[Entity];

Use square brackets when the identifier is a reserved word.

Prefer:

SELECT
    TOP (1)
    [date]
FROM dbo.legacytable;

Do not play with fire

Always use AS when defining an alias

The query below will display the column bad_name as the alias other_bad_name.

Avoid:

SELECT
    TOP (1)
    bad_name
    other_bad_name
FROM dbo.legacytable;

Prefer:

SELECT
    TOP (1)
    bad_name AS BetterName,
    other_bad_name AS OtherBetterName
FROM dbo.legacytable;

Always use WITH when using a table hint

The table legacytable has been aliased to NOLOCK in the following query.

Avoid:

SELECT
    TOP (1)
    NOLOCK.bad_name
FROM dbo.legacytable NOLOCK;

It's very likely the developer meant to use the NOLOCK table hint instead.

Prefer:

SELECT
    TOP (1)
    bad_name
FROM dbo.legacytable WITH (NOLOCK);

Use two-part object names

Include the schema when referencing a table.

Avoid:

SELECT
    TOP (1)
    Id
FROM Entity;

Prefer:

SELECT
    TOP (1)
    Id
FROM dbo.Entity;

This assists with query plan caching as explained by Greg Low.

Query in style

The query below is of no value aside of illustrating different clauses:

SELECT
    -- Avoid using reserved keywords as an alias but if you do use double quotes
    -- See: https://www.red-gate.com/hub/product-learning/sql-prompt/sql-prompt-code-analysis-avoid-non-standard-column-aliases
    CONVERT(varchar(10), char_date, 23) AS "Date",
    -- Put each selected expression on its own line
    COUNT(*) AS SomeCount
-- FROM goes on its own line
FROM dbo.Entity
    -- Sample JOINs, I'm just demonstrating how towe join
    INNER JOIN dbo.RelatedTable ON Entity.RelatedTableId = RelatedTable.Id
    LEFT OUTER JOIN dbo.OtherRelatedTable ON Entity.OtherRelatedTableId = OtherRelatedTable.Id
-- WHERE goes on its own line
WHERE
    char_date BETWEEN '2019-05-05' AND '2020-05-05'
    -- Each clause goes on its own line
    AND [type] <> 'OHHI'
-- GROUP BY goes on its own line
GROUP BY CONVERT(varchar(10), char_date, 23)
-- ORDER BY goes on its own line
-- We use square brackets around `Date` because it's a reserved keyword
ORDER BY [Date];

References