The Best Worst Code That Never Was

We All Hear What We Want To Hear

When Kendra Little (b|t) announced the #BESTWORSTCODE contest, I thought we’d be showing off bad and quirky code that we’d seen or written.

So I went digging through my personal archives, did a little updating, and proudly came up with the below examples.

It turns out that wasn’t at all what was going to happen! Kendra, of course, had much better ideas. And now I have the pubs database probably forever.

Anyway, presented without comment is what I was going to submit.

Thanks for reading!

/*We don't really have to name anything*/
DECLARE @ TABLE(_ INT DEFAULT 0);
INSERT @ (_)
DEFAULT VALUES;

/*Do we ever divide?*/
SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS [1/0]
FROM @ #@
WHERE EXISTS
(
    SELECT 1/0 AS [1/0]
    FROM @ AS ##@
    WHERE #@._ = ##@._
);


/* Highly illogical. ??? */
IF (1 + 2 = 4)
    BEGIN
        DECLARE @ TINYINT;
    END;
SELECT @ = 1;
SELECT @ AS [?];
GO

IF (2 + 1 = 4)
    BEGIN
        DECLARE @ TINYINT = 1/0;
    END;
SELECT @ AS [?];
GO


/*Conventional Naming.*/
DROP TABLE IF EXISTS #;

CREATE TABLE # (int INT, INDEX xml CLUSTERED (int));
INSERT # ( int )
SELECT varbinary.sysname
FROM 
(
    SELECT TOP (1000 * 1000) 1e0 AS sysname
    FROM sys.messages AS smallmoney
    CROSS JOIN sys.messages AS money
) AS varbinary;


/*This is fine.*/
SELECT TOP (2-1) sql_variant.int
FROM # AS sql_variant;

/*This is also fine.*/
ALTER TABLE # ADD real AS int / 0e0;

/*How many errors should I get?*/
SELECT SUM(ROWCOUNT_BIG()) AS float
FROM # AS sql_variant
GROUP BY ();


/*Oh, now you care.*/
CREATE INDEX columnstore ON #(real);

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.