Avoid Using TOP Clause much as possible in SQL server

Most of the time we need to return number of rows (first two rows or first five rows) in the table, then normally we are applying TOP clause. Then it can not be a performance issue or time consuming problem because it will takes only few (one or less) milliseconds.

Syntax;

[ TOP (expression) [PERCENT] [ WITH TIES ] ]

Examples;

USE AdventureWorks ; GO DECLARE @p AS int SET @p='10' SELECT TOP(@p)* FROM HumanResources.Employee; GO

(Books Online – Search for TOP Clause)

But when you use very large table (Ex: more than 1000000) and need to return first 10000 records with using top clause then it will take much time and it can be a performance issue. In that case I am using following method to return first ten thousand records,

USE AdventureWorks ; GO DECLARE @intNum AS int SET @intNum =10000 SET ROWCOUNT @intNum; SELECT * FROM Sales.SalesOrderDetail ORDER BY UnitPrice,SalesOrderID SET ROWCOUNT 0 GO

Using Rowcount option we can avoid top clause but there is have some limitations, when we applying Rowcount option.
i)Inside the view we can’t use Rowcount option.
ii)Using Rowcount we can’t create sub query’s (Ex. SELECT TMP.SalesOrderID,TMP.UnitPrice FROM (SELECT TOP 10000 * FROM Sales.SalesOrderDetail
ORDER BY UnitPrice,SalesOrderID) TMP)