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.