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
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply