none
Simulate ROW_NUMBER () in SQL 2000

    Question

  • I would like to generate an incrementing column value to each row found in a query in SQL Server 2000. 

    My understanding of SQL Server 2005 is that you can accomplish this approximately along these lines:

       insert into MyList (myNo, lName)
       select ROW_NUMBER() myNo, lName
       from Employee

       select * from MyList
       order by myNo

       myNo         lName
       -----         ------
       1               Jones
       2               Smith
       3               Wilson
       4               Nicholson

       Etc... (I think I have this right ... )

    In any event, I'd like to be able to do the same thing in SQL Server 2000.  I'm aware of the posibility of using IDENTITY columns, but every time I run the insert I'd like the first myNo value to be 1, and I don't want to have to reseed the identity column for each execution. 

    I've noted a few comments in some of these threads that the concept of generating a ROW_NUMBER means that I have a poor data model.  I'd just ask that we all take a leap-of-faith and accept the idea that this would be helpful ... I suppose the fact that it is now included in SQL 2005 indicates that *somebody* thinks it would be helpful.  BTW Oracle has this too......those evil guys! : )

    Thanks for any suggestions!

    Doug



    Tuesday, November 22, 2005 7:23 PM

Answers

  • Using temporary table with identity column will give best performance in SQL Server 2000. But you can generate the sequence number using the traditional SQL way like:

    insert into MyList (myNo, LName)
    select (select count(*) from Employee as e2
              where e2.LName <= e1.LName) as myNo, e1.LName
    from Employee as e1

    Note that you may have to flatten the query sometimes to get better performance. Even then it will not perform that well. The reason why it is not recommended to rely on sequence numbers is due to the fact that it doesn't have meaning in a table unless you sort the rows in a particular order. You can achieve this easily by performing the operation on the client. There are however cases where generating row_number is helpful but not just to number rows. It can be used to help in calculating moving aggregates easily, generate partitions for data, calculate percentile and so on.
    Tuesday, November 22, 2005 7:57 PM

