SQL SERVER – 2005 TOP Improvements/Enhancements

SQL Server 2005 introduces two enhancements to the TOP clause.
1) User can specify an expression as an input to the TOP keyword.
2) User can use TOP in modification statements (INSERT, UPDATE, and DELETE).

Explanation : User can specify an expression as an input to the TOP keyword.
In SQL SERVER 2000 usage of TOP is implemented in following query.



SELECT TOP 10 TableColumnID
FROM TableName

For ages Developers and DBAs wants to pass parameters to TOP keyword. IN SQL SERVER 2005 it is possible. Example, @iNum is variables set before SELECT statement is ran.

DECLARE @iNum INT
SET @iNum = 10
SELECT TOP (@iNum) TableColumnID
FROM TableName

This code will run fine in SQL SERVER 2005. Now it is possible to pass variables through Stored Procedures and T-SQL Code. Not only variables but we can run sub-query as a parameters of the TOP. Example, following query will return TOP 1/4 rows from table TableName.



SELECT TOP (
SELECT CAST((
SELECT COUNT(*)/4
FROM TableName) AS INT)) *
FROM TableName

Explanation : User can use TOP in modification statements (INSERT, UPDATE, and DELETE).
In SQL SERVER 2000 usage of the limited row modification was implemented using SET ROWSET command. Example,

SET ROWCOUNT 5
UPDATE TableName
SET TableColumn = 14
SET ROWCOUNT 0

In SQL SERVER 2005 limited row modification can be achieved by TOP keyword function. Example,



UPDATE TOP(5) TableName
SET TableColumn = 14

Common Mistakes With the New TOP Syntax

The first mistake is asking for a limited number of rows without ORDER BY. Without it, SQL Server returns whichever rows it finds first, and that can change from one run to the next. If you want the ten newest orders, say so with an ORDER BY on the date column.

Also keep the parentheses around the number. They are required when you pass a variable or a sub-query, and in INSERT, UPDATE and DELETE. Microsoft recommends them for plain numbers in a SELECT too, so the code looks the same everywhere.

In an UPDATE or DELETE, you cannot add ORDER BY directly, so the rows picked are not in any order you control. If order matters, pick the rows first in a CTE that uses TOP with an ORDER BY, then update or delete through the CTE.

One pattern I like is removing a big pile of old rows in small chunks: delete a few thousand rows, and repeat while @@ROWCOUNT is above zero. Each chunk is a short transaction, so locks are held briefly and the log has a chance to clear between chunks. The old SET ROWCOUNT way still works, but Microsoft has said it will stop affecting INSERT, UPDATE and DELETE in a future release, so avoid it in new code.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.