Diwali Deal : Flat 20% off + 2 free self-paced courses + $200 Voucher  - SCHEDULE CALL

sddsfsf

What is Common Table Expression in SQL Server?

 

Imagine you are writing a very complicated query involving a lot of tables and joins. Either you can write the query all at once. But that would make debugging difficult if there is some error in the query. Otherwise, you can break the query into small parts, get the data into temporary tables, and combine these temporary tables in a query to get the final output. There are many ways to do it. One way is to use a Common table expression or CTE in SQL server. Over the next few paragraphs, we will learn more about common table expressions, how to use them, and what are its advantages and disadvantages. You can learn about Common Table Expression by enrolling in an online SQL server training course and shape your ever-growing SQL career. 

What is Common Table Expression in SQL?

Common Table Expressions (CTE) were introduced into standard SQL to simplify various classes of SQL Queries for which a derived table was just unsuitable. CTE was introduced in SQL Server 2005. The common table expression (CTE) is a temporary named result set that we can reference within a SELECT, INSERT, UPDATE, or DELETE statement. We can also use a CTE in a CREATE view as part of the view’s SELECT query. In addition, as of SQL Server 2008, we can add a CTE to the new MERGE statement. 

A CTE (Common Table Expression) is a one-time result set that only exists for the duration of the query. It allows us to refer to data within a single SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or MERGE statement's execution scope. It is temporary because its result cannot be stored anywhere and will be lost when a query's execution is completed. It first came with SQL Server 2005 version. A DBA always preferred CTE to use as an alternative to a Sub query/View. They follow the ANSI SQL 99 standard and are SQL-compliant.

Data is an integral part of businesses, and there’s great demand for administrators. Therefore go through SQL DBA career path if you too want to set yourself up for this role.

Difference between CTE and Table variable

CTE

Temporary Table

CTE is like a Derived Table or even like a sub-query. So it does not store on disk

Table variables are like a temporary table. It stores on disk.

You cannot create any index on CTE.

You can create clustered index but  cannot create a non-clustered index

The scope of CTE is within the session.

Scope of table variable is within the batch.

Difference between CTE and Temp Table

CTE

Temp Table

Are unindexed (but can use existing indexes on reference objects)

Are real materialized tables that exist in tempdb

Cannot have constraints

Can have constraints

Persist until the next query is run

Persist for the life of the current connection

Can be recursive

Can be referenced by other queries and sub procedures

Do not have dedicated stats

Have dedicated stats generated by the engine

Do you love working with data? Or want to pursue a career in the Microsoft SQL Server Database domain? But feel stuck with doubts? A comprehensive SQL career path will help you explore all the career options.

Different Types of CTE:

CTE SQL Server is divided  into two broad categories:

  1. Recursive CTE
  2. Non-Recursive CTE

Recursive CTE

Recursive CTEs use repeated procedural loops, aka recursion. The recursive query calls them until the query satisfies the condition. In a recursive CTE, we should provide a where condition to terminate the recursion.

Using a CTE, we will see how to create a simple Recursive query to display the Row Number from 1 to 10.

Firstly we declare the Integer variable as “RowNo” and set the default value as 1, and we have created our first CTE query as an expression name “ROWCTE.” In our CTE, we’ll first display the default row number, and next, we’ll use a Union ALL to increment and display the row number 1 by one until the Row No reaches the incremented value of 10. To view the result, we will use a select query to display our CTE result.

The query is as follows

Declare @RowNo int =1;
;with ROWCTE as  
   (  
      SELECT @RowNo as ROWNO    
UNION ALL  
      SELECT  ROWNO+1  
  FROM  ROWCTE  
  WHERE RowNo < 10
    )  
SELECT * FROM ROWCTE

The output looks like the below

Still, have doubts regarding career benefits, average SQL developer salary, and the top companies hiring SQL developers around the world? Get in touch with our consultant today!

Non-Recursive CTE

Non-Recursive CTEs are simple, where the CTE doesn’t use recursion or repeated processing in a sub-routine. We will create a simple Non-Recursive CTE to display the row number from 1 to 10.

Per the CTE Syntax, each CTE query will start with a “With” followed by the CTE Expression name with a column list.

Here we have been using only one column as ROWNO. Next is the Query part. Here, we write our selected query to be executed for our CTE. After creating our CTE query to run the CTE, use the select statement with the CTE Expression name.

The query looks like below:

with ROWCTE(ROWNO) as  
   (  
     SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM sys.databases 
WHERE database_id <= 10
    )  
SELECT * FROM ROWCTE 

The output is as follows:

