SQL Server: What's the limit on number of UNIONs?

From SQL 2008 BOL

"Any number of UNION operators can appear in a Transact-SQL statement"

Also from MSDN "Maximum Capacity Specifications for SQL Server" (2008):

Batch size (1) 65,536 * Network Packet Size

Length of a string containing SQL statements (batch size) (1) 65,536 * Network packet size

(1) Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.

To my mind, that means 268,435,456 bytes when using defaults. Please say your query is shorter than that.

Necromancing, because I now know the real-life answer.

Technically, like Neil Moss said, > 268 million bytes (ASCII characters), or > 134 million UTF-16 characters.

However, there is a limit of "256 tables per SELECT statement".

And while you can use more than 256 tables in a query WITH UNION STATEMENTS, such a query cannot be used as a view, table-valued function or as a subquery for a SELECT statement.

Also, if you use such a query directly, you may hit the stack space limit of the query optimizer at around 1'300-1'500 SELECTs.

I know, because thanks to a great (warning - sarcasm) OCP (original code "programmer"), we have such a f*ed up schema, and I actually ran into both, the 256 and the 1'300-1'500 limit.

(one table per object type, in case anybody wonders; with exactly the same column-schema (but not names) per table - instead of a reference table with object-type...)

According to this post somwhere between 8000 and 16000, for his particular query. Is probably mostly goverened by the resources available to your SQL Server.

It might be worth asking another question about the particular case you have for an alternative method that doesn't need so many unions.