Why I define my Carriage Returns and Line Breaks as variables in Dynamic SQL

A question I've been asked on several occasions is "Why use NCHAR(13) + NCHAR(10), or @CRLF in your code, and not use inline new lines in your Dynamic SQL?" So, what someone means is why do I write my dynamic statements like this:

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

SET @SQL = N'SELECT *' + @CRLF +
           N'FROM ' + QUOTENAME(@TableName) + N';';

Instead of code like this:

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT *
FROM ' + QUOTENAME(@TableName) + N';';

I actually have a couple of reasons for this.

SQL Server is on Windows and Linux

As SQL Server is available on both Windows and Linux environments, being aware of what the new line characters are is important. By default on Windows the new line characters are a Carriage Return followed by a Line Feed, on Linux however, a new line is simply represented by a Line Break. This means that someone typing SQL may not have the same default new line characters across environments or when providing a solution to someone else. Take the below Dynamic SQL example:

DECLARE @SQL nvarchar(MAX);
SET @SQL = STUFF((SELECT N'
UNION ALL
SELECT N' + QUOTENAME(CONCAT(s.[name],N'.',t.[name]),N'''') + N' AS TableName,
       COUNT(*) AS [RowCount]
FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,13,N'') + N';';
PRINT @SQL;
EXEC sp_executesql @SQL;

Here I used a value of 13 for the number of characters at the start to remove. This therefore assumes that there is both a Carriage Return and Line Break, but what if I typed that SQL on Linux?

Well actually I did type it in a Linux environment, as I'm mainly a Kubuntu user at home. Now, what characters represent a New Line above? You don't know (well, you could inspect the HTML of this page and find out), and that's important. There are actually both a Carriage Return and Line Break in the above, but that's because I have my Eol setting in ADS as \r\n. Others may not have that setting.

So, what would happen to the above, if someone didn't have those Carriage Returns? Well the statement would have SE from the first SELECT missing, and so the query would be invalid.

Formatting is important

This seems like a silly thing to say, but having readable SQL is a huge benefit to having maintainable code. If you look at the above code, you'll notice that it's misaligned. That's due to the fact that I have used inline new lines in the literal string. Instead, you do see some people do the below instead:

DECLARE @SQL nvarchar(MAX);
SET @SQL = STUFF((SELECT N'
                  UNION ALL
                  SELECT N' + QUOTENAME(CONCAT(s.[name],N'.',t.[name]),N'''') + N' AS TableName,
                         COUNT(*) AS [RowCount]
                  FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,13,N'') + N';';
PRINT @SQL;
EXEC sp_executesql @SQL;

The problem, however, I have with this is that the printed SQL is misaligned, with every line having a huge indentation at the start. This isn't too bad on the above, but with more complex statements this approach can make the printed statement an eyesore.

As a result, I use a variable to insert the new lines, keeping a tidy format for both the written and printed (dynamic) SQL:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = STUFF((SELECT @CRLF +
                  N'UNION ALL' + @CRLF +
                  N'SELECT N' + QUOTENAME(CONCAT(s.[name],N'.',t.[name]),N'''') + N' AS TableName,' + @CRLF +
                  N'       COUNT(*) AS [RowCount]' + @CRLF +
                  N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,13,N'') + N';';
PRINT @SQL;
EXEC sp_executesql @SQL;

This maintains a readable statement above, and also the generated statement is nicely aligned as well.