what is substitution of ROWID and ROWNUM of Oracle in SQLServer

Posted on 2006-05-26
Last Modified: 2012-06-22
Hi ,
    usually in Oracle people uses ROWID and ROWNUM in Oracle.  I want to know what is the same we can use in SQL Server ?

 help would be appreciated

Question by:bethamsetty
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
      there is NO replacement (btw, ROWID is to be used carefully in Oracle)

      SQL Server 2005:
         you have a ROW_NUMBER() function

      SQL Server in general:
         when you want to get only the first x rows:
         SELECT TOP x ... FROM ... WHERE ...

    Author Comment

       ROW_NUMBER() can be used for SQL Server 2005 not for 2000.  I want it for 2000 and another thing is TOP x.. is used to retrive top rows but if i want to retrieve a range then what is the option
           I want to maintain paging in VB by using SQL Server 2000
             First user will view 100 rows next i want to  bring 100 to 200 rows...

    LVL 142

    Accepted Solution

    >but if i want to retrieve a range then what is the option
    there is no really good option.

    declare @t ( id int , rownumber int identity )

    insert into  @t (id ) select top 200 id from yourtable  order by ...

    select t.*
    from yourtable t
    join @t l
      on =
    and l.rownumber >= 101


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now