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

Reference : Pinal Dave (https://blog.sqlauthority.com)