There are
many queries and logic in the minds which concerns only one thing that how to
make code manageable and easy readable which reduces the building blocks
complexities. But to accomplish it CTE is the best option by which you
can easily read as well as manage SQL Server database query. CTE is
defined as Common Table Expression which is a derived table to define and
stored for the duration of query. It is very useful for recursive queries, same
query contain multiple table reference as well as in User Defined Functions,
Stored Procedures, Triggers and Views.
Well
Common Expression Table is supposed to be a result set temporary which can be
put in a scope of SELECT, INSERT, UPDATE, DELETE, or
CREATE VIEW statement.
Here the
below scripts manipulate the employees service years in the database name
Exotic and result set returns value in descending order.
with Emp_CTE (LoginID , JobTitle , OrgLevel , Years_Employeed )
as
( SELECT
LoginID, JobTitle, OrganizationLevel, datediff(year , HireDate , getdate())
FROM [AdventureWorks2008R2].[HumanResources] .[Employee]
)
select * from Emp_CTE
order by Years_Employeed desc;
as
( SELECT
LoginID, JobTitle, OrganizationLevel, datediff(year , HireDate , getdate())
FROM [AdventureWorks2008R2].[HumanResources] .[Employee]
)
select * from Emp_CTE
order by Years_Employeed desc;
It is
very useful for a numerous work like:
- Recursive query can also be created
- Doesn’t require to store metadata definition because it provide substitute for a view when the general use of a view is not required.
- Enable grouping by a column which 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.
The
Structure of CTE depends on expression name, an optional column list, and a
query defining the CTE. On defining CTE, it can be referenced like a table or
view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be
used in a CREATE VIEW statement as part of its defining SELECT statement.
Systax forcretaing CTE is:
WITH
expression_name[ (column_name [,...xx] ) ]
AS
(
CTE_query_definition )
The list
of column names is optional only if distinct names for all resulting columns
are supplied in the query definition.
The
statement to run the CTE is:
SELECT
<column_list>
FROM
expression_name;
Example
Here is example
which shows the components of the CTE structure:
1.Expression
name,
2.Column
list,
3.Query.
The CTE
expression Cmpy_CTE has three columns (PersonID, OrderID, and Orderinfo) and is
defined as the total number of sales orders per year for each salesperson.
Transact-SQL
USE
AdventureWorks2008R2;
GO
-- Define the CTE
expression name and column list.
WITH Cmpy_CTE
(PersonID,OrderID, Year)
AS
-- Define the CTE
query.
(
SELECT PersonID, OrderID, YEAR(OrderDate)
AS CmpyYear
FROM Cmpy.OrderHeader
WHERE PersonID IS NOT NULL
)
-- Define the outer query
referencing the CTE name.
SELECT PersonID,
COUNT(OrderID) AS TotalCmpy, CmpyYear
FROM Cmpy_CTE
GROUP BY CmpyYear, PersonID
ORDER BY PersonID, Year;
GO
Here is a
partial result set:
| PersonID | TotalCmpy | CmpyYear | |||||||
| 274 | 4 | 2011 | |||||||
| 274 | 20 | 2012 | |||||||
| 274 | 14 | 2013 | |||||||
| 274 | 10 | 2014 | |||||||
| 275 | 56 | 2015 | |||||||
| 275 | 139 | 2016 | |||||||
| 275 | 169 | 2017 |
Another Sample of Query:
