564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Wise Owl coding standards for T-SQL (the SQL dialect used in SQL Server)
Part three 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.
This blog gives coding conventions for more advanced SQL programmers.
Either put a comment before any batch of variable declarations:
-- the minimum date
DECLARE @MinDate DATE = '20001-01'
Or put a comment to the right:
DECLARE @FirstLetter CHAR(1) = 'S' -- first letter to use
Use PascalCase for variable names, and don't be afraid to use multiple compound words. The following are good variable names:
DECLARE @EventNameSearchString VARCHAR(100)
DECLARE @EarliestAllowableEventDate DATE
Instead of using PascalCase, you could instead use camelCase. The difference is in whether you capitalise the first letter.
Lay out stored procedures/functions like this, commenting each parameter:
CREATE PROC spListEvents(
@MinDate AS DATE -- the earliest date
, @StartLetter AS CHAR(1)=NULL -- letter events must start with
-- list out events beginning with given letter and on
-- or after given date
tblEvent AS e
e.EventDate >= @MinDate
-- if parameter not specified, ignore it
e.EventName LIKE @StartLetter + '%'
OR @StartLetter IS NULL
You should indent any code between two statements which act as bookends to code. For example:
-- do something here
-- dodgy thing to try doing
-- what to do if went wrong
Indent these, and by preference use BEGIN and END to make them clearer:
-- if today is a Tuesday, say so
IF DATEPART(weekday,GETDATE()) = 3
SELECT 'It''s a Tuesday!'
Always indent CTEs, and begin each with a comment explaining what it returns:
WITH RecentEvents AS (
-- events since 2000
tblEvent AS e
year(e.EventDate) >= 2000
EventsStartingWithS AS (
-- events starting with S
tblEvent AS e
EventName like 'S%'
-- show events satisfying both conditions
RecentEvents AS re
INNER JOIN EventsStartingWithS AS se
ON re.EventID = se.EventID
INNER JOIN tblEvent AS e
ON re.EventID = e.EventID
Indent subqueries and add a comment to explain what they do:
tblCountry AS c
-- number of events for country more than 50
FROM tblEvent AS e
WHERE e.CountryID = c.CountryID
) > 50
For subqueries, you should also put each of the SELECT, FROM, WHERE, etc clauses on a single line, as above, to make it more obvious which is the main query and which the subquery.
For all cases where you are listing the columns in a table, indent them. For example:
-- create table of events
DECLARE @EventTable TABLE (
EventID INT IDENTITY(1,1) PRIMARY KEY
, EventName VARCHAR(100)
-- add event to table
INSERT INTO tblEvent(
) VALUES (
, 'Blog on SQL standards published'
Let me know if I've left some corner of SQL unscathed!
|Parts of this blog|
25 Aytoun Street