SQL Server: Alternatives to TOP clause – ROWCOUNT, ROW_NUMBER()

Hi Friends,

This is from my old blog so dont give me credit for wirting something new 🙂

———————-

There is an alternative to TOP clause, which is to use ROWCOUNT. Use ROWCOUNT with care, as it can lead you into all sorts of problems if it’s not turned off.

SET ROWCOUNT 10
SELECT * from Customers
ORDER BY CompanyName

Another one…

WITH, ROW_NUMBER and OVER

This is new from SQL Server 2005 and onwards and looks really useful. Below is an example to get records 20 to 29 from a results set. It might a bit strange at first, but I will go through the query so you’ll see how simple it is.

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

The WITH in SQL Server 2005 onwards specifies a temporary named result (CTE), much like a temporary table in previous versions of SQL Server. However, the import parts are the ROW_NUMBER and the OVER statement, which create a row number on each row based on the Company name. This is like adding an identity seed to a temp table with an order by clause.

Look in books online for TABLESAMPLE as well but that does not necesaarily return top records.