Lesson 65. Why People That Indent Code Drive Me Nuts

(AKA A Recommended SQL Style Guide)

I am that rare breed of human for whom SQL is my primary language. Of course, I had learned other programming languages prior to SQL, but I never used them day in and day out. Most people come to SQL from other programming languages and they have a tendency to bring their nonsense with them.

This is a personal pet peeve. SQL is not like Python which enforces indentation and spacing between lines of code. People that come from other languages try to overlay whatever coding standard they have previously used onto SQL. What you wind up with is a mishmash of code inconsistently flowing all over the place as the individual engineer interprets where THEY think indents should go. Contrary to popular belief, this does NOT result in more readable code. If you left justify all of your code, at least you can use that as a universal coding standard that everybody has to follow.

Below are my recommendations for a SQL style guide. I use and implement these standards everywhere I go.

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

2. Capitalize SQL syntax.

3. 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.

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

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

Examples

Old Man Yells At Cloud

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

In [ ]:

USE demo

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') 

Capitalize SQL syntax.

In [ ]:

USE AdventureWorks2016

SELECT PurchaseOrderID, SUM(LineTotal) AS LineTotalSum
FROM Purchasing.PurchaseOrderDetail
WHERE OrderQty > 100
GROUP BY PurchaseOrderID
ORDER BY PurchaseOrderID

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.

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

In [ ]:

USE AdventureWorks2016

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

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

In [ ]:

USE AdventureWorks2016

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

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

In [ ]:

USE AdventureWorks2016

DROP PROCEDURE IF EXISTS dbo.usp_NoCountExample
GO

CREATE PROCEDURE dbo.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