Numbering the ResultSet - Equivalent of Rownum

Question

Post reply

Numbering the ResultSet - Equivalent of Rownum

anilg

SSC Journeyman

Points: 82
More actions
July 18, 2003 at 7:01 am

#106115

I am trying to migrate to SQL Server from Oracle and I have trouble finding an equivalent to ROWNUM (Oracle) in SQL Server. Is it possible in SQL Server 2000 to number the result set in a sequence?
Thank You
Anil K Godumagadda

Viewing 10 posts - 1 through 9 (of 9 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

There is no ROWNUM (Oracle) in SQL Server. You may add addtional 'Identity' column to table as row number.

anilg SSC Journeyman Points: 82 More actions · Answer 2

quote:
There is no ROWNUM (Oracle) in SQL Server. You may add addtional 'Identity' column to table as row number.
Thanks, but the problem is i cannot add a column to the database. All i can do is change the queries to get an equivalent of Rownum as Rownum is not available in SQL Server.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi anilg,

quote:
Thanks, but the problem is i cannot add a column to the database. All i can do is change the queries to get an equivalent of Rownum as Rownum is not available in SQL Server.

if I understand roght, you're looking for a consecutive numbering without a identity column?

Somewhere I found this.

create table #t( f1 int primary key, c char(3))

insert #t values(1, 'abc')

insert #t values(11, 'abc')

insert #t values(111, 'abc')

insert #t values(23, 'cba')

insert #t values(234, 'zze')

insert #t values(2345, 'zze')

insert #t values(345, 'yyw')

select * from #t

select t2.f1, t2.c,

(select count(*) from #t t1 where t1.f1 <= t2.f1) AS uniq

from #t t2

order by 3

Does this help?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 4

Create a temp table with same columns as your original table and additional identity column. Use insert into #temptable select column1, column2 ... from originaltable and query the temp table.

accent-atlant SSC Journeyman Points: 81 More actions · Answer 5

Do you test it on 1000000 row??? I have slow perfomance...

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi accent-atlant,

quote:
Do you test it on 1000000 row??? I have slow perfomance...

I guess it should only be a work around for a missing ident column.

Sure, that you will run into performance issues with million row tables.

BTW, interesting homepage (not, that I have understood too much )

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Unfortunately (and it has been suggested as I made it at least 4 times to reps) no true ROWNUM in SQL 2000. There are a number of tricks you can try but with a lot of data many will be very slow. You best bet is to write small app using ADO and move to the record tht way.

However is there any unique filed in you table that it is the clustered index? If so you can modify a bt what Frank gave to not use a temp table which should help the performanc equite a bit (temp table is written to tempdb and that IO will be very slow).

hamishahern SSC-Addicted Points: 498 More actions · Answer 8

quote:
temp table is written to tempdb and that IO will be very slow

Dont forget that you can declare a table variable which is in memory temp table and not tempdb(disk) based.

Antares686 SSC Guru Points: 125444 More actions · Answer 9

But since Table Variables cannot push except to the Page File when memory is needed 1,000,000 file can be an issue and severly degrading to server performance. Table Variables are best utilized with small datasets.