As Microsoft state that CTE is :- A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
In my previous article paging functionality in sql server, i used CTE in store procedure to gain paging functionality. It is used to hold data temporarily as temporary table and table variable done.It is fast as compare to both.
Following are structure to create CTE:
Have a happy coding.....!
In my previous article paging functionality in sql server, i used CTE in store procedure to gain paging functionality. It is used to hold data temporarily as temporary table and table variable done.It is fast as compare to both.
Following are structure to create CTE:
WITH myCTE (
Field1
,Field2..
)
AS (
SELECT Column1
,Column2
FROM myTable
)
SELECT *
FROM myCTE
CTE can be nested as
WITH myCTE (
Field1
,Field2..
)
AS (
SELECT Column1
,Column2
FROM myTable
)
,myCTE1 (column11)
AS (
SELECT col1
FROM myTable2
)
SELECT *
FROM myCTE
,myCTE1
Have a happy coding.....!