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:

My favorite Thanksgiving side dish

My favorite Thanksgiving side dish

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:

Including 8, 6, 7, 5, 3, 0, and 9

Including 8, 6, 7, 5, 3, 0, and 9

Now we’ll use those to build dynamic SQL:

Using SQL to Build SQL

Using SQL to Build SQL

And I can just copy/paste that stuff into my SELECT statement and run it:

Hey, Mister DJ, I thought you said we had a deal

Hey, Mister DJ, I thought you said we had a deal

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:

Lots of Stuffing

Lots of Stuffing

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:

Hovercraft

Hovercraft

Right-click on the plan and click Show Execution Plan XML, and you can see exactly where SSMS clips it:

Cuts out at n156 (of 4096)

Cuts out at n156 (of 4096)

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.

Previous Post
Bad Idea Jeans Week: Building a Fork Bomb in SQL Server
Next Post
Bad Idea Jeans Week: Dynamically Generating 999 Indexes on a Table

12 Comments. Leave new

  • Excellent post Jenny, ermm.. Brent. I got your number.

    Reply
  • Dng t, nw ‘v gt tht sng n m hd!!!

    Reply
  • Stephen A Mangiameli
    August 24, 2016 9:46 am

    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.

    Reply
    • This is the “Bad Idea” post. 😉

      Reply
      • 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?”
        😉

        Reply
      • 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

        Reply
  • “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.

    Reply
  • Glorious glory is for querulous queries.

    Reply
  • So is the 65,535 return limit a result of SSMS being 32bit as it was in Excel?

    Reply
  • 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 🙂

    Reply
  • “Good code documents itself” – Every lazy programmer

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}