DbSchema Database Designer

DbSchema | SQL Server Common Table Expressions

SQL Server alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Common Table Expressions (CTEs)
  4. Usage of CTEs
  5. Advantages and Limitations of CTEs
  6. Restrictions on Using CTEs
  7. Permissions Required for Using CTEs
  8. Types of CTEs
  9. How to Use CTEs in sqlcmd and DbSchema
  10. Conclusion
  11. 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:

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

Limitations:

  1. Limited Scope: A CTE is only available to the statement that follows it directly.
  2. No Indexing: CTEs cannot have an index created on them.
  3. 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:

  1. A CTE must be followed by a single SQL statement that references the CTE.
  2. CTE cannot be used in a VIEW.
  3. 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:

  1. 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.

  1. 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.

  1. 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:

  1. Connect to Your Database:

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

  1. Open SQL Editor:

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

  1. 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.

  1. 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.

11. References

  1. Common Table Expressions
  2. DbSchema Documentation
  3. SQLCMD Utility
DbSchema Features

DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.