Common Table Expressions

If you think only .NET languages are the only thing that is getting evolved day by day while SQL Syntaxes remains constant, you might have to think it again. SQL Server is gradually coming with evolution day by day with some alternative syntaxes of writing SQL Statements to make more complex SQL statements easier to understand and maintainable in form of CTE or Common Table Expressions. In this post I will try to cover some of the benefits that we get when working with CTE.

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 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. CTE is standard ansi sql standard.

Say for instance you have a query like this :

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

The query looks really a mess. Even if I need to write something that wraps around the entire query, it would gradually become unreadable. CTE allows you to generate Tables beforehand and use it later when we actually bind the data into the output.

Rewriting the query using CTE expressions would look like :

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

Yes as you can see, the second query is much more readable using CTE. You can specify as many query expressions as you want and the final query which will output the data to the external environment will eventually gets reference to all of them. For instance :

With T1(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
),
T2(Name, Desig)
AS
(
SELECT NAME, DESIG FROM Designation)
SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
WHERE T1.Age > 50 AND T1.Name = T2.Name
ORDER BY T1.NAME

So the queries are separated using commas. So basically you can pass as many query as you want and these queries will act as a subqueries, getting you the data and name it as a temporary table in the query.
According to the syntax, the CTE starts with a With clause. You can specify the column names in braces, but it is not mandatory.

Lets create one cool CTE for you finally :

WITH ShowMessage(STATEMENT, LENGTH)
AS
(
SELECT STATEMENT = CAST(‘I Love ‘ AS VARCHAR(150)), LEN(‘I Love ‘)
UNION ALL
SELECT
CAST(STATEMENT + ‘DAILY DOTNET TIPS! ‘ AS VARCHAR(150))
, LEN(STATEMENT) FROM ShowMessage
WHERE LENGTH < 100 ) SELECT STATEMENT, LENGTH FROM ShowMessage [/code] So this will produce something like this :
Please Note: Always consider starting your CTE with a semicolon when you have more than one query in a batch to ensure the with statement is not considered as a part of the previous query.