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):
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.