Is there a way to use rownum without using a temp table?

You mean the identity function? If so, no.

Show quoteHide quote

ok, thanks.

I am experiencing a issue and I am trying investigate a better solution.

A developer is using the rownum - yes the identity function to sort her rows
by using a temp table.

Sometimes when executing the stored procedure is runtime is pretty fast but
other times after testing it a few times, the run time becomes slow.
Sometimes it takes over 1 minute to return the results. We tried placing an
index on the temp table. We initially saw a difference but again if ran a few
more times for testing, it takes over a minute or sometimes 4.
It goes from 2 sec or less than 10 seconds to run up to 1 to 4 minutes to
run. The only thing that I can relate this performance issue is to the temp
table usage.

So I was running some tests to see the performance without the temp table,
it works but I am not using the rownum -the identity function which she needs.



Show quoteHide quote

Why does she need it, since such things are not attributes in the data
model, but deal with display or physical storage?   The front end can
add a number for display.

We were trying to avoid passing that much data to the front end to sort. We
were trying to have the db to handle the sorting.

Show quoteHide quote

http://www.aspfaq.com/2427



Show quote Hide quote

I don't see the rank function in 2K. Isn't this a new feature in 2005?

Show quoteHide quote

Where do you see a rank function in that article?  Keep in mind that in the
following:

    rank = COUNT(*)

Rank is just a column alias.




Show quoteHide quote

ok, thanks. I am trying to use it in the below query:

@rownumLow INT = 1,
@rownumHigh INT = 20 - these are declared and passed in the stored procedure

->Select
           rank = (
                       SELECT count(*)
                       FROM a
                               where
                            rownumlow < rownumhigh
                            ),                            
        DISTINCT   a.adid, a.adnumber, a.adtitle, SUBSTRING(a.content, 1, 300) AS
content, a.premiere, a.featured,
            a.phone, a.extension, a.address, a.city, a.state, a.zip,
a.bedroomcountcode, a.bathroomcountcode,
            a.monthlyrent, a.monthlyrenthigh, a.propertylongitude,
a.propertylatitude, a.showaddress, a.showmap,
            a.dateactivated, a.adismultiunit, a.category, a.hitstoday

FROM    rcv_addata AS a WITH(NOLOCK)
WHERE    (a.active = 1)
Order by rank

Show quoteHide quote

Actaully there is an error in the code prior to the rank test. I just notice
from another change done last week.

Show quoteHide quote

Deep SQL .Net integration question
any performance diff using -Between- or >= and <= ?
Spreadsheet totals
Foreign Key may cause multilple cycles
Bulk Insert from Excel