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

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams.


Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected.


Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys.


Query Builder alt >

Query Builder

Create SQL Queries using the mouse.


SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries


Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model.


Dark Theme alt >

Dark Theme

Configurable styles & dark theme.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.