RowNum in SqlServer

Question

RowNum in SqlServer

avnish kumar

SSC Enthusiast

Points: 155
More actions
September 17, 2002 at 11:52 pm

#78742

hi
we use RowNum in Oracle to get that many rows.
what is equivalent to RowNum in SqlServer2000 and MSAccess.
Thanks
Avnish

Avnish

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

There is no real equivalent to rownum in SQL Server. Depending on what you want to do, you can use the TOP keyword (SELECT TOP 10 a, b, c FROM d ORDER BY a).

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

avnish kumar SSC Enthusiast Points: 155 More actions · Answer 2

avnish kumar

SSC Enthusiast

Points: 155

September 18, 2002 at 12:50 am

#436737

Thanks

Avnish

Antares686 SSC Guru Points: 125444 More actions · Answer 3

FOr SQL 2000, if you need to resize on the fly conside SET ROWCOUNT x which will return the number of rows defined by x. Also, overall performance with SET ROWCOUNT is faster, especially on large datasets as rows are output as soon as they enter the buffer and the query ends when the number is reached, where TOP all the data enters the buffer then the first x are output.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Note: Opps had @@ROWCOUNT instead of ROWCOUNT, but fixed now.

Edited by - antares686 on 09/19/2002 04:16:30 AM

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

Antares, I have sometimes seen recommendations to use TOP instead of SET ROWCOUNT because the optimizer doesn't work very well with SET ROWCOUNT. I haven't tested enough to be sure, but what is your view on that matter?

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Antares686 SSC Guru Points: 125444 More actions · Answer 5

quote:
Antares, I have sometimes seen recommendations to use TOP instead of SET ROWCOUNT because the optimizer doesn't work very well with SET ROWCOUNT. I haven't tested enough to be sure, but what is your view on that matter?
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu

Really, I test all solutions I can think of and see which provides best overall performance on a query. However, I have only noted when using Order By's seems to run the same pace as the TOP x method but not always but that could be the size of the data I am using. Have you heard anything specific I can test???

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

quote:
Have you heard anything specific I can test???

Hmm, no not anything I can think of. Just seen it as a general recommendation sometimes, but never with something real to prove it.

And yes, I agree, always all possibilities for a problem.

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/