⚠️ It’s hard to convey sarcasm over the text. I’m putting this disclaimer at the top so I don’t come off egoistic. This post is meant as hyperbole, and I’ll leave finding the truth to any statements to the readers.
I’m allergic to semicolons
Aaron Bertrand (t) recently wrote a post Why I always start CTEs with a statement terminator.
So I’ll take it one step further.
I’ll never use semicolons unless I have to.
Tools like Redgate’s SQL Prompt can add semicolons automatically, but I still won’t do it.
These are the reasons why
- They don’t do anything for me. I can see where the statement ends. If I can’t, then a semicolon wouldn’t help anyway
- They are clearly not required by the engine either
- Requiring semicolons is not enforceable. It was deprecated in 2008 R2 (in 2010) - but here we are.
Features Not Supported in a Future Version of SQL Server
Not ending Transact-SQL statements with a semicolon.
End Transact-SQL statements with a semicolon ( ; ).
Practical reasons
My code is not set in stone. So I have to move this clinging semicolon out of the way whenever I want to update.
Selecting the space before the semicolon is like separating two thin Lego pieces. I hope you have a large font and precise mouse
Let’s take this query as an example
SELECT
dopc.instance_name
, dopc.cntr_value
FROM
sys.dm_os_performance_counters AS dopc
WHERE
dopc.object_name = N'SQLServer:Deprecated Features'
AND dopc.cntr_value > 0; -- I bet you hate this comment now
Removing the line
Without the semicolon | With the semicolon |
---|---|
Select the line | Select the line |
Shift+Del | Shift+Del |
Up Arrow | |
End | |
; |
Adding new lines
Without the semicolon | With the semicolon |
---|---|
Select the empty line below | Pixelhunt the space before the semicolon first (or say goodbye to IntelliSense) |
Add the condition | Delete the ; |
Enter | Select the empty line below |
Add the condition | |
; | |
Enter |
It’s just more work for no benefit at all.
Heck, I don’t even end all my messages with an interpunction.
And be honest with yourself, do you?
I’ve skipped a few full stops on purpose, have you noticed?
The controversy doesn’t end here.
The leading commas
First, let me ask you this. Do you write the conditions in the WHERE
clause like this
WHERE
o.is_ms_shipped = 1
AND parent_object_id IS NULL
AND o.name LIKE 'tbl%'
Or like this?
WHERE
o.is_ms_shipped = 1 AND
parent_object_id IS NULL AND
o.name LIKE 'tbl%'
I’m using the first style because
- I like knowing where the new condition begins
- It’s easier to add lines or move them around
- The logic starts in the same column
Me using leading commas is just being consistent.
Practical reasons
Having all commas moved out of the way at the start of the line is good for mass column updates using ALT + Drag
. Also, since the whitespace at the end doesn’t count, I can quickly wrap my columns in code and add aliasing, comments, etc.
Not saying converting all columns to VARCHAR(200)
is a good idea, but here is a demo:
I’m mainly using SSMS. The Visual Studio Code or Azure Data Studio are close with the Multi-cursor (drag middle button), but not quite.
Aliasing
Just when you thought you couldn’t hate my style more - BAM, out of the left-field with the aliasing.
I like using initials as an alias
- It’s short, so it helps with the readability
- I can usually deduce the full table name
- You should qualify your columns, and there are tables like this out there
- tblCustomerContactFormApproval - Imagine qualifying your columns with this
The usual counterargument is (surprisingly) also readability.
“How can you tell what table
p
means? It’s better to use the full table name or meaningful alias”
Well, that’s easy, you see.
I start reading the code in the FROM
clause. Also, I’ve trained myself to keep multiple things in short term memory.
This handy script shows you the number of conflicting alias initials.
/* The Clash of Aliases */
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L3)
, tally AS (SELECT TOP (256) n FROM Nums ORDER BY n)
, aliases AS
(
SELECT
t.name AS tabName
, STRING_AGG(ca.chr, '') WITHIN GROUP (ORDER BY ca.seqNo) AS abb
FROM sys.tables t
CROSS APPLY( SELECT SUBSTRING(t.name, n, 1), n from tally n) ca(chr, seqNo)
WHERE
PATINDEX('[A-Z]', ca.chr) > 0
AND ca.chr = UPPER(ca.chr) COLLATE Latin1_General_CS_AI
GROUP BY t.name
)
SELECT
a.tabName
, LOWER(a.abb) AS abb
, COUNT(1) OVER (PARTITION BY a.abb) AS cnt
FROM aliases a
ORDER BY cnt DESC, a.tabName
What about the other stuff?
I haven’t covered
- Indentation and Tabs vs Spaces
- UPPERCASE, lowercase, camelCase, etc.
- Extra words like
INNER JOIN
vsJOIN
And that’s because most of the time, I don’t care. I only care if it has any practical implications (performance, readability, etc.), but none of it is a hill I’m willing to die on. Not even the semicolons or leading commas are worth it.
Try to make an argument for your code style, but in the end, agreed guidelines and consistency should always win.
Just hope that there is an automatic formatter that can fix your code.
Thank you for reading.