What is CTE table in SQL?

Common Table Expressions(CTE) are used to hold some sort of result sets temporarily in DB..
Complex SQL statements can be made easier to understand and maintainable in the form of CTE or Common Table expressions.

When dealing with sub-queries, it is often required that you need to select a part of the data from a sub query or even join data from a query with some other tables. In that case, either you have an option to name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and more complex and your query would look unmaintainable at any time. CTE allows you to define the subquery at once, name it using an alias and later call the same data using the alias just like what you do with a normal table.


-- Normal sub query

SELECT * FROM  (
        SELECT A.Address, E.Name, E.Age From Address A
        Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

Equivalent CTE is:

With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME

When to Use Common Table Expressions
Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. Even when a CTE is not necessarily needed, it can improve readability. In Using Common Table Expressions, Microsoft offers the following four advantages of CTEs:

1) Create a recursive query.
2) 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.
3) Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
4) Reference the resulting table multiple times in the same statement.
5) 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.

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

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

1) recursive queries, like walking up a hierarchy tree - that's extremely tricky and cumbersome without a CTE

2) anytime you want to use one of the ranking functions like ROW_NUMBER(), RANK(), NTILE() and so forth

3) 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.
4) to display Parent-Child recursive hierarchy levels we obviously use CTEs
5) We can avoid CURSORs in some scenarios; CURSOR will cause performance issues... So we can avoid those with CTEs

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....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Refer this link too for further information
http://www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

CTEs in SQL Server:
Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs— recursive and nonrecursive


For better understanding of CTEs refer this link
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

Click on "Mark as Answer" if the above posts help you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

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.
also you can refer the http://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx here

hope this helps

Jpatil

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator

CTE is Common Table expression.


Please see the below video for more information on CTE with examples.

http://www.dotnetfunda.com/videos/show/228/cte-in-sql-server

Pkanwar, if this helps please login to Mark As Answer. | Alert Moderator