My guess you expected that SQL Server will run each query separately but it's not the case. SQL Server treats it as one query regardless of how many "AS" parts it has.


>I was surprised when the following query was extremely slow:
>
>

>SET NOCOUNT ON
>
>WITH DoublePunches (puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, FXCount)
>AS
>(
>select puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, count(*) AS FXCount
>		FROM handpunch_punches 
>		WHERE DelFlag = 0 
>		GROUP BY puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut 
>		HAVING count(*) > 1
>)
>, RestPunches (puKey, puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut)
>AS
>(
>SELECT MAX(Handpunch_Punches.puKey) AS puKey,
>		Handpunch_Punches.puEmKey, 
>		Handpunch_Punches.puDateIn, 
>		Handpunch_Punches.puTimeIn, 
>		Handpunch_Punches.puDateOut, 
>		Handpunch_Punches.puTimeOut
>		FROM Handpunch_Punches
>		JOIN DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		GROUP BY Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut 
>)
>
>DELETE Handpunch_Punches
>		FROM Handpunch_Punches
>		JOIN DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		WHERE NOT EXISTS(SELECT NULL FROM RestPunches WHERE RestPunches.puKey = Handpunch_Punches.puKey);
>

>
>While the query using temporary tables was extremely fast:
>
>

>SET NOCOUNT ON
>
>SELECT puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut, count(*) AS FXCount
>		INTO #DoublePunches
>		FROM handpunch_punches 
>		WHERE DelFlag = 0 
>		GROUP BY puEmKey, puDateIn, puTimeIn, puDateOut, puTimeOut
>		HAVING count(*) > 1
>
>SELECT MAX(Handpunch_Punches.puKey) AS puKey,
>		Handpunch_Punches.puEmKey, 
>		Handpunch_Punches.puDateIn, 
>		Handpunch_Punches.puTimeIn, 
>		Handpunch_Punches.puDateOut, 
>		Handpunch_Punches.puTimeOut
>		INTO #RestPunches
>		FROM Handpunch_Punches
>		JOIN #DoublePunches DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		GROUP BY Handpunch_Punches.puEmKey, Handpunch_Punches.puDateIn, Handpunch_Punches.puTimeIn, Handpunch_Punches.puDateOut, Handpunch_Punches.puTimeOut 
>
>DELETE Handpunch_Punches
>		FROM Handpunch_Punches
>		JOIN #DoublePunches DoublePunches ON Handpunch_Punches.puEmKey = DoublePunches.puEmKey
>				AND Handpunch_Punches.puDateIn = DoublePunches.puDateIn
>				AND Handpunch_Punches.puTimeIn = DoublePunches.puTimeIn
>				AND Handpunch_Punches.puDateOut = DoublePunches.puDateOut
>				AND Handpunch_Punches.puTimeOut = DoublePunches.puTimeOut
>		WHERE NOT EXISTS(SELECT NULL FROM #RestPunches RestPunches WHERE RestPunches.puKey = Handpunch_Punches.puKey);
>
>
>DROP TABLE #DoublePunches
>DROP TABLE #RestPunches
>

>
>So either I misunderstand the purpose of WITH or there is something else I am not aware of?