T-SQL New features in SQL Server 2005 - Part 2

In continuation to the previous post, today we will be discussing about two new enhancements, namely Top and Common Table Expressions (better known as CTEs).

Top:
It isn't so that the Top clause is introduced in this version. Its about the new possibilities where you can use Top clause in an enhanced fashion. Take for example, you are using SQL Server 2000 and have to write a stored procedure for implementing server side paging. In a typical scenario, you will be passing a value to a parameter for defining the page size (i.e. number of records to be returned) along with other parameters like page index etc. Now an optimum way to use the value of page size would be to use "SET ROWCOUNT" with the page size parameter of the procedure, so that only the desired number of rows are returned:

SET ROWCOUNT @pageSize

SELECT col1, col2 FROM Table1

However, now you have the power of writing your query in following way:

SELECT TOP(@pageSize) col1, col2 FROM Table1

This will result in same number of rows as specified by @pageSize. Note the use of parenthesis enclosing the @pageSize variable. These are mandatory if you are using a variable with Top clause. You can, however, omit these if using a fixed numeral.

Top clause can now be combined with Insert, Update and Delete statements as well apart from Select. Reviewing following examples will provide a clearer view on this:

DECLARE @Count INT

SET @Count = 50

SELECT TOP (@Count) * FROM Categories

This statement will return top 50 record from table named categories. Another usage can be as follows:

DECLARE @Count INT

SET @Count = 2

UPDATE TOP (@Count) Categories

SET CategoryName = CategoryName + 's'

This statement will update the first two records in 'Categories' table and append an 's' to the CategoryName. In a real life scenario, the queries will be more complex than the above mentioned ones, but those will only be a variation (for eg. Update statement may have a 'Where' clause to filter out the necessary records which needs to be modified.)

It is also worth mentioning that Microsoft recommends using the Top clause in new development work since 'SET ROWCOUNT' is slated to be removed from future versions of SQL Server. More information about the same can be found here.

Common Table Expressions:
Common Table Expressions(or CTEs) can be defined as "An expression that is referred to by name within the context of a single query." What this means is that you define a query, give it a name(or alias) and use that alias as if it is a physical table. An example will make it clear what I am referring to:

WITH EvenNumbers AS -- (1)

(

SELECT Col1 FROM Numbers WHERE Number%2=0 -- (2)

)

SELECT Col1 FROM EvenNumbers -- (3)

Here, we have a physical table named "Numbers" which contains random numbers in "Col1" column. Lets dissect the query to understand how it works:
(1) The syntax to define a CTE is by using keyword "WITH". In the above statement, "EvenNumbers" is the alias that we wish to assign to the resultset from the query defined in (2).
In line (2), we are selecting only those values of Col1 which are divisible by 2 i.e. even numbers. The statement in line (3) returns all the records found in (2).

Looking at it the first time, you might not be able to visualize the numerous options CTEs provide. A very good use of CTEs can be in set-based operations. Imagine writing a complex query having join on may be 5-6 tables...can be difficult and time consuming. Looking at it from CTEs perspective, however changes it all. Internally it is doing just the same thing, but provides a better and easier mechanism to understand and write query. Another very useful application of CTEs is in writing recursive queries. Yes! I mean recursive queries!!! We will be looking at that application of CTEs in coming days. Till then....happy querying!!!