Follow these 10 guidelines for writing clean easy to read SQL.
Dos
Limit each line of code to a single idea.
Left justify your code. Every character of every line should start at column 1 in the editor.
Capitalize SQL keywords and operators.
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.
If you have a lot of columns in your select statement, give each column its own row.
”Drop if exists” should be a part of every script that creates a permanent database object.
Don'ts
DO NOT write code with leading commas.
DO NOT line up column names.
DO NOT name views with a leading v_.
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.
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
