Fundamentals of Common Table Expresions (CTE)

Introduction

A common table expression, or CTE, is a temporary selection or other query operation of records from one or more tables. You use it to get an idea of what the query operation would produce if performed on a table. You can generate a CTE to create, select, merge, or delete records.

There are two types of common table expressions: recursive and non-recursive.

Practical Learning: Introducing Common Table Expressions

1. Start Microsoft SQL Server and connect
2. On the Standard toolbar, click the New Query button
3. To create a new database, type the following:
   

   USE master;
   GO
   
   CREATE DATABASE HotelManagement1
   GO
   
   USE HotelManagement1;
   GO
   CREATE SCHEMA Logistics;
   GO
   
   CREATE TABLE Logistics.SleepingRooms (
       RoomNumber nchar(10) not null,
       RoomType nvarchar(20) default N'Bedroom',
       BedType nvarchar(40) default N'Queen',
       Rate money default 75.85,
       Available bit default 0
   );
   GO
   
   CREATE TABLE Logistics.ConferenceRooms (
       RoomNumber nchar(10) not null,
       RoomType nvarchar(20) default N'Conference',
       BedType nvarchar(40),
       Rate money default 75.85,
       Available bit default 0
   );
   GO
   
   INSERT INTO Logistics.SleepingRooms(RoomNumber, BedType, Rate, Available)
   VALUES(N'104', default, 80.25, 0),
         (N'105', N'King', 95.50, 1),
         (N'106', default, 65.95, 1),
         (N'107', N'Queen', 65.95, 1),
         (N'108', N'King', 92.50, 1),
         (N'109', default, 68.95, 0),
         (N'110', default, 74.95, 1);
   GO
   
   INSERT INTO Logistics.ConferenceRooms(RoomNumber, Rate, Available)
   VALUES(N'C-120', 525.00, 1);
   GO
   
   INSERT INTO Logistics.SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)
   VALUES(N'116', N'Studio', N'King', 112.95, 0);
   GO
   
   INSERT INTO Logistics.ConferenceRooms(RoomNumber, Rate, Available)
   VALUES(N'C-122', 450.00, 1);
   GO
   
   INSERT INTO Logistics.SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)
   VALUES(N'202', N'Studio', N'King', 105.95, 1),
         (N'203', N'Studio', default, 102.50, 1);
   GO
   
   INSERT INTO Logistics.SleepingRooms(RoomNumber, BedType, Rate, Available)
   VALUES(N'204', N'Double', 115.95, 1),
         (N'205', default, 82.95, 0),
         (N'206', N'King', 98.50, 1)
   GO
   
   INSERT INTO Logistics.ConferenceRooms(RoomNumber, Rate, Available)
   VALUES(N'C-302', 650.00, 1);
   GO

4. Press F5 to execute

Creating a Common Table Expression

The formula to create a common table expression (CTE) is:

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

You start with the WITH keyword followed by a name for the temporary set. The name must be different from any table that will be used in the CTE's expression. Later, we will see the parameters you can add after the name. After the name, type AS followed by parentheses. In the parentheses, create a simple or composite SELECT expression.

After the code that defines the CTE, that is, after the AS(CTE_query_definition) expression, create a SELECT statement that will produce the results.

Practical Learning: Creating a Common Table Expression

1. Delete everything in the Query Editor and replace it with:
   

   USE HotelManagement1;
   GO
   
   WITH BedRooms AS
   (
       SELECT * FROM Logistics.SleepingRooms
   )
   
   SELECT * FROM BedRooms;
   GO

2. Press F5 to execute
    
   
3. To see the results of both tables, change the statement as follows:
   

   USE HotelManagement1;
   GO
   
   WITH BedRooms AS
   (
       SELECT * FROM Logistics.SleepingRooms
   )
   
   SELECT * FROM BedRooms;
   SELECT * FROM Logistics.SleepingRooms;
   GO

4. Press F5 to execute
5. To see a list of only available rooms from the CTE, change the statement as follows:
   

   USE HotelManagement1;
   GO
   
   WITH BedRooms AS
   (
       SELECT * FROM Logistics.SleepingRooms WHERE Available = 1
   )
   SELECT * FROM BedRooms
   GO

6. Press F5 to see the result
    
   

A CTE With Parameters

