SQL Code Style Guide

By: Dan Stewart
February 10, 2016
MIT License

Standards were meant to be broken. This is not a standard. This is how I like to write SQL code. Feel free to deviate when needed.

Source Code File Organization

Source code files should contain only one SQL object definition with the file name being the same name as the object name. For example, if the object name is spShackListingByID the file name would be spShackListingByID.sql.

Stored Procedures

CREATE PROCEDURE spShackListingByID
(
  Param1,
  Param2
)

AS

Query

GO

Indentation

Spaces, not tabs. Tabs are measured differently by different editors, but spaces are uniform.

Commas

Commas go at the end of a line except if used in a method.

SELECT s.ShackID AS [ID],
  Name AS [Shack Name]

Method commas do not need to be on their own line.

REPLACE(
  REPLACE(
    CAST(@LineBreaksInTextField AS VARCHAR(MAX)), CHAR(13), ' '), CHAR(10), ' ')

Line Length

Break up lines as needed to avoid horizontal scrolling. The section on Wrapping Lines will help.

REPLACE(REPLACE(CAST(@LineBreaksInTextField AS VARCHAR(MAX)), CHAR(13), ' '), CHAR(10), ' ')

--If it is too long, break on a comma.

REPLACE(REPLACE(CAST(@LineBreaksInTextField AS VARCHAR(MAX)), 
  CHAR(13), ' '), 
  CHAR(10), ' ')

Wrapping Lines

New line general principles:

  1. Break after a comma
  2. Break at the end of SELECT statement
  3. Break at the end of CASE
  4. Break at the end of WHEN THEN statement
  5. Break at the end of ELSE statement
  6. Break at the end of FROM statement
  7. Break at the end of JOIN statement
  8. Break at the end of WHERE statement
  9. Break at the end of AND/OR statement
  10. Break at the end of ORDER BY statement
  11. Break at the end of GROUP BY statement
  12. Break at the end of HAVING statement

Wrapping Lines Example

SELECT COUNT(s.ShackID),
  CASE 
    WHEN s.HasWater = 1 THEN 'Move in ready'
    ELSE 'Fix er upper'
  END as [Good Shack],
  o.Name
FROM Shack s
  JOIN Renter r ON r.RenterID = s.RenterID
    AND r.Type = 'Human'
WHERE s.ZipCode = '80112'
GROUP BY s.HasWater,
  r.Name
HAVING COUNT(s.ShackID) > 1

Comments

The comments I usually see involve explaining variables and why they have the value they do.

SQL queries can have two kinds of comments: block comments and line comments. Block comments are delimited by //, and line comments are –.

Avoid adding comments at the end of a line of code. Here be dragons…

UPDATE Shack SET HasWater = 1 -- Yeah water! WHERE ShackID = 5

Because the comment was added on the same line as the code, the WHERE clause was commented out. Now all of the shacks have water instead of just the one with ID = 5. I’ve seen this happen. It’s not fun.

Avoid surrounding a block comment with a frame. It may look nice, but it is hard to maintain.

/* ()xxxx{:::::::::::::::::::::::::
If you add anything to this comment,
you have to lengthen the swords.
()===[]{::::::::::::::::::::::::::: */

Comments can create zombie code. This is dead code, but if you check it in, it will live an undead existence. Remove zombie code before you check in.

SELECT *
FROM Shack
WHERE Zip = '80112'
-- AND OccupantType = 'Zombie'

Declarations

One declaration per line is recommended.

DECLARE @ShackID INT,
  @OccupantType VARCHAR(25),
  @HasWater BIT

Put variables only at the beginning of the query.

CREATE PROCEDURE spShackListings
(
  @OccupantType VARCHAR(25),
  @HasWater BIT
)

AS

DECLARE @StartDate DATETIME,
  @EndDate DATETIME

SET @StartDate = GETDATE() - 1
SET @EndDate = GETDATE()

White Space

Blank Lines

Blank lines go:

Blank spaces go:

Naming Rules

There are only two hard problems in computer science.

  1. Naming things
  2. Cache invalidation
  3. Off-by-one errors

Don’t get stuck in analysis paralysis with names. Name the object the first thing that comes to mind and change it later.

Objects

Naming Variables

Tables

Microsoft SQL Server

Capitalization

Pascal case - The first letter and the first letter of each word are capitalized. For example: HasWater.

Camel case - The first letter of an identifier is lowercase and the first letter of each subsequent concatenated word is capitalized. For example: spShackListings.

All Caps - All letters in the identifier are capitalized. For example: SELECT

The following table provides rules and example for common identifiers:

Identifier Type Rules for Naming Examples
SQL All Caps SELECT FROM WHERE
TSQL Functions All Caps GETDATE() MIN() MAX()
Objects Pascal case HasWater
Stored Procedures Camel case spShackListings

Common Functions

Date Ranges

CREATE PROCEDURE spShackMoveInDates
(
  @StartDate DATETIME,
  @EndDate DATETIME
)

AS

SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), 0)
SET @EndDate = DATEADD(DAY, 1, DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 0))

SELECT *
FROM Shack
WHERE MoveInDate >= @StartDate AND MoveInDate < @EndDate

GO

Multiple Values

DECLARE @LastNames VARCHAR(MAX),
  @RemainingLastNames NVARCHAR(MAX),
  @CommaIndex INT,
  @LastName NVARCHAR(250)

DECLARE @LastNameTable TABLE (LastName VARCHAR(250))

SET @LastNames = 'Stewart,Stuart'
SET @RemainingLastNames = @LastNames
SET @CommaIndex = CHARINDEX(',', @RemainingLastNames)

WHILE (LEN(@RemainingLastNames) > 0) BEGIN
  IF @CommaIndex > 0 BEGIN
    SET @LastName = LEFT(@RemainingLastNames, @CommaIndex - 1)
  END
  ELSE BEGIN
    SET @LastName = @RemainingLastNames
  END

  INSERT INTO @LastNameTable (LastName) VALUES (RTRIM(LTRIM(@LastName)))

  IF @CommaIndex > 0 BEGIN
    SET @RemainingLastNames = RIGHT(@RemainingLastNames, LEN(@RemainingLastNames) - @CommaIndex)
  END
  ELSE BEGIN
    SET @RemainingLastNames = ''
  END

  SET @CommaIndex = CHARINDEX(',', @RemainingLastNames)
END

SELECT *
FROM Renter r
  JOIN @LastNameTable t ON t.LastName = r.LastName

Best, Latest

Find the latest shack rented for each renter.

SELECT r.MoveInDate,
  s.Name
FROM Shack s
  JOIN Renter r ON r.RenterID = s.RenterID
  JOIN (
    SELECT ROW_NUMBER() OVER (PARTITION BY r.RenterID ORDER BY s.MoveInDate DESC) AS Ordinal,
      s.ShackID
    FROM Shack s
      JOIN Renter r ON r.RenterID = s.RenterID) LastShackRented ON LastShackRented.ShackID = s.ShackID
        AND LastShackRented.Ordinal = 1
    WHERE s.MoveInDate > '1/1/2016'
ORDER BY s.MoveInDate

Concatenating into a Single Column for Custom Data

SELECT (SELECT ISNULL(r.FirstName, '') + ISNULL(r.Name, '') AS [data()]
FROM Renter r
  JOIN Shack s ON s.RenterID = r.RenterID
WHERE r.RenterID = rn.RenterID
FOR XML PATH('')) AS [Custom Info]
  FROM Renter rn
  WHERE rn.RenterID = 123