Next, we would discuss a special case in CTE i.e. Multiple CTE.

Multiple CTE

Sometimes, we'll need to create multiple CTE queries and join them together to see the results. We may use multiple CTEs concepts in this scenario. We need to use the comma operator to create multiple CTE queries and merge them into a single statement. The CTE name must precede the "," comma operator to distinguish multiple CTE.

Multiple CTEs help us simplify complex queries that are eventually joined together. Each complex piece had its own CTE, which could be referenced and joined outside the WITH clause.

The syntax looks like this below

WITH   
cte_name1 (column_names) AS (query),  
cte_name2 (column_names) AS (query)  
SELECT * FROM cte_name  
UNION ALL  
SELECT * FROM cte_name; 

Let us now check out a real-life example.

In this example, we have defined the two CTE names, customers_in_newyork and customers_in_california. Then the result set of subqueries of these CTEs populates the CTE. Finally, we will use the CTE names in a query that will return all customers in New York and California State.

The query looks like the one below:

WITH   
customers_in_NewYork  
AS (SELECT * FROM Sales.Customer WHERE TerritoryID = 1),  
customers_in_California  
 AS (SELECT * FROM Sales.Customer WHERE TerritoryID = 4)  
SELECT * FROM customers_in_NewYork  
UNION ALL  
SELECT * FROM customers_in_California;

The output of the query is as below.

Why we need Common Table Expression in SQL

  • It is useful when defining a derived table multiple times within a query.
  • It is useful when we need to create an alternative to a view in the database.
  • It is useful when we need to perform the same calculation multiple times on multiple query components simultaneously.
  • It is useful when we need to use ranking functions like ROW_NUMBER(), RANK(), and NTILE().

CTE Syntax in SQL Server

The CTE syntax includes a CTE name, an optional column list, and a statement/query that defines the common table expression (CTE). After defining the CTE, we can use it as a view in a SELECT, INSERT, UPDATE, DELETE, and MERGE query.

The following is the basic syntax of CTE in SQL Server:

WITH cte_name (column_names)   
AS (query)     
SELECT * FROM cte_name;  

In this syntax:

  • We have first specified the CTE name that will be referred to later in a query.
  • The next step is to create a list of comma-separated columns. It ensures that the number of columns in the CTE definition arguments and the number of columns in the query must be the same. If we have not defined the CTE arguments columns, it will use the query columns that define the CTE.
  • After that, we'll use the AS keyword after the expression name and then define a SELECT statement whose result set populates the CTE.
  • Finally, we will use the CTE name in a query such as SELECT, INSERT, UPDATE, DELETE, and MERGE statement.

It should keep in mind while writing the CTE query definition; we cannot use the following clauses:

  1. ORDER BY unless you also use as TOP clause
  2. INTO
  3. OPTION clause with query hints
  4. FOR BROWSE

SQL CTE Examples

The following CTE example prints all the days of the week in a table format.

The query is as follows:

WITH cte_numbers(n, weekday) 
AS (
SELECT 
0, 
DATENAME(DW, 0)
UNION ALL
SELECT    
n + 1, 
DATENAME(DW, n + 1)
FROM  
cte_numbers
WHERE n < 6
)
SELECT 
weekday
FROM 

cte_numbers;

The output is as below:

Advantages of CTE

Some of its advantages are given below:

  • SQL CTE facilitates code maintenance easier.
  • Sql CTE increases the readability of the code.
  • It increases the performance of the query.
  • SQL CTE makes it possible to implement recursive queries easily.

Disadvantages of CTE

  • Sql CTE members cannot use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.
  • The CTE can only be referenced once by the Recursive member.
  • We cannot use the table variables and CTEs as parameters in stored procedures.
  • We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.
  • Since it's just a shortcut for a query or subquery, it can't be reused in another query.
  • The number of columns in the CTE arguments and the number of columns in the query must be the same.

cta14 icon

SQL Testing Training

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Conclusion

Over the last few paragraphs, we have learned about the different aspects of CTE, how it works, when to use it and its advantages and disadvantages, and how to become a SQL database administrator. This document can be a head start on learning more about CTE. We hope this write-up will give the readers enough thought to do a more detailed study about the topic.

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

-1 day 05 Nov 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

6 days 12 Nov 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

5 days 11 Nov 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

-1 day 05 Nov 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

9 days 15 Nov 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

2 days 08 Nov 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

1 day 07 Nov 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

2 days 08 Nov 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

3 days 09 Nov 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

2 days 08 Nov 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

9 days 15 Nov 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

2 days 08 Nov 2024