I have 2 different queries for identifying dups (below). The only difference between the 2 queries is that one uses a CTE and the other uses a #Temp table.
Does anyone know why the CTE is so much faster than the #Temp table (0:20 seconds VS. 1:22)?
I would rather use the CTE but I need to run 2 STATEMENTS using the CTE (DELETE from CTE and then INSERT INTO table FROM CTE) but SQL Server only allows you to write one STATEMENT on the CTE.
WITH DUPS AS( Id, Column1, Column2, Column3, RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id) FROM mytable ) Select top 1 * FROM DUPS WHERE RN > 1
SELECT Id, Column1, Column2, Column3, RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id) INTO #DUPS FROM mytable Select top 1 * FROM #DUPS WHERE RN > 1