How many characters long may an SQL Server SQL sta

Question

What is the maximum length of an SQL statement in SQL Server? Does this length depend on the version of SQL Server?

For example, in

DECLARE @SQLStatement NVARCHAR(MAX) = N'Something' 
EXEC(@SQLStatement) 

the @SQLStatement is allowed to be X characters long. What is X?

Answer 1

It's going to be 65,536 times your network packet size, according to the documentation

Seems like this is the same at least since SQL Server 2005

Answer 2

Verbatim from the documentation:

65,536 * Network Packet Size

What isn't clear, but which any sane person would immediately question, is what does "Network Packet Size" mean?

Network packet size, in this case, as expected, does not refer to physical size, since this would break the abstraction of end-to-end guaranteed delivery streaming protocol (TCP), but rather refers to packet size in the context of the Tabular Data Stream protocol used by SQL Server for communication. According to the same documentation, the default size of a TDS packet is 4KB.

Answer 3

If your variable is defined as NVARCHAR(MAX) then it can be 2GB in size (remember that NVARCHAR uses two bytes for every character, compression aside). If you are approaching anywhere near this size, however, the length of your SQL statement is probably the least of your worries.

Answer 4

The length of an SQL statement should be as short as possible while maintaining human comprehensibility.

Since parsing and compiling the SQL statement into an actual job for the DB engine is very fast, the length of the statement doesn't really matter to the parser. Of far more importance, especially for a hand-constructed SQL statement, is that a human can maintain that statement in the future.