- We can take CTE as tempory view (can be thought as tempory result set) that create in memory
- Its scope valid until next query run
- CTE need to use just after it declaration
- There are two type Recursive and Nonrecursive CTE
- Can define multiple CTE
- CTE are unindexable ( it cau use exist index in referenced object)
- CTE can't have constrain
- ORDER BY clause can't be used in the CTE_Query_Definition , execpt when use TOP keyword
Simple Example
ex 1
WITH CTE_CUSTOMER_EX2
( [CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
)
AS
(
SELECT [CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
FROM .[dbo].[Customers]
)
SELECT * FROM CTE_CUSTOMER_EX2
( [CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
)
AS
(
SELECT [CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
FROM .[dbo].[Customers]
)
SELECT * FROM CTE_CUSTOMER_EX2
ex 2
;WITH CTE_CUSTOMER_EX1
AS
(
SELECT [CustomerID]
,[CompanyName]
,[ContactName]
FROM .[dbo].[Customers]
)
SELECT * FROM CTE_CUSTOMER_EX1
Note
If there are query before CTE define , need to use Semicolon teminator before declare CTE
Multiple CTE
With CTE_M1 as
(
-select statement
)
, CTE_M2 as
(
-select statemet
)
you can find some more detail and examples using this Link and Link2
No comments:
Post a Comment