DbSchema | SQL Server Common Table Expressions

SQL Server • 02-Jul-2023

by Malik Umair Sajjad

Table of Contents

Introduction
Prerequisites
Common Table Expressions (CTEs)
Usage of CTEs
Advantages and Limitations of CTEs
Restrictions on Using CTEs
Permissions Required for Using CTEs
Types of CTEs
How to Use CTEs in sqlcmd and DbSchema
Conclusion
References

1. Introduction

In this article, we are going to explore Common Table Expressions (CTEs) in SQL Server and how to use them with sqlcmd and DbSchema. CTEs are a new construct introduced in SQL Server that provides a more readable and user-friendly approach to derived tables.

2. Prerequisites

To follow along with this guide, you will need:

A basic understanding of SQL Server
Access to SQL Server Management Studio (SSMS) or an equivalent tool
Familiarity with sqlcmd utility and DbSchema

For installation and establishing connection you can read our article SQL Server-How to create a database?

3. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. CTEs can be thought of as alternatives to derived tables and views, which can increase the readability and maintainability of your SQL code.

4. Usage of CTEs

You define a CTE using the WITH keyword. Once defined, a CTE is not stored as an object in the database memory and disappears after use.

Here is a basic example:

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
  SELECT SalesPersonID, COUNT(SalesOrderID)
  FROM Sales.SalesOrderHeader
  GROUP BY SalesPersonID
)
SELECT SalesPersonID, NumberOfOrders
FROM Sales_CTE;

5. Advantages and Limitations of CTEs

Advantages:

Improved Readability and Maintainability: CTEs make complex queries easier to understand and manage.
Recursive Querying: CTEs can be used to write recursive queries which are not possible with derived tables.
Substitute for a View: When the use of a view is not possible, a CTE can be a viable alternative.

Limitations:

Limited Scope: A CTE is only available to the statement that follows it directly.
No Indexing: CTEs cannot have an index created on them.
Limited Performance: In some cases, using a CTE can result in worse performance compared to temporary tables.

6. Restrictions on Using CTEs

CTEs come with some restrictions:

A CTE must be followed by a single SQL statement that references the CTE.
CTE cannot be used in a VIEW.
CTEs do not support INSERT, UPDATE, DELETE, or CREATE VIEW statements within the CTE definition.

7. Permissions Required for Using CTEs

To create and use a CTE, the user must have the permissions on the underlying database objects that the CTE references.

8. Types of CTEs

There are two types of CTEs:

*CTE Type*	*Description*
`Non-Recursive CTEs`	These are the simplest form of CTEs which are not self-referencing.
`Recursive CTEs`	These CTEs are self-referencing, enabling you to perform hierarchical or recursive queries.

9. How to Use CTEs in sqlcmd and DbSchema

SQLCMD

The sqlcmd utility is a command-line tool that allows you to interact directly with SQL Server. Here are the steps to use CTEs in sqlcmd:

Create SQL Script:

Start by creating an SQL script that contains your CTE. Here’s a simple example of what the SQL file (let’s call it query.sql) could look like:

WITH Employee_CTE (EmployeeID, FirstName, LastName)
AS
(
    SELECT EmployeeID, FirstName, LastName
    FROM HumanResources.Employee
    WHERE EmployeeID < 5
)
SELECT * FROM Employee_CTE;

It creates a temporary result set (named Employee_CTE), which consists of the EmployeeID, FirstName, and LastName of every employee in the Employee table where the EmployeeID is less than 5.

This script will select the employees whose EmployeeID is less than 5 and then return those rows.

Connect to SQL Server via sqlcmd:

Once you have your SQL script prepared, open your command line interface, and connect to your SQL Server instance using sqlcmd. The general command to do this is as follows:

sqlcmd -S <server> -d <database> -U <username> -P <password>

Replace <server>, <database>, <username>, and <password> with your specific server details and login credentials.

Run the SQL Script:

After successfully connecting to your SQL Server instance, execute the query.sql script that contains the CTE:

sqlcmd -S <server> -d <database> -U <username> -P <password> -i query.sql

The output of your CTE will be displayed on the command line.

Sample Database:

For example, let’s say we have the following Employee table:

*EmployeeID*	FirstName	*LastName*
`1`	John	Doe
`2`	Jane	Smith
`3`	James	Johnson
`4`	Patricia	Brown
`5`	Robert	Davis
`6`	Linda	Miller

Results from the Query:

If you run the CTE query the result would look like:

*EmployeeID*	FirstName	LastName
`1`	John	Doe
`2`	Jane	Smith
`3`	James	Johnson
`4`	Patricia	Brown

So the result is a table of employees whose IDs are less than 5, effectively filtering out employees with IDs 5 and 6 from the original Employee table.

DbSchema

DbSchema is a visual database designer and management tool. Here’s how to use CTEs in DbSchema:

Connect to Your Database:

Start DbSchema, create a new project, and connect to your database.

Open SQL Editor:

Click on the ‘SQL Editor’ button to open the SQL editor window. This is where you will write your SQL queries.

Write a CTE Query:

Similar to the sqlcmd example, we’ll use a CTE to retrieve employees whose EmployeeID is less than 5:

WITH Employee_CTE (EmployeeID, FirstName, LastName)
AS
(
    SELECT EmployeeID, FirstName, LastName
    FROM HumanResources.Employee
    WHERE EmployeeID < 5
)
SELECT * FROM Employee_CTE;

Enter the above SQL code into the editor.

Run the Query:

Execute the SQL statement by pressing the Run button or using the F5 key. Your query’s result will be displayed in the Result tab at the bottom of the SQL Editor window.

With these steps, you can use CTEs in both sqlcmd and DbSchema. They provide a more readable and maintainable approach to complex SQL queries.

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

10. Conclusion

Common Table Expressions provide a more readable form of query expression in SQL Server. They can be used in various scenarios where a derived table might seem complicated. In this article, we explored the use, advantages, and limitations of CTEs, and we also learned how to use them in sqlcmd and DbSchema.