Common Table Expression(CTE) in Sql server 2005
Posted by Viral Sarvaiya on April 15, 2010
The common table expression is one of the new features in sql server 2005. It can be used instead of temp table or table variables in the stored procedures in the circumstances. Let’s see CTE with some example queries.
Most of the developers while writing the stored procedures they create the temp tables or table variables. They need some table to store the temporary results in order to manipulate the data in the other tables based on this temp result.
The temp variables will be stored on the tempdb and it needs to be deleted in the tempdb database.
The table variable is best when compare with the temp tables. Because the table variable initially will be there in the memory for the certain limit of size and if the size increase then it will be moved to the temp database. However the scope of the table variable is only up to that program. When compare with table variable the CTE is best. It just store the result set like normal view.
For more details refer following link