Stupid T-SQL Tricks

Presented without comment:

Next up, can you break up a query with spaces? Yep:

Well if you can do that – can you break up a query across lines? Sure you can:

And now, brace yourself: this one is so weird that I can’t even embed it in the blog. I’m just going to show you a picture of it first:

Look carefully. This shouldn’t work, right?

You’re probably going to go through a few phases as you look carefully at that code:

  1. “Is it going to work?”
  2. “No – that can’t possibly work.”
  3. (Then you’re going to copy/paste the code from a Github gist and try it, but here’s the key: don’t try to edit it yet, just run it.)
  4. “How did that work?”
  5. “Why doesn’t the proc return any results?”

And then you’re going to try editing it, and things are going to get really weird. I wish I had a webcam to see your face as you worked through it. I’ll follow up with the secret in a later post. Here’s your only clue: Solomon Rutzky knew what it was as soon as he saw the code.

Previous Post
Is Your Database Databasic?
Next Post
How Table Variables Mess With Parallelism

31 Comments. Leave new

  • Similar idea as : “Revenge: The SQL!”
    https://sqlbits.com/Sessions/Event12/Revenge_The_SQL

    Reply
  • I put a “hex” on you.

    Reply
  • Kenneth Fisher
    June 6, 2018 10:41 am

    You missed no spaces at all 😀

    SELECT[SELECT]FROM[FROM]WHERE[WHERE]LIKE[LIKE];

    https://sqlstudies.com/2017/04/13/no-space-queries/

    Reply
  • On the last one, I’d hazard to guess that the names of the similar looking columns/parameters are comprised of similar looking unicode characters.

    Reply
  • Michael McCrone
    June 6, 2018 11:39 am

    CTRL+F “?????” That’s my hint.

    Reply
  • Ernest Libertucci
    June 6, 2018 1:32 pm

    Great use-case for quoted identifier!

    Reply
  • Solomon Rutzky
    June 6, 2018 4:09 pm

    I’ve always wondered why you are allowed to have a trailing comma within the parenthesis of a CREATE TABLE statement. Not a “trick” per se, but odd. Also, don’t forget about single-character variables and local temporary tables. For example (including a bonus trick):

    — DROP TABLE #;
    CREATE TABLE # (? INT, );
    DECLARE @ INT;
    SELECT ? FROM # WHERE ?= @;

    I’m not sure if the above with copy/paste correctly, or even encode/decode properly, but we shall see in a moment 😉

    Reply
    • Solomon Rutzky
      June 6, 2018 4:17 pm

      Well, it looks like that did not encode properly. There was not supposed to be a “?” in that example. This page claims to be encoded as UTF-8, so I guess that character got lost somewhere on the back-end. If this next attempt works, then I think I can post it correctly. If not, then maybe I will just comment on the Gist. Either way, happy cat: 😺 (or at least attempted happy cat 😉 )

      Reply
      • Solomon Rutzky
        June 6, 2018 4:19 pm

        Hey, that did work. So, final attempt:

        CREATE TABLE # (ᅟ INT, );
        DECLARE @ INT;
        SELECT ᅟ FROM # WHERE ᅟ= @;

        Reply
  • Zachary Faragher
    June 6, 2018 6:49 pm
    Reply
  • Robert McCormick
    June 6, 2018 7:57 pm

    It’s not often my past experience as an assembler programmer comes to the rescue. UTF8 bytestring to the rescue! I learned something today.

    Reply
  • I recommend materials from the session of Marek Adamczuk from the sqlday2018 conference.
    I liked the sql inside the function.
    https://github.com/SQLDay/2018/blob/master/Marek%20Adamczuk/sqlday2018.zip

    Sample:
    create or alter function dbo.arith_add (@arg1 float = 0, @arg2 float = 0)
    returns float
    as
    begin
    return (@arg1 + @arg2);
    end;
    GO

    create or alter function dbo.arith(@operation nvarchar(20),@arg1 float = 0, @arg2 float = 0)
    returns float
    as
    begin
    declare @fnname sysname, @result float;
    select @fnname = ‘dbo.arith_’+@operation;
    exec @result = @fnname @arg1, @arg2; –WHAT???
    return @result;
    end;
    GO

    select
    dbo.arith(‘add’,6,2) as [add] –8=6+2

    Reply
    • Solomon Rutzky
      June 7, 2018 1:54 am

      Hi there. That is creative, but to be clear, it is not Dynamic SQL. EXEC does let you specify the module to execute via a variable (just the module, no other T-SQL), and you can execute scalar UDFs (in which case the UDF can actually have optional parameters, like procs, and you can ignore the return value). You can, however, do real Dynamic SQL in a scalar UDF using SQLCLR (since all SQL submitted via SQLCLR objects is Dynamic SQL).

      Reply
  • Luc Van der Veken
    June 7, 2018 1:26 am

    SMSS, Ctrl+F and copying one of the .oOo. into the search field quickly (well, almost quickly) lead to the same conclusion as others before me, saving and looking at it in a hex editor confirmed it, but then I noticed something really strange for which I found no explanation yet.
    On github, at least when I look at it here in Chrome, there’s an @ and a [ that swapped positions in line 23. When I copy the entire block of code and paste it into SMSS or Notepad, they magically jump back in place.

    Reply
  • hmmm – this is beyond me. “And then you’re going to try editing it, and things are going to get really weird. ” . What would you like to change? EXEC [?????].[?????].[?????]
    @????? = N'[1]’,
    @????? = N'[2]’,
    @????? = N'[3]’,
    @????? = N'[4]’
    GO gives the same result, what i expected. So there must be something weird going on what everyone notices, but i don’t. I don’t understand how these columns got the same name, but… what weirdness should we encounter when editing?

    Reply
  • Goran Stevanovic
    June 7, 2018 10:31 am

    Quite amusing. Surely the columns in a table can’t have same names so those funky characters can’t be identical. Here’s how to compare them:

    USE [?????]
    GO

    SELECT
    NAME AS “Column Name”,
    LEFT(NAME,1) AS “Left Char”,
    UNICODE(LEFT(name,1)) AS “Left Char Code”,
    RIGHT(NAME,1) AS “Right Char”,
    UNICODE(RIGHT(name,1)) AS “Right Char Code”
    FROM sys.all_columns WHERE object_id=OBJECT_ID(N’?????.?????.?????’)

    Reply
  • this works too: SELECT*FROM sys . databases;

    Reply
  • That really made us laugh!
    A little more…
    CREATE TABLE dbo.[FROM] ([SELECT] INT, [WHERE] INT, [LIKE] INT, [ON] INT);
    GO
    CREATE TABLE dbo.[JOIN] ([=] INT, [AND] INT, [] INT, [&] INT, [OR] INT);
    GO
    SELECT [SELECT] FROM [FROM] JOIN [JOIN] ON [ON] = [=] WHERE [WHERE] LIKE [LIKE] AND [AND] < [ [>];
    GO

    Reply
    • That didn’t appear as planned, there are greater-than, less-than and ampersand columns being used against themselves – you get the idea.

      Reply
  • My hint is to try combination of MDCLXII, MDCCLXXVI

    One thing i don’t know yet: flipping of brackets and other after treating code as literal.

    Reply
  • ????? isn’t equal to ?????

    Reply
  • Oddly enough, select*from yourTable doesn’t cause a parsing error. Something with * here doesn’t require a space.

    Reply
  • antonio viarengo
    November 20, 2018 4:21 am

    Brent, i do not know if you have seen my comment on github , i suggest this to make it funnier :
    CREATE TYPE [?????] from nvarchar(20)
    SO you can avoid to refer to nvarchar(20) and can use [?????] as datatype
    CREATE TABLE [?????].[?????]([?????] [?????], [?????] [?????], [?????] [?????], [?????] [?????]);
    and the same in CREATE PROC
    😀

    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":""}