Reduce Complexity in Separate Building Blocks Using SQL CTE

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;

It is very useful for a numerous work like: 

1. Recursive query can also be created
2. Doesn’t require to store metadata definition because it provide substitute for a view when the general use of a view is not required.
3. Enable grouping by a column which 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.

Structure of a CTE

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: