News & Opinion
Bad Idea Jeans Week: Dynamically Generating Long Queries
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.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Excellent post Jenny, ermm.. Brent. I got your number.
Dng t, nw ‘v gt tht sng n m hd!!!
Don’t comment your code? Maybe during testing, but my memory isn’t good enough to remember what I wrote last week let alone 6 months ago. Code comments help me jump through code quickly, especially if it’s somoeone else’s circus.
This is the “Bad Idea” post. 😉
So, therefore, shouldn’t your comments properly and completely document everything you were or might have been thinking during the creation of the piece of code that you’re currently working on?
Possibly going so far as to indicate not only your thoughts but what you are doing at each step of the way? Comments such as /*now I am typing comments such as*/ to indicate that you weren’t really thinking of anything other than to be typing the comment about what you were typing?
You know, going for commentception?
/*For those who miss a 6 year old movie reference, the above was referring to the movie Inception released in 2010 and starring Leonardo Decaprio /*Who I’m sure I mis-spelled his last name but I’m too lazy to go back and check for the correct spelling*/*/
Or would that also fall into the category of “Bad idea?”
😉
Looks like everyone isn’t reading the title this week. Perhaps putting the title in neon with flames around it will do the trick. “Definitely use flames, nothing is better than putting flames on every webpage” – 1990’s Web Developer
“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.”
Too funny 😀
It’s a shame that many developers seem to take that as Rule #1 though.
Glorious glory is for querulous queries.
So is the 65,535 return limit a result of SSMS being 32bit as it was in Excel?
Garland – I doubt it because you can return millions of rows to SSMS without an issue. Good idea though.
These bad idea jeans posts are great, whether people want to believe it or not you just might encounter this stuff to some degree in the wild 🙂
“Good code documents itself” – Every lazy programmer