Hi friends,in this article i will explain about Common Table Expressions(CTE) in SQL SERVER 2008.
I already explained in the previous articles about Create your own captcha image generator in ASP.NET using C#.NET/VB.NET,How to create a RDLC report using asp.net with c# /VB.NET and Create ,list and Alter the trigger associated with a table with SQL Server?
I already explained in the previous articles about Create your own captcha image generator in ASP.NET using C#.NET/VB.NET,How to create a RDLC report using asp.net with c# /VB.NET and Create ,list and Alter the trigger associated with a table with SQL Server?
Syntax:
A Common Table Expression contains three core parts:
The CTE name (this is what follows the WITH keyword)
The column list (optional)
The query (appears within parentheses after the AS keyword)
The query using the CTE must be the first query appearing after the CTE.
We commonly write the queries as below.But seeing this we can't understand clearly.
SELECT * FROM (
SELECT A.Username, A.Gender, A.Country From User_Details A Inner join Registration_details E on E.User_id = A.USER_ID) C
WHERE C.Country ='india'
ORDER BY C.username
|
WITH CTE(USER_NAME,Gender,Country)
AS
(
SELECT UD.Username, UD.Gender, UD.Country From User_Details UD INNER JOIN Registration_details RD ON RD.User_id = UD.USER_ID
)
SELECT * FROM CTE WHERE Country='India'
|
The output of above two queries are same as shown below.
If we use two CTE's as shown below.
WITH CTE1(USER_NAME,Gender,Country)
AS
(
SELECT UD.Username, UD.Gender, UD.Country From User_Details UD INNER JOIN Registration_details RD ON RD.User_id = UD.USER_ID
),
CTE2(Name)
AS
(
SELECT Name from Employee
)
SELECT * FROM CTE1 INNER JOIN CTE2 ON CTE1.USER_NAME=CTE2.Name
|
The output of above code is as shown below.
Uses of Common Table Expressions(CTE):
· The Common Table Expression (CTE) was introduced earlier in the SQL Server 2005.
The CTE defines about a temporary view, which can be referenced in the same query just as a view .
The CTE’s can be used and compiled in exactly the same ways that simple Subqueries are being used.
It can be used instead of temp table or table variables in the stored procedures in the circumstances.
CTE’s can also recursively refer to the same table using a union or union all, and this works great for searching an adjacency pairs pattern hierarchy.
In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs: Create a recursive query.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
The CTE defines about a temporary view, which can be referenced in the same query just as a view .
The CTE’s can be used and compiled in exactly the same ways that simple Subqueries are being used.
It can be used instead of temp table or table variables in the stored procedures in the circumstances.
CTE’s can also recursively refer to the same table using a union or union all, and this works great for searching an adjacency pairs pattern hierarchy.
In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs: Create a recursive query.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
"If you like my blog or articles, you can appreciate by leaving your comments or Liking my Facebook page Aspdotnet-kishore, following on Google+ Aspdotnet-Kishore, Twitter on AspdotnetKishore, Linked in Aspdotnet-Kishore, stumbling my posts on stumble upon and subscribing on RSSfeed Aspdotnet-Kishore for free updates directly to your Email inbox . Watch my blog for more articles."
No comments:
Post a Comment