Introduction:
In this article,i am going to explain about the new “TOP” keyword enhancements in SQL SERVER 2008.

Main:

In SQL Server 2000 and earlier versions, TOP allows you to limit the number of rows returned as a number or a percentage in SELECT queries. As of SQL Server 2005, you can use TOP in DELETE, UPDATE, and INSERT queries and can also specify the number (or percentage) of rows by using variables or any valid numeric returning expression (such as a subquery). The main reason for allowing TOP with DELETE, UPDATE, and INSERT was to replace the SET ROWCOUNT option, which SQL Server traditionally didn’t optimize very well.

You can specify the TOP limit as a literal number or an expression. If you’re using an expression, you must enclose it in parentheses. The expression should be of the bigint data type when you are not using the PERCENT option and a float value in the range 0 through 100 when you are using the PERCENT option. You might find it useful to create an expression for TOP and make it a parameter that you pass in to a stored procedure, as shown in below,

Using TOP enhancements in a stored procedure

CREATE PROCEDURE uspReturnTopOrders(@NumberOfRows bigint) AS SELECT TOP (@NumberOfRows) SalesOrderID FROM Sales.SalesOrderHeader ORDER BY SalesOrderID

Executing the stored procedure is easy. Just pass in the number of records you want (in this case, 100), as shown here:

EXEC uspReturnTopOrders @NumberOfRows = 100

Here are the results:

SalesOrderID
————
43659
43660
43661
43662
43663
:

(100 row(s) affected)

Using a subquery can be powerful when you’re doing things on the fly. The following example shows how to get the TOP n orders based on how many rows are in the SalesPerson table:

SELECT TOP (SELECT COUNT(*) FROM Sales.SalesPerson) SalesOrderID, RevisionNumber, OrderDate FROM Sales.SalesOrderHeader ORDER BY SalesOrderID

Because there are 17 rows in the SalesPerson table, the query returns only the top 17 rows from the SalesOrderHeader table:

SalesOrderID Revision NumberOrderDate
————- ——— ————————–
43659 1 2001-07-01 00:00:00.000
43660 1 2001-07-01 00:00:00.000
43661 1 2001-07-01 00:00:00.000
43662 1 2001-07-01 00:00:00.000
43663 1 2001-07-01 00:00:00.000
43664 1 2001-07-01 00:00:00.000
43665 1 2001-07-01 00:00:00.000
43666 1 2001-07-01 00:00:00.000
43667 1 2001-07-01 00:00:00.000
43668 1 2001-07-01 00:00:00.000
43669 1 2001-07-01 00:00:00.000
43670 1 2001-07-01 00:00:00.000
43671 1 2001-07-01 00:00:00.000
43672 1 2001-07-01 00:00:00.000
43673 1 2001-07-01 00:00:00.000
43674 1 2001-07-01 00:00:00.000
43675 1 2001-07-01 00:00:00.000

(17 row(s) affected)

Using the PERCENT option is just as easy. Just add the PERCENT keyword, and make sure that your variable is a float. In below 2-20, we’re asking for the top 10 percent, so we’ll get back 3,147 records because the AdventureWorks2008 SalesOrderHeader table has approximately 31,465 records in it.

Returning TOP percentages

DECLARE @NumberOfRows AS float SET @NumberOfRows = 10   SELECT TOP (@NumberOfRows) PERCENT * FROM Sales.SalesOrderHeader ORDER BY OrderDate

Conclusion:
Hope this helps,
Happy Coding.