As part of an experiment, I needed to build a really long query. (Don’t ask.)
From another recent experiment, I know that SQL Server won’t let a query return more than 65,535 columns. I set about writing a one-line query that would return 65,535. I’m a big fan of writing the simplest reproduction scripts possible – I don’t want them to rely on tables if they don’t have to – so we’ll start a CTE like this:
That’s a simple CTE which gives me a fake table called Stuffing, with one field named Stuff.
I want to select that Stuffing field 65,535 times, but ideally, I’d like to return that field with a different name each time. (I might use this to artificially insert 65,535 columns later.) I’ll need a trusty numbers table (like Method #7 from this StackOverflow answer), which will give me 65,535 rows that I can use to build dynamic SQL:
Now we’ll use those to build dynamic SQL:
And I can just copy/paste that stuff into my SELECT statement and run it:
Alright, so we’ve learned that I can’t return more than 65,535 columns, AND I can only use 4,096 elements in my SELECT. I can think of several workarounds there – 65,535 / 4096 = about 16, which means I could create a few tables or CTEs and do SELECT *’s from them, thereby returning all 65,535 columns with less than 4,096 things in my SELECT. But for now, we’ll just start with 4,096 things in my SELECT:
Presto! We’ve got stuffing. As long as we’re here, it’s interesting to see how SQL Server clips off queries in execution plans. If we hover the mouse over the query in a plan, we can see the query – or at least, the beginning of it:
Right-click on the plan and click Show Execution Plan XML, and you can see exactly where SSMS clips it:
This is why I say you should never comment your code. You want to make sure you can see your entire query in its glorious glory. If you absolutely have to comment your code, avoid vowels at all costs, and type like a 14 year old texter.