All replies

  • Using temporary table with identity column will give best performance in SQL Server 2000. But you can generate the sequence number using the traditional SQL way like:

    insert into MyList (myNo, LName)
    select (select count(*) from Employee as e2
              where e2.LName <= e1.LName) as myNo, e1.LName
    from Employee as e1

    Note that you may have to flatten the query sometimes to get better performance. Even then it will not perform that well. The reason why it is not recommended to rely on sequence numbers is due to the fact that it doesn't have meaning in a table unless you sort the rows in a particular order. You can achieve this easily by performing the operation on the client. There are however cases where generating row_number is helpful but not just to number rows. It can be used to help in calculating moving aggregates easily, generate partitions for data, calculate percentile and so on.
    Tuesday, November 22, 2005 7:57 PM
  • OK, thanks, I guess I'll have to go to plan B and occasionally reseed the identity column.  I'll have a related question shortly.

    Thanks!

    Doug
    Tuesday, November 22, 2005 8:12 PM
  •  Doug B wrote:

    I've noted a few comments in some of these threads that the concept of generating a ROW_NUMBER means that I have a poor data model.  I'd just ask that we all take a leap-of-faith and accept the idea that this would be helpful ... I suppose the fact that it is now included in SQL 2005 indicates that *somebody* thinks it would be helpful.  BTW Oracle has this too......those evil guys! : )


    Theoretical Question about the concept of 'rownumbers'. . .

    Again, I am working off my general understanding of how databases work

    I am assuming that by rownumber you desire is the actual physical sequence of a record in the database in order of insertion.

    Lets say you insert 3 records into a single column (called 'field') database table (myTable) 'A', 'B','C'

    I would expect
    select rownumber, field from myTable order by rownumber

    to yield

    1, 'A'
    2, 'B'
    3, 'C'

    now lets say I execute

    update mytable set field = 'B' where field = 'B'

    (yes I understand its not a realistic assignment, but I do this for theortical reasons)

    I would expect

    select rownumber, field from myTable rownumber

    to now yield:

    1, 'A'
    2, 'C'
    3, 'B'

    Why???

    Because, databases don't really update the row (from what I understand). . .
    they reinsert the row being updated while replacing the existing data with the data in the update.

    The original row is then marked for deletion.
    Actual deletion is typically under the control rdbms

    Now. . .

    Here is my point about intrinsic value -

    Nothing in the values of the data changed, yet executing the exact same query resulted in totally different results.

    That violates basic theory and should never happen.
    You should never, ever, EVER develop an application that uses physical location of a record in a database.

    Thats what a spreadsheet is for.

    You'll note that ROW_NUMBER in SQL Server is determined over a 'partition clause'.
    Again, this has no intrinsic value because if rows are deleted between execution, or the partition clause' changes, the row number changes. 


      

    I am not trying to be an ***.
    Seriously I am not.
    I am trying to save you from having to jump through hoops down the road.

    If you need to keep track of records by the order they are input, use a DateTime field.

    That has an intrinsic value with regard to the data.
    That is 'DateTimeInserted'
    Tuesday, November 22, 2005 9:31 PM
  • Thank you for your comments.  The assumptions you're working from are not correct.  Nonetheless, I do appreciate those who take time to contribute.

    All the best!

    DB
    Tuesday, November 22, 2005 9:58 PM

  •  The assumptions you're working from are not correct. 

    please enlighten me.
    I mean it. . . please enlighten me.

    my email is in my profile
    Tuesday, November 22, 2005 10:01 PM
  •   insert into MyList (myNo, lName)
       select ROW_NUMBER() myNo, lName
       from Employee

       select * from MyList
       order by myNo

       myNo         lName
       -----         ------
       1               Jones
       2               Smith
       3               Wilson
       4               Nicholson

       Etc... (I think I have this right ... )

    use the identity function (like I told you weeks ago!)
    like this:


    create
    database foobar
    go
    select identity(int, 1,1) myNo , LastName 
          into
    foobar.dbo.mylist 
       from AdventureWorks.Person.
    Contact
    go
    select * from
    foobar.dbo.mylist
    go
    drop database foobar

    now what happens when myNo 1 gets fired and there are 10000 records in the myList????
    You are going to rebuild mylist????
    Recaculate the indexes????
    I hope you didnt reference myNo anywhere as a foreign key else you have to propogate that too!!! And,  my god!!! the indexes on all those tables.
    Exponential Gridlock!!!!  FLAWED DATAMODEL!!!!

    as far as my other comments.
    I suggest you research -

    'Ghost Record'
    DBCC PAGE

    SQL 2005 may be different but I doubt it!
    and oracles psuedocolumns (rownum/row_number) were NEVER meant for the usage you are proposing.

    Someone please chime in here.

    Oh nevermind. . .
    Pound a screw with a hammer. . .if the board sticks, I guess it was a nail!
    That is . . . until the wind blows!

    Wednesday, November 23, 2005 7:37 AM
  • Blair ...

    Wow, I'm really not sure what to say here.  I'm not sure about the comment about "weeks ago" ... I just posted this two days ago.  I think someone else had a related question.  That was where I noticed that there seemed to be so much concern about the use of automated numbers other than an identity.

    Your examples and comments generally make sense.  As I indicated before I have a particular problem I'm trying to solve.  My understanding of the etiquite for this forum is that one should be brief-and-to-the-point.  Hence, my request for forum members to simply take a "leap of faith" and offer suggestions with the problem at hand.

    Of course, this leaves some situations where those "trying to help" make assumptions about problems posted that may or may not fit in with the exact problem at hand.  Nonetheless, in most cases varried feedback with different ideas allows us to put together a broad spectrum of opinions and come up with the best solution.

    To be honest, some of your technical comments were actually helpful with the problem I'm working on.  I must admit, however, that the way you come across in your responses leaves me thinking that it is more important for you to feel that you're correct than it is to necessarily help others.  After a while this becomes very negative.  In an earlier comment you asked me to "enlighten you" ... who would want to communicate with you, when the feedback offered has such a negative tone?  Seriously?

    I wish you the best, and I appreciate positive input from all.

    DB


     
    Saturday, November 26, 2005 1:35 AM
  • again, I dont mean to come off as an a$$. . .

    To me, the beauty of database is the transparency of physical location of data in the 'space' it is defined. When I see approaches that hint at building in dependencies on a records postion in a table, red flags fly! buzzers buzz! and alarm bells ring! My tone was not meant to be arrogant but express extreme concern and to spur you to hold back for a second and think about what you are doing. Yes, I have no idea as to what your project entails, but this is just one of those things that literally scares the dickens out of me as I recently have spent many hours fixing something that 'smells' very similar! That is, a Progress to SQL server migration from hell!!!

    As far as a few weeks ago. . . there was a similar question with regards to foxpro. . . it was one of those 'i used to do this in foxpro, how do I do it in sql server?'

    Apologies if I came off negatively.

    b
    Monday, November 28, 2005 6:11 AM
  • Blair ...

    I think that is the nicest thing that anyone could have possibly saidI now have a lot of respect for you, and it was kind of you to acknowledge my feelings.

    I also don't want to come off as an a** ... there's no benefit in that.  I guess I've learned from this experience that my intrepretation of a forum member's comments needs to be taken with a more relaxed attitued on my part ... maybe I need a little Xanax to smooth over my work day!

    OK, so after fishing through your comments and those from Umachandar above, it looks like the row_number() idea is a dead end, even when combined w/ a leading datetime column.  This was actually a preliminary question.  After going through this input, I posted the *real* problem in all of its full, gory detail back on Nov 22nd.  Given the holiday last week (and the complexity of the problem), I haven't seen any responses. 

    If you're up to it and can spare the time the complete (I hope) problem description is in the thread Using a trigger to insert committed rows into a table following SQL Server's tran log order.  It is obvious you have a lot of experience w/ SQL Server, so any feedback would be appreciated.

    Regarding the above thread, I'm thinking I'm going to have to use just an identity to get the ordering rigt.  I'm just concerned w/ the impact of having to re-seed the identity at some point in the future.  My numbers don't have to be contiguous, just ascending.  I believe there's also that little identity gap feature when you bounce the server that I think may be a minor problem to deal with.

    Unfortunately, for the moment I'm also stuck with triggers.  We may be able to pull something together in the future w/ a transaction log parser or feed from MS Replication down the road, but for this release I don't have an alternative.

    If you can spare the time, I'm sure it would be a great help.

    Thank you again,

    Doug
    Monday, November 28, 2005 5:39 PM
  • yes tone and inflection (and mostly sarcasm) are lost online!

    in my best homer simpson: 'aw-gh-gh-gh. . . .xaaaa-naaaax'

    see my post in reply to your transaction/trigger question
    Tuesday, November 29, 2005 6:24 AM
  • Hi

     

    I have multiple datastreams, with contiguous packets. Using row_number() I can see when I have dropped a packet as the number is more than 1 less than the row_number(). This allows me to request the packet to be resent.

     

    I have an unwieldy way of using a massive dataset to cater for the maximum packets received and using subqueries get the missing numbers in SQL2000. Is there no way to get a simulation of row_number() ? It would be so much better than  

    Select ServiceCode, (Select Count(*)
       from Messages
       where ServiceCode = M.ServiceCode
       and effectivedate = M.EffectiveDate
       and MsgSeqNo < M.MsgSeqNo), MsgSeqNo
    from Messages M
    where ServiceCode = 'J21'
    and effectivedate = '2007-09-18'
    order by MsgSeqNo asc

     

     

    SELECT ServiceCode, a.MsgSeqNo+1 AS BeginSeqNo,
           b.MsgSeqNo-1 AS EndSeqNo
       FROM
         (SELECT ROW_NUMBER() OVER(ORDER BY (MsgSeqNo)) AS RowNum, MsgSeqNo
            FROM MsgsReceived) a
       INNER JOIN
         (SELECT RANK() OVER(ORDER BY (MsgSeqNo)) AS RowNum, MsgSeqNo
            FROM MsgsReceived) b
       ON a.RowNum = b.RowNum - 1
       WHERE (b.MsgSeqNo - a.MsgSeqNo) <> 1

     

    Ideas, hints or directions appreciated.

    Thursday, February 21, 2008 10:55 AM
  • I used this to update a subset of records in my table where my position number field got messed up.

    --create a temporary table based on the origional with a counter field
    SELECT new_number = 0, id_field INTO #tmp_desc FROM description_tb
    where desc_type = 'category' and desc_text = 'section1'

    --increment the new number for the temporary table
    DECLARE @counter int
    SET @counter = 0
    UPDATE #tmp_desc
    SET @counter = new_number = @counter + 1

    --update the origional table with the new counter where the id field exists
    UPDATE description_tb 
    SET old_number = ( SELECT new_number
    FROM #tmp_desc
    WHERE #tmp_desc.id_field = description_tb.id_field)
    WHERE EXISTS
      ( SELECT #tmp_desc.id_field
        FROM #tmp_desc
        WHERE #tmp_desc.id_field = description_tb.id_field)

    --throw out temp table
    drop table #tmp_desc

    Randall <><

    • Edited by CodeThumper Wednesday, July 28, 2010 2:52 AM gramar and documentation
    Wednesday, July 28, 2010 2:42 AM