Simulate Row_Number() in SQL Server 2000

[DanaTA]

Hi,

Is there a way to do this? I want to use custom paging in asp.net in a GridView and want to avoid overworking the server. The data set could be large and it does have in integer id column but I want to show the data in an alpha sort and the id will not be ordered the same. The things I've seen on other forums depend on the id for the paging. I'd like to find a better way. Can anyone help or am I asking too much from the technology?

Dana

 

[r937]

calculating row number with sql is overworking the server

unfortunately there's no easy way around this

for each row, count how many rows have an alpha value less than that row, and this gives you your row number

that's O(n[sup]2[/sup]) or however you say it

alternative: stored procedure cursor loop, equally slow

r937.com | rudy.ca

 

[DanaTA]

Thanks,

I was afraid of that. I have a hosted site and I have SQL Server 2000 as part of my package. I may call and see if they plan to upgrade to SQL Server 2005, but I don't know if they are planning on it or it there are logistical issues concerning an existing database. I have MS Visual Studio 2005 Pro, which came with the developer's version of SQL Server 2005, so I can install it if they do upgrade. I guess I'll have to find the least of the evils in the mean time.

If using the integer id column were acceptable I could at least use the method I saw on 4guys. I may have to go that way. I guess it is still better than default paging in Studio, which does a complete read every page request.

Dana

 

[ptheriault]

Check out this post by myself and Denis. It shows how to add row numbers for 2000 and 2005.

thread183-1306393

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[DanaTA]

Thanks Paul. I'll check that out.

Dana

 

[ESquared]

If you're using ADO, why don't you use the built-in ability to retrieve results in blocks? It's still involving the server but doesn't require the overhead of the server calculating a number. I forget the exact syntax but I believe I am correct that ADO can do this and has methods to show which chunk of rows you are working with and to move to the next chunk.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]