CTE
(Common Table Expressions) was first introduced with SQL 2005.it is nothing but
a temporary result set and execution scope of a single select, insert, and
update or create view. It is the best replacement for Views and exists only in
the scope of running query. CTEs can be used in Stored
Procedures, User Define Functions (UDFs), Triggers and Views. It is very
helpful when we don't have access to create object in a database. CTE can be declared once and can be used in multiple queries.
CTE can be recursive and non-recursive. It is similar to derive table that is not stored as an object.
It is stored in memory.
Common Table
Expression Syntax
Ø
The
CTE name and it is follows the WITH keyword
Ø
The
column list
Ø
The
query
Example
WITH CET_Emp
(FirstName,LastName,MiddleName,HireDate,BirthDate,LoginID,EmailAddress,Phone) AS
(
SELECT
FirstName
,LastName
,MiddleName
,HireDate
,BirthDate
,LoginID
,EmailAddress
,Phone
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
)
select *
from CET_Emp
|
The CTE is part of the subsequent statement only. The subsequent
statement can be a single SELECT/INSERT/UPDATE/DELETE, or a compound (with UNION,
INTERSECT etc).
CTE is a named temporary result set which is used to manipulate
the complex sub-queries data. This exists for the scope of statement. This is
created in memory rather than TempDB database. You cannot create any index on
CTE.
In CTE we can’t use constraints.
Benefit of using CTE
Ø
CTE
is un-materialized/ non-index able (cannot create indexes on CTE)
Ø
CTE
is logical/disposable View
Ø
CTE
persists only till the very next query
Ø
CTE
is mostly used for recursion, as CTE can call itself
Ø
CTE
resists in memory
Ø
Reusability
No comments:
Post a Comment
If you have any doubt, please let me know.