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.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server: Alternatives to TOP clause – ROWCOUNT, ROW_NUMBER()”

  1. Hi,

    I am just looking for an equivalent to top clause that follows where clause (something similar to Limit in Mysql). I require this to be used in another application to retriev the latest record from DB. The clause that comes next to select cant be used. Can you please help me.

    Regards

    Rajkumar

  2. Hi,

    I am looking for an equivalent to trunc function in sql server I require this to be used in another application to

    Sangamesh

Leave a Reply

Your email address will not be published.