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)

SQL Scripts
Previous Post
SQL SERVER – User Defined Functions (UDF) to Reverse String – UDF_ReverseString
Next Post
SQL SERVER – FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup

Related Posts

6 Comments. Leave new

Leave a Reply