Wednesday, September 23, 2009

A Little Mischief

Every now and then, I like to stir up a little mischief.

Sometimes I’ll go into Starbucks and order an Iced Personal Half-Caff Quad Venti Extra-Vanilla Nonfat One-Sugar Two-Honey With-Whip No-Foam Latte, just to see what kind of reaction I get. (Seriously… I really do).

The vast majority of baristas will stare back at me with a vacant expression and say, “Er… Whu-ut?” Some will just smile or laugh out loud, knowing I’m just kidding around. But on rare occasions, a barista will come right back at me without batting an eyelash and ask with all seriousness, “Do you want a Petite Reduced-Fat Cinnamon Old-Fashioned Zucchini-Walnut Muffin with that?”

They aren’t as much fun.

SQL Server is kind of like that type of barista. It’s not fun trying to get a reaction out of SQL Server. It’s hard to trip it up. The other day, for kicks, I wrote up an obnoxious, impossible-to-understand query (see below), and SQL processed it without question. Occasionally, I think it’d be kind of cool for SQL to be in on the joke and come back with a message saying, “Oh, come ON… Get real!”

/* Initialize */ 
if object_id('tempdb..#from') is not null drop table #from;
go


/* The 'mischief' query: */

with [top] as (select [top]=100),[where](

[select],[top100],[percent],[into],[order],
[from],[employees],
[over],[partition],[by],[column],
[go]
)as(

select EmployeeID,null,FirstName
,LastName,ReportsTo,City
,HireDate,1,1,5,null,Title
from Northwind.dbo.Employees)

select top (select [top] from [top])
percent [percent] as [as], [sum]=sum ([over]) over(
partition by [partition],[by]),
[into] into #from from [where] where
[order]
=[by] order by [order],[by],
[go];
go

4 comments:

  1. ooh, this is rich Brad. :)

    Let me see if i can come up with something along your silly scheme:

    WITH [FROM] AS (SELECT 'SELECT' [SELECT], 'AS' AS [AS], 'FROM' [FROM], 'WHERE' [WHERE], '=' [=])
    SELECT [SELECT], [AS] AS [AS], [FROM] FROM [FROM] WHERE [WHERE] = [=];


    WITH [SELECT Brad FROM Schultz WHERE Status = 'Silly'] AS(SELECT 'Brad' [Brad], 'Silly' [Status])
    SELECT Brad FROM [SELECT Brad FROM Schultz WHERE Status = 'Silly'] WHERE Status = 'Silly';

    CREATE TABLE [FROM]([FROM] CHAR);
    DELETE FROM [FROM] FROM [FROM] [FROM];
    DROP TABLE [FROM];

    ReplyDelete
  2. @Brian: Yes... excellent! (Except for the fact that you misspelled my last name). Keep 'em coming.

    ReplyDelete
  3. @Brad

    Oops. I see i added the T. Sorry.

    I'm curious what is the maximum amount of times a word can be repeated. The DELETE statement has 5 FROMs.

    There's also ASCII art, but i don't know if it would translate well on this page.

    Finally, [[] works, but [[]] does not. Hmm...

    ReplyDelete
  4. @Brian:

    Double up the closing square bracket, just like doubling up single quotes in literals. In other words, [[]]] will work.

    Hmmm... a new challenge... the most times a word can be repeated. The DELETE statement was impressive. ;)

    ReplyDelete