What is the advantage of common table expression in sql server

we write CTE sql like below one

WITH yourCTE AS 
(开发者_如何转开发
 SELECT .... FROM :... WHERE.....
) SELECT * FROM yourCTE

what would be advantage to put sql in with block. i think that if we put complicated sql in with block then we just can write sql like SELECT * FROM yourCTE. as if i am accessing view. what is added advantage of using CTE in terms of performance. please discuss. thanks

There are a number of cases where a CTE can be really useful:

• recursive queries, like walking up a hierarchy tree - that's extremely tricky and cumbersome without a CTE (see here for a sample of a recursive CTE)

• anytime you want to use one of the ranking functions like ROW_NUMBER(), RANK(), NTILE() and so forth (see here for info on ranking functions)

• in general any case where you need to select a few rows/columns first, based on some criteria, and then do something with these, e.g. update a table, delete duplicates etc.

One case I often use a CTE for is deleting all but the most recent row of a given set of data, e.g. if you have customers and an 1:n relationship to their orders, and you want to delete all but the most recent order (based on an OrderDate), for each customer, it gets quite hairy to do this in SQL without a CTE.

With a CTE and the ranking functions, it's a breeze:

;WITH CustomerOrders AS
(
    SELECT  
       c.CustomerID, o.OrderID,
       ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC) AS 'RowN'
    FROM
       dbo.Customer c
    INNER JOIN
       dbo.Orders o ON o.CustomerID = c.CustomerID
)
DELETE FROM 
    dbo.Orders
FROM 
    CustomerOrders co
WHERE 
  dbo.Orders.OrderID = co.OrderID
  AND co.RowN > 1

With this, you create an "inline view" that partitions by CustomerID (e.g. each customer gets rownumbers starting at 1), order by OrderDate DESC (newest order first). For each customer, the newest, most recent order has RowN = 1, so you can easily just delete all other rows and you've done what you wanted to do - piece of cake with a CTE - messy code without it....

This MSDN article describes it the best. The bottom line is that, if you are already selecting the data from a view, you don't have to wrap it in a CTE and THEN select from the CTE. I don't think there's much difference (performance wise) between a CTE and a view. At least not in my experience (and I've been working with some complex database structures housing tons of records recently). A CTE is, however, ideal for recursive selects.

Another thing, though, is that a CTE can be beneficial if you'd be selecting the same subset of joined data multiple times in your query/ies and DON'T have a view defined for it. I think it's overkill if you'll be joining data just for a single query and then wrapping it up in a CTE. The query path will still get cached even though you're not using a CTE...

1. Making recursive query.
2. Hold a query output virtually in a temporary area named as given while definition.
3. No need to save Meta data.
4. Useful when there is need to do more operation on some query output.
5. Query output retain while till then query is running
6. Best use of holding temporary data for further processing.
7. Allow more grouping option than a single query.
8. Allow to get scalar data from a complicated query

Good evening friends..Today we are going to learn about Common table expression that is a new feature which was introduced in SQL server 2005 and available in later versions as well.

Common table Expression :- Common table expression can be defined as a temporary result set or in other words its a substitute of views in SQL Server. Common table expression is only valid in the batch of statement where it was defined and cannot be used in other sessions.

Syntax of declaring CTE(Common table expression) :-

with [Name of CTE]
as
(
Body of common table expression
)

Lets take an example :-

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

I have created two tables employee and Dept and inserted 5 rows in each table. Now I would like to join these tables and create a temporary result set to use it further.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

Lets take each line of the statement one by one and understand.

To define CTE we write "with" clause, then we give a name to the table expression, here I have given name as "CTE_Example"

Then we write "As" and enclose our code in two brackets (---), we can join multiple tables in the enclosed brackets.

In the last line, I have used "Select * from CTE_Example" , we are referring the Common table expression in the last line of code, So we can say that Its like a view, where we are defining and using the view in a single batch and CTE is not stored in the database as a permanent object. But it behaves like a view. we can perform delete and update statement on CTE and that will have direct impact on the referenced table those are being used in CTE. Lets take an example to understand this fact.

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

In the above statement we are deleting a row from CTE_Example and it will delete the data from the referenced table "DEPT" that is being used in the CTE.

I hope this article will be helpful to you and you will be able to use CTE whenever you find it suitable.