Sample Database for Common Table Expressions

As I have presented my Common Table Expressions presentation many times, and as part of writing the Common Table Expressions Book I have created some sample database scripts to use.

Once you load up the script into SSMS and refresh the databases tree item, you will see a new database called cte_demo that includes 2 tables.

dbo.Departments

The first table is called dbo.Departments and it contains 17 rows with a number of store departments with the top level departments being indicated by a parent of NULL, and subdepartments having actual parent ID’s.

dbo.Royalty

The dbo.Royalty table contains several generations of the British Royal Family family tree. We will use these for some interesting hierarchical queries.

Stored Procedures

This database also includes 2 stored procedures to rebuild the Departments table as we will be changing data in that table as we test different scripts.

Downloadable Scripts

Download one of the following scripts and run it in SQL Server Management Studio to set up the CTE_DEMO database for use in several samples.

This script is intended to be run on a test or development server and is not intended to be run on a production system.

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com