Does SQL Server / T-SQL support line-continuation to break up long strings?

Question

I sometimes have a SQL script that has one or more super-long (sometimes even stupid-long) strings. Typically these are VARBINARY literals / constants that represent files / Assemblies, but occasionally they are text.

The primary problem with really long strings is that some text editors do not handle them all that well. For example, I have a VARBINARY literal that I use in a CREATE ASSEMBLY [AssemblyName] FROM 0x.... statement, and the Assembly itself is just over 1 MB in size, which equates to just over 2 million characters in a text file since each byte requires two characters to be represented in hex notation (e.g. 0x1F = a 1 and an F). SQL Server Management Studio (SSMS) does not handle this well and hangs for several seconds as I try to scroll over that line. And in fact, some versions (not sure if this still happens) will even display a warning about long lines when opening a script that has at least one line over a certain length.

A secondary issue is that it complicates the formatting when either using in an editor without word-wrap enabled, or posting online. The problem here is that the slider for the horizontal scroll bar is very narrow and moving it even just a little bit usually scrolls the non-super-long text out of view.

Now, T-SQL doesn't terminate commands with newlines or even semi-colons (though semi-colons are preferred / recommended, starting with SQL Server 2005). So since SQL Server knows how to parse each statement such that it knows when it ends, it seems like splitting the long line across multiple lines, separated only by a newline / carriage-return + line-feed, doesn't seem unreasonable. But this doesn't work in either case.

PRINT 'Line1
Line2';

returns (in the "Messages" tab):

Line1
Line2

And that makes sense enough as the newline is within a literal / constant. But doing this for a VARBINARY also doesn't work.

PRINT 0x1234
5678;

gives me an error.

Answer 1

Thankfully there is support for line-continuation in T-SQL via the \ (backslash) character. Just place that at the end of of a line, right before the newline / carriage-return + line-feed, and the newline will be ignored.

For text strings, this behaves as follows:

PRINT 'Line1\
Line2';

returns (in the "Messages" tab):

Line1Line2

For binary / hex strings, this behaves as follows:

PRINT 0x1234\
5678;

returns (in the "Messages" tab):

0x12345678;

---

In order to format binary files (Assemblies, Certificates) into text hex-byte strings for use in SQL scripts, I wrote a command-line utility called BinaryFormatter that I have released as open-source on GitHub. Not only does it convert binary file into a text representation, but it also uses line-continuation to spread long VARBINARY literals across as many rows are needed, based on the specified number of character to use per each row. The result is something along the lines of:

4D5A09DE34F178313345A4\
00007F4E39782EFC48D842\
00000000

which I then copy and paste into my script, as shown in the {...} area below:

CREATE ASSEMBLY [AssemblyName]
FROM 0x\
{output from BinaryFormatter}
;

For additional details on this topic, please see my blog post: Line-Continuation in T-SQL