This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Wise Owl coding standards for T-SQL (the SQL dialect used in SQL Server) Part two of a three-part series of blogs
A blog setting out an approach to coding in SQL to ensure consistently high standards across your organisation. Coding standards for writing SQL in Management Studio Coding conventions for selecting data in SQL (this blog) Coding conventions for programming in SQL

Wise Owl coding standards for T-SQL (the SQL dialect used in SQL Server)

Part two of a three-part series of blogs

A blog setting out an approach to coding in SQL to ensure consistently high standards across your organisation.

Coding standards for writing SQL in Management Studio
Coding conventions for selecting data in SQL (this blog)
Coding conventions for programming in SQL

Posted by Andy Brown on 01 February 2017

In this blog

Indentation
Comments
Capitalisation
Table Aliases
Column Aliases
Joins
The CASE Expression
Dates
Combining criteria

Coding conventions for selecting data in SQL

Here is how to write a SELECT statement:

USE WorldEvents

-- show events in date order

SELECT

-- use quotes for aliases containing spaces

e.EventName AS 'Name of event'

, e.EventDetails AS 'Event details'

, e.EventDate AS EventDate

FROM

tblEvent AS e

WHERE

-- show just events in country 5 (the USA)

e.CountryID = 5

ORDER BY

e.EventDate ASC

Read on for specific notes on comments, indentation, capitalisation, aliases, etc.

You should include commas before each column that you want to display, not after, although many Wise Owl blogs, exercises and manual chapters put the commas at the end of each line. Sorry!

Indentation

You should indent any code following any one of the main SQL keywords (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY):

SELECT

-- what to select

FROM

-- table

WHERE

-- any criteria

GROUP BY

-- columns to group by

HAVING

-- aggregated data to filter by

ORDER BY

-- how to sort

Comments

You should comment frequently (a good guideline is that if you've written 3 lines of SQL without a comment, it's probably time to include one). Most comments should use -- as a prefix, but you can use comment blocks when you have a lot to say:

If you have a procedure which needs a lot of explanation, it's

OK to encase the comments (indented, as here) like this.

Comments should be indented to the same level as the line of code which immediately follows, and should always be preceded by a blank line:

ORDER BY

-- show in date order

e.EventDate ASC

Capitalisation

You should put keywords (these normally appear in blue) in upper case:

USE WorldEvents

-- list events

SELECT

FROM

tblEvent AS e

Table Aliases

You should give all table names aliases, even for simple queries:

-- list events

SELECT

e.EventID

,e.EventName

FROM

tblEvent AS e

Use short alias names in lower case (in most cases the first letter of the main part of the table name will suffice, as for the above example).

Column Aliases

Column aliases are optional. Where you use them, you should encase them in quotation marks if the alias includes spaces, as below:

SELECT

-- use quotes for aliases containing spaces

e.EventName AS 'Name of event'

, e.EventDetails AS 'Event details'

, e.EventDate AS WhenHappened

FROM

tblEvent AS e

Joins

The syntax for inner joins is shown below:

SELECT

-- show columns from the 2 tables

c.CountryName

, e.EventName

FROM

tblCountry AS c

-- join countries to events

INNER JOIN tblEvent AS e

ON c.CountryID = e.CountryID

Notes:

You should include both words INNER JOIN.
The comment before the join is a good idea, but optional.

For outer joins, you should use left outer joins only, not right ones:

-- join countries to events

LEFT OUTER JOIN tblEvent AS e

ON c.CountryID = e.CountryID

The CASE Expression

You should indent the clauses of a CASE expression as below:

-- events by century

SELECT

e.EventName

-- show century that events occur in

, CASE

WHEN YEAR(e.EventDate) < 1900

THEN 'Pre 19th'

WHEN YEAR(e.EventDate) < 2000

THEN '20th'

ELSE '21st'

END AS Century

FROM

tblEvent AS e

It's also acceptable to put the WHEN and THEN on the same line:

-- show century events occur in

, CASE

WHEN YEAR(e.EventDate) < 1900 THEN 'Pre 19th'

WHEN YEAR(e.EventDate) < 2000 THEN '20th'

ELSE '21st'

END AS Century

You should always have at least one comment for a CASE expression, and always give it the resulting column an alias (the one above is called Century).

Dates

You can use either the FORMAT or the CONVERT function to present dates (although be aware that the FORMAT function can be slow):

-- events by century

SELECT

e.EventName

-- two ways to show dates

, FORMAT(e.EventDate,'dd/MM/yyyy') AS 'Event date 1'

, CONVERT(char(10),e.EventDate,103) AS 'Event date 2'

FROM

tblEvent AS e

WHERE

-- show events from 2010 onwards

e.EventDate > '2009-12-31'

As the above examples shows, use the ISO standard date format of YYYY-MM-DD to refer to any date (so 2009-12-31 represents 31st December 2009).

Combining criteria

Where you use and/or, put these at the beginning of the lines they apply to:

SELECT

e.EventName

, e.EventDetails

, e.EventDate

FROM

tblEvent AS e

WHERE

-- show events from 2000 on

YEAR(e.EventDate) >= 2000

-- name starts with s

AND e.EventName LIKE 's%'

If you need to combie criteria using parentheses, indent between them:

WHERE

-- show events from 2000 on

year(e.EventDate) >= 2000

-- name starts or ends with S

AND (

e.EventName LIKE 's%'

OR e.EventName LIKE '%s'

)

The next and final part of this blog looks at coding conventions for SQL programming, rather than just for selecting data.

Parts of this blog
Coding standards for writing SQL in Management Studio Coding conventions for selecting data in SQL (this blog) Coding conventions for programming in SQL

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Coding conventions for selecting data in SQL

Indentation

Comments

Capitalisation

Table Aliases

Column Aliases

Joins

The CASE Expression

Dates

Combining criteria

Head office

London

Manchester