Alternatives to TOP clause...

Response to a user query.

Hi,

There is an alternative to TOP, 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 to SQL Server 2005 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 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.

Regards

Amit Bansal

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.14.1/1050 - Release Date: 04/10/2007
5:03 PM