Example of CTE to prove it is better than Cursor

I have read that CTE are better than cursor. But I开发者_运维百科 am unable to find the simple clear example which can prove this. I am newbie in Sql Server 2005 and to understand it I need a simple example in which we are storing value via CTE and processing it one by one.

---

If you use Cursor, the sequence of rows are get executed one by one because it fetch only one row at a time. So it takes more time when we use Cursor in tables which has more rows.

---

CTE yields a faster result in a recursive scenario. The result of CTE is repeatedly used to get the final resultset. So since you have taken your where clause or subquery in CTE, definitely it is going to show performance improvement. While cursors flow of execution is synchronous(it executes one by one), it is bound to take more time. Reference : http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Just a note, in many scenarios, temp tables gives better performance then CTE also, so you should give a try to temp tables as well.
Reference : http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d040d19d-016e-4a21-bf44-a0359fb3c7fb