TOP Enhancements In SQL 2005 and Later versions

In previous versions of SQL Server, TOP operator used only to return the number of rows or a percentage in SELECT queries. In SQL Server 2005, 2008 or later versions, you can use TOP in DELETE, UPDATE, and INSERT queries and can also specify the number of rows (or percent) 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.

Following Sample Query is a sample Example for delete with Top

-- Top with delete query

delete top (2) from dbo.table3

-- Top with Subquery

USE AdventureWorks

GO

SELECT TOP (SELECT COUNT(*) FROM Sales.SalesPerson)

SalesOrderID, RevisionNumber, OrderDate

FROM Sales.SalesOrderHeader

ORDER BY SalesOrderID