Introducing Common Table Expressions in SQL Server 2005
(Page 1 of 5 )
This article mainly focuses on the new concept of "Common Table Expression" introduced in Microsoft SQL Server 2005. I shall emphasize simple code. I will use different approaches not seen in any of the previous versions of SQL Server, together with several examples covering a variety of scenarios. If you're a beginner, this article will help get you off to a good start.
I am assuming that the readers of this article will have some knowledge of RDBMS along with some exposure to either SQL Server 2000 or SQL Server 2005. I suggest readers take a look at my previous articles on Microsoft SQL Server 2005 concepts.
Introduction to common table expression
A common table expression is an expression that returns a temporary named result set from a simple or complex query, defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can work with both DDL and DML statements.
A common table expression looks very similar to a temporary table or view. The only difference between a CTE and a view is that views are created as database objects, whereas CTEs are not created as objects in the database. A CTE is available only for a single statement, and in general, every CTE can also be a part of a view.
There are mainly two types of CTEs: non-recursive CTEs, and recursive CTEs. In this article, I shall concentrate on non-recursive common table expressions, and in another upcoming article, we will continue with recursive common table expressions.
A CTE which doesn't include references to itself within its own body is called a non-recursive CTE. Some clauses such as COMPUTE, COMPUTE BY, ORDER BY (ORDER BY can be used if we specify the TOP clause), INTO, OPTION clause with query hints, FOR XML, FOR BROWSE and so on cannot be used within a CTE definition.
Before I start to give you examples of non-recursive common table expressions, please take a look at Microsoft's sample databases. Microsoft changed its strategy a bit towards sample databases. Instead of simply providing OLTP based databases (like Northwind), it provides the samples for both OLTP and OLAP. To work with the latest sample databases, we need to select "sample databases" during SQL Server 2005 installation. You can also download the sample databases separately from the Microsoft web site and install them as necessary.
Microsoft introduced a new sample database called AdventureWorks. In fact AdventureWorks has been extended to give samples covering Business Intelligence (BI) as well. This difference makes the AdventureWorks database the more powerful sample when compared with the Northwind database. In this article, all examples are focused on the AdventureWorks database.
Next: An example of a non-recursive common table expression >>
More MS SQL Server Articles
More By Jagadish Chaterjee
