TSQL enhancement: TOP x

In SQL 2k, when you TOP in SELECT statement, it must follow a constant integer number or a number and PERCENT keyword. It’s syntax is:
TOP integer | integer PERCENT  [ WITH TIES ]
 

E.g.:

USE Northwind
GO
SELECT TOP 10 * FROM dbo.Products ORDER BY ProductName
GO
SELECT TOP 20 PERCENT * FROM dbo.Products ORDER BY ProductName
GO
 
WITH TIES specifies that additional rows will be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP …WITH TIES can only be specified if an ORDER BY clause is specified.
 
E.g.:
— the following select returns 8 rows
SELECT TOP 8 * FROM dbo.Products ORDER BY SupplierID
GO

— the following select returns 2 extra rows (10 rows totally) with the same SupplierID
— with the last record in the above query

SELECT TOP 8  WITH TIES * FROM dbo.Products ORDER BY SupplierID
GO

 
When we use TOP in stored procedures, sometimes we have to use dynamic TSQL in order to return different rows based on the requests, or use SET ROWCOUNT, which has its own problem.
 
SQL 2005 enhance the TOP operator in two ways:
1)  to allow a variable to specify the number or percent. With this enhancement, we can get rid of dynamic TSQL and SET ROWCOUNT
2) beside SELECT, it can be used with DELETE, UPDATE, INSERT as well 
 
The syntax is:
TOP (expression) | interger | interger PERCENT [ WITH TIES ]
E.g.
 
DECLARE @Records int
SET @Records=8
SELECT TOP (@Records)  * FROM dbo.Products ORDER BY SupplierID
SELECT TOP (@Records)  WITH TIES * FROM dbo.Products ORDER BY SupplierID
UPDATE TOP (@Records)  dbo.Products
SET UnitsOnOrder=0
WHERE ProductID>60
It will randomly update 10 products with ID>60.
 
 
 
Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

One Response to TSQL enhancement: TOP x

  1. Youjin says:

    this is such a good article! oh the enhancement they made regarding the TOP operator was the limit I always encountered! that is good news! 🙂

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