CTE or Common Table Expressions, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table and can be declared once and referenced multiple times in a query. One major advantage over derived table is, CTEs can be recursively defined and so it eliminates the need for recursive stored procedures.

In general, we use CTE for data selection only. But a CTE can be used for INSERT, UPDATE, DELETE and MERGE operations as well. Like derived table, a CTE can be used in functions, stored procedures, triggers, or views. In other words, CTE is temporary named result set of a query and defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement.

The general syntax of a CTE is:

I will write more on using CTE but for today, have a look into a recursive CTE to get employee level of employee from employee table.



Rakesh Mishra

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook