Why my way of writing SQL is superior

⚠️ 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 🔗︎

Adding new lines 🔗︎

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 vs JOIN

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.