To make sure you can externally control the results of a CTE, you can pass a type of parameter to it. To do this, after the name of the CTE and before the AS operator, add the parentheses and pass one or more parameters, each represented by a name. The names of parameters must be the exact same names of columns of the table(s) from which the CTE's statement will be based. The number of columns must be the same as the number of columns that will be involved in the final SELECT statement.

In the body of the CTE, use the parameter(s) as you wish. For example, you can involve the parameter(s) in a condition in the CTE.

Practical Learning: Passing Parameters to a CTE

1. To pass some parameters to the CTE, delete everything in the Query Editor and replace it with:
   

   USE HotelManagement1;
   GO
   
   WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
   AS
   (
   	SELECT RoomNumber, RoomType, BedType, Rate, Available
   	FROM Logistics.SleepingRooms
   	WHERE BedType = N'Queen'
   )
   SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
   GO

2. Press F5 to execute
    
   
3. To see a list of only available rooms from the CTE, change the statement as follows:
   

   USE HotelManagement1;
   GO
   
   WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
   AS
   (
       SELECT RoomNumber, RoomType, BedType, Rate, Available
       FROM Logistics.SleepingRooms
       WHERE BedType = N'Queen'
   )
   SELECT RoomNumber, RoomType, Rate, Available
   FROM BedRooms
   WHERE Available = 1;
   GO

4. Press F5 to see the result
    
   

Topics on Common Table Expresions

Using Conditions

In all of our SELECT expressions so far, considered all records of the intended table. In some cases, you can set a condition that would select only some records. You can use the condition in one or both SELECT statements.

Recursive Common Table Expressions

A recursive common table expression is a CTE that can contain more than one SELECT statement. In the body of the CTE, you can create as many SELECT statements as you want but those statements must be joined. To join them, you can use a UNION, UNION ALL, or MERGE operator.

Practical Learning: Creating a Recursive CTE

1. Change the CTE's code as follows:
   

   USE HotelManagement1;
   GO
   
   WITH HotelRooms
   AS
   (
       SELECT * FROM Logistics.SleepingRooms
       UNION
       SELECT * FROM Logistics.ConferenceRooms
   )
   SELECT * FROM HotelRooms;
   GO

2. Press F5 to execute
3. To see a list of all available rooms, change the statement as follows:
   

   USE HotelManagement1;
   GO
   
   WITH HotelRooms
   AS
   (
       SELECT * FROM Logistics.SleepingRooms
       UNION
       SELECT * FROM Logistics.ConferenceRooms
   )
   SELECT RoomNumber, RoomType, BedType, Rate
   FROM HotelRooms
   WHERE Available = 1;
   GO

4. Press F5 to execute
    
   

Non-Recursive Common Table Expressions

A non-recursive common table expression is a CTE that can be followed by only one SELECT, INSERT, UPDATE, or DELETE statement that involves a column from inside the CTE's body.

Topics on Common Table Expresions

In our recursive CTE expressions so far, considered all records of the intended table. In some cases, you can set a condition that would select only some records before merging them. You can use the condition in one or both SELECT statements.

Fundamentals of Table-Valued Functions

Introduction

All of the functions we have created so far returned 0 or a single value that was one of the data types supported by Transact-SQL. A table-valued function is a function that returns a table. A multi-statement table-valued function is a function that creates a table, optionally fills it up with the desired records, and then returns the table.

Creating a Table-Valued Function

To create a function that returns a table, you have various options. In the Object Explorer, expand the database that will own the function, expand the Programmability node, and expand Functions. Right-click Table-Valued Functions and click New Multi-Statement Table-Valued Function...

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

As an alternative, open a new Query Editor. In the Templates explorer, expand Function, drag Create Multi-Statement Function (New Menu):

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <data_type_for_param1, , int>, 
	<@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
	-- Add the column definitions for the TABLE variable here
	<Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
	<Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	
	RETURN 
END
GO

The primary formula to create a multi-statement table-valued function is:

