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 (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – 2005 TOP Improvements/Enhancements

  1. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  2. If there are duplicate records in the table. For example:
    There are 3 records, in which 2 records are same. If I will query like ‘select top 2 from table_name’ then what will be…

  3. when i run the below command , shows 201 row affected
    but when i refresh the database don’t changed on it(database)

    update dbo.data
    set id = floor(id) or round(id,0)

    best regards

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s