This is going to hurt a little

Imaging a 5100 line stored procedure that is doing a conversion of some data. After hundreds of site updates, you get called in to find a wee little bug that seems to have no consistent behavior but it is definitely in the conversion. Then some where in that 5100 lines of code you find these snippets - several hundreds of lines apart but interacting just as viciously:

CREATE TABLE #Duplicates
(
RecID INT IDENTITY (1,1)
,i_id INT
, and other things
)

..eventually some insert code fills this temp table. Later we find this new table:

CREATE TABLE #Itemslist
(
RecID INT IDENTITY(1,1)
,INo INT
, and other things
)

...which also gets properly loaded. Eventually final results are loaded into yet another temporary table. I will spare you the details and just point out the join here;

SELECT some stuff
FROM #Itemslist il
JOIN #Duplicates d ON il.RecID = d.RecID
JOIN etcetera

Fortunately the duplicate table was very small which is the only thing that contained the error that propagated from this.

Where else does one equal zero?

From more of the old code from Hyberia: We figure that this is the lazy man's (person's) method to making insert tables without all that messy typing.


SELECT rlk.*, rlk.i_id as i_id2 --CREATE #RSLAG table
INTO #RSLAG --note.. the i_id2 column was added to
FROM RSLAG rlk --cause the original i_id column to be
WHERE 1 = 0 --created without the IDENTITY property

SELECT rlk.* --CREATE #RSLAG_total table
INTO #RSLAG_total
FROM #RSLAG rlk
WHERE 1 = 0

Query to NoWhere

In a three hundred line stored procedure (not bad) one developer noted that the performance issue came down to the corrolated sub-query. Upon inspection, this left-joined monster was not even referenced by any output column, where clause or reference.

What a FIND!

SELECT
a_bunch_of_stuff
FROM #RPT_ORDERS ot
LEFT JOIN (SELECT
Another_long_painful_query
FROM #RPTEXTRA toc
INNER JOIN A_BUNCH_OF_STUFF_TO_MAKE_IT_EVEN_SLOWER tm
WHERE toc.x = 400
GROUP BY toc.y
,tm.starts
) mt ON tmt.ord = mt.ord
AND tmt.starts = mt.starts
ORDER BY more_stuff