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.