Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: Oracle RowID in SQLServer?

    Hi,

    In Oracle we have a datatype called 'ROWID' - Oracle uses this datatype to store the address (rowid) of every row in the database. Do we have any equivalent datatype in SQLServer similar to this ?

    Regards,
    Sn

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. At least not in SQL Server 2000.

    Data in a relational database has no inherent order. That said, I think that this functionality has been implemented in 2005 anyway.

    There are alternative ways of achieving the same effect in SQL Server, depending upon what you want to use the row number for. Are you doing paging?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by blindman
    That said, I think that this functionality has been implemented in 2005 anyway.
    Say it ain't SO!!!!

    I can't believe Uncle Bill is caving in to the Oracle crowd (though I grew up on Tandem SQL, and it had the concept of row numbers also).

    That is just SO wrong.

    Jeeze. I'm going to retire early and head to my cabin in the deep, dark Idaho forest with my Commodore 64.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yes, in 2005 there is RANK(), DENSE_RANK(), and ROW_NUMBER() which all do similar things:

    http://msdn2.microsoft.com/en-us/library/ms186734.aspx

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    OK, so listen up all y'all...

    let's all agree we won't use such constructs even though they are available, shall we?

    Kinda similar to urban assault weapons...just because they exist, doesn't make it right to use them
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    They can have my row_number when they pry it from my cold dead data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, there is a crucial difference. The row_number of a row in SQL 2005 can change, but the rownum in oracle can not, as it is stored with the data itself. That should keep the Oracle folks on their toes for a considerable while.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have a guilty secret.....

    I have been using row_number for a little while now to satisfy TOP x BY GROUP queries for tables with wide composite keys.

    Does that mean I can't join your gang?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by pootle flump
    I have a guilty secret.....

    I have been using row_number for a little while now to satisfy TOP x BY GROUP queries for tables with wide composite keys.

    Does that mean I can't join your gang?
    Not necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley
    Well, there is a crucial difference. The row_number of a row in SQL 2005 can change, but the rownum in oracle can not, as it is stored with the data itself.
    Yes - it is determined by its own little order by clause so it isn't totally naughty. I'm not really terribly up on these things but I think it is in the SQL99 standard.... maybe.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by TallCowboy0614
    Not necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.
    Yay! And there was me thinking there might be a catch.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by TallCowboy0614
    Not necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.
    Nobody told me I was allowed to take this damn thing off!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •