Transact SQL

Follow these 10 guidelines for writing clean easy to read SQL.

Dos

  1. Limit each line of code to a single idea.

  2. Left justify your code. Every character of every line should start at column 1 in the editor.

  3. Capitalize SQL keywords and operators.

  4. If you have a really long line of code, let it run off the page. Who cares? That’s what the scroll bar is for.

  5. If you have a lot of columns in your select statement, give each column its own row.

  6. ”Drop if exists” should be a part of every script that creates a permanent database object.

Don'ts

  1. DO NOT write code with leading commas.

  2. DO NOT line up column names.

  3. DO NOT name views with a leading v_.

  4. DO NOT create procs that call other procs, views, or functions, more than one level deep.

Examples And Counter Examples

Rule 1. Limit each line of code to a single idea.

Do NOT combine ideas on a single line.

USE AdventureWorks2016

--good
SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

--bad
SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod ON poh.PurchaseOrderID = pod.PurchaseOrderID

--if you have more than one join condition, where does the rest of it go?
--good
SELECT *
FROM tableOne t1
JOIN tableTwo t2
ON t1.KeyValueOne = t2.KeyValueOne
AND t1.KeyValueTwo = t2.KeyvalueTwo

--bad
SELECT *
FROM tableOne t1
JOIN tableTwo t2 ON t1.KeyValueOne = t2.KeyValueOne
AND t1.KeyValueTwo = t2.KeyvalueTwo

--even worse
--bad
SELECT *
FROM tableOne t1
JOIN tableTwo t2 ON t1.KeyValueOne = t2.KeyValueOne AND t1.KeyValueTwo = t2.KeyvalueTwo

Rule 2. Left justify your code.

Every character of every line should start at column 1 in the editor. Go back and look! This entire class follows that standard.

USE demo

--good
DECLARE @genders TABLE(gender_id INT, gender VARCHAR(20))
DECLARE @toys TABLE(toy_id INT, gender_id INT, toy_name VARCHAR(20))

INSERT INTO @genders(gender_id, gender)
SELECT 1, 'boy'
UNION ALL
SELECT 2, 'girl'
UNION ALL
SELECT 3, 'both'

INSERT INTO @toys(toy_id, gender_id, toy_name)
SELECT 1, 1, 'GI JOE'
UNION ALL
SELECT 2,2, 'My Little Pony'


SELECT g.*,t.toy_name
FROM @genders g
LEFT OUTER JOIN @toys t
ON g.gender_id = t.gender_id 
AND t.toy_name NOT IN ('My Little Pony') 

--bad
select first      as employee_first_name,
       surname    as employee_last_name,
       title,
       case 
           when employment = 1 then 'FT' 
           when employment = 2 then 'PT'
           else 'T' 
       end        as employment_status,
       'Y'        as is_valid,
       'HR'       as employee_source

--good
select 
first as employee_first_name,
surname as employee_last_name,
title,
case 
when employment = 1 then 'FT' 
when employment = 2 then 'PT'
else 'T' 
end as employment_status,
'Y' as is_valid,
'HR' as employee_source

Rule three. Capitalize SQL reserved words and operators.

USE demo

--bad
select 
first as employee_first_name,
surname as employee_last_name,
title,
case 
when employment = 1 then 'FT' 
when employment = 2 then 'PT'
else 'T' 
end as employment_status,
'Y' as is_valid,
'HR' as employee_source


--good
SELECT 
first AS employee_first_name,
surname AS employee_last_name,
title,
CASE
WHEN employment = 1 THEN 'FT' 
WHEN employment = 2 THEN 'PT'
ELSE 'T' 
END AS employment_status,
'Y' AS is_valid,
'HR' AS employee_source

Rule 4. If you have a really long line of code, let it run off the page.

Note: This is a contrived example. As you can see, it CLEARLY violates standard 5.

USE AdventureWorks2016

--bad
SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID, pod.DueDate, 
                                                       pod.OrderQty, pod.ProductID, pod.UnitPrice, pod.LineTotal, pod.ReceivedQty, 
													   pod.RejectedQty, pod.StockedQty, pod.ModifiedDate, poh.PurchaseOrderID, 
													   poh.RevisionNumber, poh.Status, poh.EmployeeID, poh.VendorID, poh.ShipMethodID, 
													   poh.OrderDate, poh.ShipDate, poh.SubTotal, poh.TaxAmt, poh.Freight, poh.TotalDue, 
													   poh.ModifiedDate
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

--good
SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID, pod.DueDate, pod.OrderQty, pod.ProductID, pod.UnitPrice, pod.LineTotal, pod.ReceivedQty, pod.RejectedQty, pod.StockedQty, pod.ModifiedDate, poh.PurchaseOrderID, poh.RevisionNumber, poh.Status, poh.EmployeeID, poh.VendorID, poh.ShipMethodID, poh.OrderDate, poh.ShipDate, poh.SubTotal, poh.TaxAmt, poh.Freight, poh.TotalDue, poh.ModifiedDate
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

Rule 5. If you have a lot of columns in your select statement, give each column its own row.

USE AdventureWorks2016

--bad
SELECT pod.PurchaseOrderID, pod.PurchaseOrderDetailID, pod.DueDate, 
                                                       pod.OrderQty, pod.ProductID, pod.UnitPrice, pod.LineTotal, pod.ReceivedQty, 
													   pod.RejectedQty, pod.StockedQty, pod.ModifiedDate, poh.PurchaseOrderID, 
													   poh.RevisionNumber, poh.Status, poh.EmployeeID, poh.VendorID, poh.ShipMethodID, 
													   poh.OrderDate, poh.ShipDate, poh.SubTotal, poh.TaxAmt, poh.Freight, poh.TotalDue, 
													   poh.ModifiedDate
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

--good
SELECT 
pod.PurchaseOrderID, 
pod.PurchaseOrderDetailID, 
pod.DueDate, 
pod.OrderQty, 
pod.ProductID, 
pod.UnitPrice, 
pod.LineTotal, 
pod.ReceivedQty, 
pod.RejectedQty, 
pod.StockedQty, 
pod.ModifiedDate, 
poh.PurchaseOrderID, 
poh.RevisionNumber, 
poh.Status, 
poh.EmployeeID, 
poh.VendorID, 
poh.ShipMethodID, 
poh.OrderDate, 
poh.ShipDate, 
poh.SubTotal, 
poh.TaxAmt, 
poh.Freight, 
poh.TotalDue, 
poh.ModifiedDate
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

Rule 6. ”Drop if exists” should be a part of every script that creates a permanent database object.

USE AdventureWorks2016

DROP PROCEDURE IF EXISTS usp_NoCountExample
GO

CREATE PROCEDURE usp_NoCountExample

AS
BEGIN

SET NOCOUNT ON;

SELECT *
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE 1 = 1

SET NOCOUNT OFF;

END;
GO

Don't Do These Things Specifically

Don't 1. DO NOT write code with leading commas.

Who does that?! (Plenty of people.)

USE AdventureWorks2016

--bad
SELECT 
pod.PurchaseOrderID
,pod.PurchaseOrderDetailID
,pod.DueDate 
,pod.OrderQty 
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

--good
SELECT 
pod.PurchaseOrderIDpod.PurchaseOrderDetailID,
pod.DueDate,
pod.OrderQty 
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

--NO! Go straight to jail. Do not pass go! Do not collect 100 dollars!
SELECT 
      pod.PurchaseOrderID
     ,pod.PurchaseOrderDetailID
     ,pod.DueDate 
     ,pod.OrderQty 
FROM Purchasing.PurchaseOrderHeader poh
JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID

Don't 2. DO NOT line up column names.

Seems cool until you have a 1000 line proc and you change a line and now you have to change 9999 other lines so everything stays lined up!

USE demo

--bad
select first employee_first_name,
       surname employee_last_name,
       title,
       case when employment = 1 then 'FT' 
       when employment = 2 then 'PT' 
       else 'T' end as employment_status,
       'Y' as is_valid,
       'HR' employee_source


--good
select 
first as employee_first_name,
surname as employee_last_name,
title,
case 
when employment = 1 then 'FT' 
when employment = 2 then 'PT'
else 'T' 
end as employment_status,
'Y' as is_valid,
'HR' as employee_source

Last updated