genereting rowNumber in select

Question

Post reply

genereting rowNumber in select

Tudor Gabriel

SSC-Addicted

Points: 483
More actions
October 4, 2005 at 1:56 pm

#166294

i need a fast way to add rowNumbers to a table returned by an sql statement.
for example i need to modify "select a from b" to generate
NR a
1 a
2 a
3 a
4 a
i know about the IDENTITY function but it requires an INTO clause in the select, and it will slow down the select alot (to create an temp table for hundreds of times).
i also thought about making an permanent table with only one column, populated with
number from 0 to A BIG NUMBER. And somehow JOIN the columns, but i dont have a common
criteria to put in the ON segment of the join.
and my PK is a GUID, so its not something incremented ....
im out of ideas .... and i kind'of need the answer because the deadline is coming

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

October 4, 2005 at 2:08 pm

#594985

Why can't you handle that client side?

Tudor Gabriel SSC-Addicted Points: 483 More actions · Answer 2

because im trying to limit the number of results the user gets , TOP wont be enough because i need to generate lets say the next 20 results . and the only way to do this(in my mind) was to number my table rows. this way it would be independ of sorts, filters etc. as oposed to a real column (with autoinc) in the table.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can do it like this :

Select top 20 * from () dtSearh where GUI > @LastShownResult

Tudor Gabriel SSC-Addicted Points: 483 More actions · Answer 4

unfortunately i dont have sqlserver installed here, and i cant test it until tomorrow , but because the GUID is not incremental, and not generated in a certaing order (like ascending) wont GUI > @LastShownResult fail?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ya it would... I had this working but the requirements were somewhat different. Depending on how long it takes to search and how many times the searches are hit, the solution his gonna change. Do you have some more data on that?

Tudor Gabriel SSC-Addicted Points: 483 More actions · Answer 6

i had to write DB application to work with sqlserver, and its 75% complete.

all my pk's are guids. im just a noob in this, and i didnt used result paging (or how its called). Now because the server has lots (250000) of records, it works slow.

So i need to rewrite most of my querrys to return just a fragment of results, not all of them at once.

So my Pk is an Uniqueidentifier .... and somehow i need to make a procedure which i will call with the "page" number and return the next N records from the last return.

i thought there must be an easy way to do it .... "identity" looked like the perfect thing until the INTO requirment. maybe its a way to join 2 columns from two separate tables into one, whithout them having something in common ..... still searching for solutions

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

How many rows are returned from a single search (normally)?

Tudor Gabriel SSC-Addicted Points: 483 More actions · Answer 8

somewhere around 300-400 .... but the rows are the result of a querry with some heavy calculations , not just a simple querry. The user has to wait already for 5-6secs for the result to appear ... and the waiting time will rise ... so i need to return just a couple of them at once .....

i already tried optimizing with indexes .... and the tabels are normalized ... so this is the last way to get some major performance boost .....

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

How about caching the GUI into a results table (with identity or what method of generating it). Then query back from that tables and then generating the requested results?

Tudor Gabriel SSC-Addicted Points: 483 More actions · Answer 10

i was trying to avoid the creation of a table every time i use the select .... but if this is the only solution .... it can be done

thank for the help!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

No I mean a global SavedSearchResults table with something that identifies the search (UserID, SearchID, GUI, RowID). Then you can easily use that table to requery the next results...

Select top 20 Do, The, Calculations, Here from dbo.SavedSearchResults inner join... ON SavedSearchResults.GUI = ?.GUI and RowId > 20.

All you have to do his send the searchid back to the application and this can be worked out quite well.

Tin Le SSC Enthusiast Points: 100 More actions · Answer 12

-- Try this.

use pubs

Select RowNumber = Count(ut1.pub_id)

,ut1.pub_id, ut1.pub_name, ut1.[city]

From dbo.publishers ut1 With (nolock),dbo.publishers ut2 With (nolock)

Where ut1.pub_id > ut2.pub_id

Group By ut1.pub_id, ut1.pub_name, ut1.[city]

Order By 1

Tin Le

Tin Le SSC Enthusiast Points: 100 More actions · Answer 13