Fundamentals: White space and Line Breaks

Something that is important in any language is formatting. I don't just mean in computer languages, such as Python (where good use of tabbing is required as part of the syntax), I mean in any written language. From T-SQL to English, Rust to Japanese and Braille to Russian, what ever language you're writing it's important to make sure it's well presented. Even this post you're reading now, I've tried to ensure has good formatting, and is why I use a code prettifier/beautifier.

So, why is formatting so important? One of the biggest reasons is that it makes your code readable. Not just for you, but for any one else that is going to read it, or might. When you write a document, which you know is going to very likely be read by someone else as you're probably writing it for that purpose, you make sure your document is easy on the eyes; or at least I hope you are. The same applies to your SQL you're writing.

Line Breaks

Line Breaks are great for making your code fit more nicely on screen. No one, and I mean no one, wants to see a mess that looks like this:

CREATE PROC dbo.DeleteArchives @OlderThan date AS BEGIN DECLARE @SQL nvarchar(MAX), @CRLF nchar(2) = NCHAR(13) + NCHAR(10); SET @SQL = STUFF((SELECT @CRLF + N'DROP TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE t.[name] LIKE N'x[_]%' AND t.modify_date < @OlderThan FOR XML PATH (N''),TYPE).value('.','nvarchar(MAX)'),1,2,N''); EXEC sys.sp_executesql @SQL; END;

Can you read that? Perhaps you can, but can you read it easily? No you can't. The sad truth is, however, that we've all seen code that looks like that. Maybe it's not in the definition of an object like that, but I've often seen single line statements in application code when it's over 200 characters long. If you get an error of some kind in the SQL, it's very difficult to debug.

Line breaks help split the statement into blocks as well, as you can include multiple line breaks to split parts of. For example, you might DECLARE some variables and then leave a gap after wards, and then start a SELECT statement. You might want a gap after and before you BEGIN and COMMIT a Transaction. It's up to you when and where you use them, but use them appropriate, and to help make the code you're writing more easily readable (and able to be seen without scrolling).

So with some Line Breaks, what does the above look like?

CREATE PROC dbo.DeleteArchives @OlderThan date AS
BEGIN

DECLARE @SQL nvarchar(MAX), @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF +
N'DROP TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
WHERE t.[name] LIKE N'x[_]%'
AND t.modify_date < @OlderThan
FOR XML PATH (N''),TYPE).value('.','nvarchar(MAX)'),1,2,N'');

EXEC sys.sp_executesql @SQL;

END;

Well, it's certainly better than it was before, but there's still something else we should be using.

White Space

White Space is also really important for readable code. Like I mentioned earlier, Python actually enforces this, which I honestly think is a good thing. The language requires you to indent code at specific points, such as inside an IF or WHILE, or when declaring a function, in the same way your would use braces ({}) in C#. This does make Python quite a readable language as each code block is easily identifiable.

For SQL, you should too be indenting your code but normally you want to do so to align the same parts of the query.With a SELECT, a common (sensible) thing to do is make it so that all the names of the columns align. For example:

SELECT Column1,
       Column2,
       Column3 + Column4 AS Column34

When I have a WHERE I like to make all of the clauses align on the right (and indent by a space when they are nested. So I have SQL that looks like this:

WHERE Column1 = 1
  AND Column2 = 'abc'
  AND (Column3 = 10
   OR  Column4 = 100)

Like I mentioned about things like an IF or WHILE, doing the same in SQL is also important. Such as when using a BEGIN...END or a TRY...CATCH, but also inside an object or CTE's definition for example.

The query above had the definition of an SP, as well as the declaration of some variables and a subquery. When you use some white space as well, you might end with a query that looks like this:

CREATE PROC dbo.DeleteArchives @OlderThan date AS
BEGIN

    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

    SET @SQL = STUFF((SELECT @CRLF +
                             N'DROP TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
                      FROM sys.schemas s
                           JOIN sys.tables t ON s.schema_id = t.schema_id
                      WHERE t.[name] LIKE N'x[_]%'
                        AND t.modify_date < @OlderThan
                      FOR XML PATH (N''),TYPE).value('.','nvarchar(MAX)'),1,2,N'');

    EXEC sys.sp_executesql @SQL;

END;

You can see I indented the code inside the BEGIN...END, but also aligned the names of the variables in the DECLARE and then made sure the subquery aligned nicely.

Use a style that suits you

How you use Line Breaks and Whitespace in a query, or any code, is up to you. Some (like myself) prefer to put the line break after the comma in the SELECT, others prefer to put it after the column name/alias and the prefix the next column with a comma. I've even seen SQL where all the commas are aligned with the start of the SELECT and the column names after it.

There's no set way you need to write SQL, how it looks is completely up to you. An important thing, however, is to be consistent with your formatting. It'll make it much easier for you to read when you have a style that you use consistently and automatically. When looking for specific things, you'll find you see them a lot quicker if your using a format style you're used to and is easily readable.