Exploring Yukon T-SQL - Top Option

Note : This article was written on Beta 1 syntaxes. Hence we cannot guarantee the details provided will be exactly as found in the final RTM release. But the context and code-snippets outlined would give an indication to the features being introduced in the next release of SQL Server a.k.a Yukon.

There are tons of new features that are getting introduced in the next version of SQL Server codenamed Yukon. And this is the first in the series of articles, I would explore T-SQL enhancements we are presented with this new version. In this article we will explore how the TOP operator on SQL Server has been enhanced. There are significant enhancements we can observe while using this operator.

Batching Update Statement

I feel this is a significant enhancement to look forward. Now lets see how we would have achieved the same requirement in our SQL Server 2000 syntax.

SET ROWCOUNT 100

WHILE (1=1)

BEGIN TRANSACTION

      Update <<TableName>>

      SET ....., MyLastUpdate = 'Date'

      WHERE MyLastUpdate < 'Date'

      -- Updates 100 Rows here

      IF @@RowCount = 0

      BEGIN

            Commit transaction

            Break

      END

      Commit Transaction

END

To run you through the logic here, ROWCOUNT causes SQL Server to stop processing once the query reaches the specified number of rows is returned. This technique is useful because it avoids the concurrency hits that large updates incur. Smaller the row count, lesser is the chance the update task will prevent other users from accessing the data. Moreover, this method will also be friendly with the to transaction logs.

Well, now lets turn to Yukon and what it is to offer us.

Interesting implementation right. We have removed the requirement for ROWCOUNT. Understand we would have reset the ROWCOUNT back to 0 (un-restricted), if we were to do more processing. Now that requirement is also eliminated. 

Other enhancements 

As in the above code the same is applicable for other DML operations such as Insert, Delete and Select. The interesting implementation comes with the SELECT clause. In the earlier release this was a repeated requirement from the newsgroups. Can I pass a parameter to the TOP Operator. This call has been made for quite sometime now. And this has been heard from Microsoft. A valid requirement surely. Here is the typical syntax for the same.

Declare @myCount Int

Set @myCount = 5

Select TOP (@myCount) * from Northwind..Employees

This is the final statement we would write. Now there is a small change from the way we used to write. Note the change is the use of brackets while defining the TOP operator as this. An error is thrown if we donot represent the same in brackets. Now there is an extension to the same. The count can also be an expression from a select operation. The below code is just an example. This brings all records from the employee tables. This is to just illustrate the syntax.

The same holds good for use of TOP with percentages also. A typical example to select the TOP 5% of rows from the employees table would look like:

Declare @myCount Int

Set @myCount = 5

Select TOP (@myCount) PERCENT * from Northwind..Employees

There are tons of such features to be explored under SQL Server Yukon. As the release of SQL Server gets closer, we will have more such information for grab for you.