What is CTE in Sql server 2005 ?

 Posted by Rajkatie on 2/18/2012 | Category: Sql Server Interview questions | Views: 4451 | Points: 40

A common table expression (CTE) can be thought of as a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a derived table. Unlike a derived table, a CTE can be self-referencing, not stored as object and can be referenced multiple times in the same query.

It can be recursive and non-recursive.

It provides the significant advantage of being able to reference itself because earlier version sql server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive step.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Source: http://msdn.microsoft.com/en-u | Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Sheonarayan on: 9/21/2012 | Points: 10
Below is the code snippet for the CTE Where PersonalDetail is the name of the physical table.

WITH myTable (FirstName, LastName, Age)

SELECT FirstNAme, LastName, Age FROM PersonalDetail WHERE AutoID > 3

SELECT * FROM myTable WHERE FirstName = 'Yashoda';

Hope this helps.
Posted by: Sqldev on: 3/11/2013 | Points: 10

Below video have complete information about CTE like what is CTE , properties of CTE and examples for CTE in sql server.


Login to post response