CREATE FUNCTION FunctionName()
RETURNS @TableName TABLE (Table's Columns)
AS
BEGIN
    RETURN;
END;

You start with the CREATE FUNCTION expression followed by a name for the function and its parentheses. As seen already, after the parentheses, type the RETURNS keyword. Since you are actually creating a table, follow the RETURNS keyword with the @ operator, followed by the desired name of the table, followed by the TABLE keyword, and its parentheses.

Create the columns of the table in the parentheses. Each column is created using the formula:

ColumnName DataType Options

Each column follows the necessary options: name, data type, optional primary key, optional nullity, optional unique, optional check, and/or optional expression.

After the parentheses that contain the columns, type AS, BEGIN, RETURN and END, preferably on different lines for better readability. Here is an example that creates a function that internally creates a table:

USE Exercise1;
GO

CREATE FUNCTION GetStates()
RETURNS @States TABLE
(
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    RETURN;
END;
GO

Data Entry on a Table-Valued Function

When creating the function, if you want to add records to the table, before the RETURN keyword, use the INSERT or INSERT INTO statement(s) as necessary. Here is an example:

USE Exercise1;
GO
DROP FUNCTION GetStates;
GO

CREATE FUNCTION GetStates()
RETURNS @States TABLE
(
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES(N'DC', N'District of Columbia'),
			      (N'MD', N'Maryland'),
			      (N'PA', N'Pennsylvania'),
			      (N'VA', N'Virginia'),
			      (N'WV', N'West Virginia');
    RETURN;
END;
GO

Calling a Multi-Statement Table-Valued Function

After creating a multi-statement table-valued function, it is represented in the Table-Valued Functions node of the Object Explorer. To call the function, use a SELECT statement. To indicate that you want all records of the table, you can use the * operator. Here is an example:

USE Exercise1;
GO

SELECT * FROM GetStates();
GO 

This would produce:

A multi-statement table-valued function is a type of table. It simply makes it easy to access the records of a table with one call. Because it is a table-type, you can access one, some, or all of its fields. To specify the fields you want to get, create a SELECT statement that holds the list of columns. Here is an example:

USE Exercise1;
GO
SELECT LongName FROM GetStates();
GO

This would produce:

Of course you can also set conditions that the SELECT statement must follow. In the same way, you can include any of the statements we have already seen, or that we will see in other lessons, in the creation or call of an inline table-valued function.

Passing Arguments to a Multi-Statement Table-Valued Function

In its formula, we saw that a table-valued function had parentheses. In the parentheses, you can specify one or more arguments. Each argument is in the form of @, followed by a name, and a data type. Here is an example:

USE Exercise1;
GO
DROP FUNCTION GetStates;
GO

CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40))
RETURNS @States TABLE
(
    RegionName nvarchar(40),
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES(N'New England', N'CT', N'Connecticut'),
			      (N'New England', N'ME', N'Maine'),
			      (N'South Atlantic', N'DC', N'District of Columbia'),
			      (N'South Atlantic', N'MD', N'Maryland'),
			      (N'South Atlantic', N'PA', N'Pennsylvania'),
			      (N'South Atlantic', N'VA', N'Virginia'),
			      (N'South Atlantic', N'WV', N'West Virginia');
    RETURN;
END;
GO

When calling the function, you must provide a value for the argument(s). Here is an example: 

USE Exercise1;
GO

DECLARE @Region nvarchar(40);
SET @Region = N'South Atlantic';
SELECT ShortName, LongName, RegionName FROM GetStates(@Region)
WHERE RegionName = @Region;
GO

The argument of the function is created using any rule you know about parameters. For example, it can be created with a default value. Here is an example:

USE Exercise1;
GO
DROP FUNCTION GetStates;
GO

CREATE FUNCTION GetStates(@IdentifyRegion nvarchar(40) = N'All')
RETURNS @States TABLE
(
    RegionName nvarchar(40),
    ShortName nchar(2),
    LongName nvarchar(50)
)
AS
BEGIN
    INSERT INTO @States VALUES
	(N'New England', N'CT', N'Connecticut'),
	(N'New England', N'ME', N'Maine'),
	(N'New England', N'MA', N'Massachusetts'), 
	(N'New England', N'NH', N'New Hampshire'),
	(N'New England', N'RI', N'Rhode Island'), 
	(N'New England', N'VT', N'Vermont'),
	(N'Mid-Atlantic', N'NJ', N'New Jersey'), 
	(N'Mid-Atlantic', N'NY', N'New York'),
	(N'Mid-Atlantic', N'PA', N'Pennsylvania'), 
	(N'South Atlantic', N'DE', N'Delaware'),
	(N'South Atlantic', N'DC', N'District of Columbia'),
	(N'South Atlantic', N'FL', N'Florida'),	
	(N'South Atlantic', N'GA', N'Georgia'),
	(N'South Atlantic', N'MD', N'Maryland'),
	(N'South Atlantic', N'PA', N'Pennsylvania'), 
	(N'South Atlantic', N'VA', N'Virginia'),
	(N'South Atlantic', N'WV', N'West Virginia'),
	(N'All', N'AL', N'Alabama'), (N'All', N'AK', N'Alaska'), 
	(N'All', N'AZ', N'Arizona'),
	(N'All', N'AR', N'Arkansas'), (N'All', N'CA', N'California'),
	(N'All', N'CO', N'Colorado'), (N'All', N'CT', N'Connecticut'), 
	(N'All', N'DE', N'Delaware'), 
	(N'All', N'DC', N'District of Columbia'),
	(N'All', N'FL', N'Florida'), (N'All', N'GA', N'Georgia'), 
	(N'All', N'HI', N'Hawaii'),
	(N'All', N'ID', N'Idaho'), (N'All', N'IL', N'Illinois'), 
	(N'All', N'IN', N'Indiana'),
	(N'All', N'IA', N'Iowa'), (N'All', N'KS', N'Kansas'), 
	(N'All', N'KY', N'Kentucky'),
	(N'All', N'LA', N'Louisiana'), (N'All', N'ME', N'Maine'), 
	(N'All', N'MD', N'Maryland'),
	(N'All', N'MA', N'Massachusetts'), (N'All', N'MI', N'Michigan'),
	(N'All', N'MN', N'Minnesota'), (N'All', N'MS', N'Mississippi'),
	(N'All', N'MO', N'Missouri'), (N'All', N'MT', N'Montana'), 
	(N'All', N'NE', N'Nebraska'),
	(N'All', N'NV', N'Nevada'), (N'All', N'NH', N'New Hampshire'),
	(N'All', N'NJ', N'New Jersey'),	(N'All', N'NM', N'New Mexico'),
	(N'All', N'NY', N'New York'), (N'All', N'NC', N'North Carolina'),
	(N'All', N'ND', N'North Dakota'), (N'All', N'OH', N'Ohio'),
	(N'All', N'OK', N'Oklahoma'), (N'All', N'OR', N'Oregon'),
	(N'All', N'PA', N'Pennsylvania'), 
	(N'All', N'RI', N'Rhode Island'),
	(N'All', N'SC', N'South Carolina'), 
	(N'All', N'SD', N'South Dakota'),
	(N'All', N'TN', N'Tennessee'), (N'All', N'TX', N'Texas'), 
	(N'All', N'UT', N'Utah'),
	(N'All', N'VT', N'Vermont'), (N'All', N'VA', N'Virginia'),
	(N'All', N'WA', N'Washington'),	
	(N'All', N'WV', N'West Virginia'),
	(N'All', N'WI', N'Wisconsin'), (N'All', N'WY', N'Wyoming');
    RETURN;
END;
GO

When calling the function, you can omit the value of the argument and type DEFAULT in its place holder:

USE Exercise1;
GO

SELECT RegionName, ShortName, LongName FROM GetStates(DEFAULT);
GO

Otherwise, you can call the function and pass a value of your choice.

Table-Valued Functions and Conditions

When calling a multi-statement table-valued function, you can use a condition to restrict the list of records it produces. For example, you can use a WHERE condition to control the result. Here is an example:

USE Exercise1;
GO

DECLARE @Region nvarchar(40);
SET @Region = N'South Atlantic';
SELECT LongName, ShortName FROM GetStates(@Region)
WHERE RegionName = @Region;
GO

This would produce:

In the same way, you can use CASE, LIKE, BETWEEN, etc to refine the result. 

Inline Table-Valued Functions

Introduction

Consider the following Employees table and its records:

USE Exercise1;
GO
DROP TABLE HumanResources.Employees;
GO

CREATE TABLE HumanResources.Employees
(
    EmployeeNumber nvarchar(10),
    EmployeeName nvarchar(50),
    DateHired date,
    HourlySalary money
);
GO

INSERT INTO HumanResources.Employees
VALUES(N'593705', N'Frank Somah', N'20061004', 26.15),
      (N'720947', N'Paul Handsome', N'20000802', 36.05)
GO
INSERT INTO HumanResources.Employees(EmployeeNumber, EmployeeName, HourlySalary)
VALUES(N'247903', N'Gina Palau', 18.85);
GO
INSERT INTO HumanResources.Employees
VALUES(N'595002', N'John Meah', N'20000212', 32.25);
GO
INSERT INTO HumanResources.Employees(EmployeeName, EmployeeNumber, DateHired)
VALUES(N'Clarice Simms', N'971403', N'20011112');
GO
INSERT INTO HumanResources.Employees
VALUES(N'928375', N'Chuck Stansil', N'20080628', 20.18),
      (N'792764', N'Orlando Perez', N'20000616', 12.95);
GO

When you want to retrieve the records held by that table, you can write a SELECT statement made of too many sections. Imagine you plan to get the records many times, over and over. Instead of creating a new SELECT statement every time, you can create a function that gives you access to the records.

An inline table-valued function is a function that produces the records from a function using a SELECT statement. As opposed to an inline table-valued function, you cannot (directly) include a SELECT statement in a multi-statement table-valued function. The solution is to create the function as inline.

Creating an Inline Function

To create an inline table-valued function, you have various options. To have code generated for you:

• In the Object Explorer, expand the database that will own the function. Expand Programmability. Expand Functions. Right-click Table-Valued Functions and click New Inline Table-Valued Function...
  

  -- ================================================
  -- Template generated from Template Explorer using:
  -- Create Inline Function (New Menu).SQL
  --
  -- Use the Specify Values for Template Parameters 
  -- command (Ctrl-Shift-M) to fill in the parameter 
  -- values below.
  --
  -- This block of comments will not be included in
  -- the definition of the function.
  -- ================================================
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:		<Author,,Name>
  -- Create date: <Create Date,,>
  -- Description:	<Description,,>
  -- =============================================
  CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
  (	
  	-- Add the parameters for the function here
  	<@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
  	<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
  )
  RETURNS TABLE 
  AS
  RETURN 
  (
  	-- Add the SELECT statement with parameter references here
  	SELECT 0
  )
  GO

• In the Template Explorer, expand the Function node. Right-click Create Inline Function (New Menu) and click either Open or Edit
• Open an empty Query Editor. In the Template Explorer, expand the Function node. Drag Create Inline Function (New Menu) and drop it in the Query Editor

Once the skeleton code has been generated for you, you can edit and customize it.

The basic formula to create an inline function is:

CREATE FUNCTION [SchemaName.]FunctionName(Argument(s))
RETURNS TABLE 
AS
RETURN
    SELECT 0

You start with the CREATE FUNCTION expression followed by a name for the function and its arguments. If the function takes one or more arguments, enter it(them) in the parentheses. If there is no argument, leave the parentheses empty,

The closing parenthesis is followed by the RETURNS TABLE expression. The TABLE keyword indicates that the function will return a list of columns and their records. This is followed by the AS and the RETURN keywords.

After the RETURN keyword, if necessary, declare some variable(s) and do what you want in the body of the function. Before the end of the function, you must create a SELECT statement that returns a table-type.

Here is an example:

USE Exercise1;
GO

CREATE FUNCTION HumanResources.GetEmployees()
RETURNS TABLE 
AS
RETURN 
    SELECT * FROM HumanResources.Employees;
GO

Calling an Inline Table-Valued Function

After creating an inline table-valued function, you can access its records. To call such a function, you use a SELECT statement. For example, to indicate that you want all records of the table, you can use the * operator. Here is an example:

USE Exercise1;
GO

SELECT * FROM HumanResources.GetEmployees();
GO 

This would produce:

Because an inline table-valued function is a type of table, you can access one, some, or all of its fields using a SELECT statement. Here is an example:

SELECT EmployeeName,
       HourlySalary 
FROM HumanResources.GetEmployees();

This would produce:

Of course, you can also set a condition that the SELECT statement must follow. Here is an example:

SELECT EmployeeNumber,
       EmployeeName
FROM   HumanResources.GetEmployees()
WHERE  HourlySalary >= 20.00;

This would produce:

In the same way, you can include any of the statements we have already seen, or that we will see in other lessons, in the creation or call of an inline table-valued function.

Practical Learning: Closing Microsoft SQL Server

1. Close the Query Editor
2. When asked whether you want to save, click No
3. Close Microsoft SQL Server

Previous	Copyright © 2008-2022, FunctionX, Inc.	Next