CTEs in SQL Server 2005

SQL Server 2005 brings a handful of interesting enhancements inside TSQL Data Manipulation Language. An example:

Let's say that on the good old Northwind Products table we are asked to separate the products by price in three groups; we have to take the maximum price and define three sections: from 0 to 1/3 of the max, from 1/3 to 2/3 of the max, and from 2/3 to the max. Now, we are asked to show the prices of all the products in the middle section along with its difference from the lower limit (1/3 of the max price).

On SQL Server 2000, you could solve it like this:

declare @low money
declare @high money

SELECT @low = ((MAX(UnitPrice)) / 3)
  FROM Products

SELECT @high = (2 * MAX(UnitPrice) / 3)
  FROM Products

SELECT ProductId, ProductName, UnitPrice, UnitPrice - @low
 FROM Products
 WHERE Products.UnitPrice > @low
 AND Products.UnitPrice <= @high

On SQL Server 2005, you can solve it like this:

WITH low AS (SELECT ((MAX(UnitPrice)) / 3)
  AS value FROM Products),
high AS (SELECT (2 * MAX(UnitPrice) / 3)
  AS value FROM Products)
SELECT ProductId, ProductName, UnitPrice, UnitPrice - low.value,
 FROM Products, low, high
 WHERE Products.UnitPrice > low.value
 AND Products.UnitPrice <= high.value

Note the WITH clause which, in this case, define two CTEs (Common Table Expressions): low and high. These CTEs behave like temporary tables (allowing us to do joins, etc.) but in fact they are just resultsets, so they are more efficient than temp tables. The notation is also more compact, and furthermore, it can be used to define a view.