Is it a good idea to use CTE over Cursor?

SqlTeffy 2015-09-13 06:31:18

Is it a good idea to use CTE over Cursor? I tried replacing Cursor with a Table Variable and there was performance improvement from 50 minutes to 2 minutes for about 1000 rows.

In a Table Variable I created a RowId column and in a WHILE loop I used SELECT to get rows by RowID and then performed UPDATE.

Someone recommended to use a CTE as it is more flexible to use. You don't have to use RowId and SELECT instead it just works like Cursor.

The CTE code used is as below:

;With CTE (num)
As
(
  select unqnum from t1
)  
update T2 set T2.sales = T2.sales + 1
from CTE inner join T2 
on CTE.num = T2.unqnum

I want to know whether CTE is good if you want to perform UPDATE row-by-row on over 2000 rows.

Aaron Bertrand 2015-09-13 14:51:09
Let's step back, forget about table variables, cursors, and CTEs, and explain exactly what you are trying to do to the base table? It seems you are stuck in the dangerous but common mindset across many database platforms that you want to do some individual thing to each row, when the way you should be thinking about it is what action you need to take against some set of rows. In this case it seems like you are materializing some kind of sequence number in the base table? Why? This is the kind of thing you can always calculate at runtime; keeping a copy of this redundant data in the table just incurs more maintenance for every single row change, just for the questionable benefit of not having to determine the row number at query time.