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.

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

Wait a minute

OK, Check this out. There are no errors in 2005 or 2008 even with that comma dangling off the end ...

CREATE TABLE [dbo].[AdvertiserSamCategory] (
[AdvertiserSAMID] [int] IDENTITY (1, 1) NOT NULL ,
[AdvertiserID] [INT] NOT NULL ,
[DomainLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SamCategoryID] [INT] NOT NULL ,
) ON [PRIMARY]

Guess you really don't need that definition