none
There is no direct equivalent to Oracle's rownum or row id in SQL Server

    Question

  • hi,

    There is no direct equivalent to Oracle's rownum or row id in SQL Server

    there is any possiblity are alternate thing is exist then please reply

     

    regards

    santosh

     

    Saturday, September 23, 2006 5:42 AM

All replies

  • Sure, there is a function in SQL Server 2005 called row_number()

    Here's an example:

    WITH [EMPLOYEE ORDERED BY ROWID] AS
    (SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE)
    SELECT * FROM [EMPLOYEE ORDERED BY ROWID] WHERE ROWID =4

    I'd advise looking up the documentation for it, as there are several use cases.

     

    Saturday, September 23, 2006 9:12 AM
  • Santosh--

    No there is no direct hidden column like Oracle having (rowid)

    this is what we do:

    We manually added a column with name as ROWID and set its datatype to timestamp, which is a database-wide unique number that gets updated every time a row gets updated.

    Note that timestamp get updated, when you update that row, but in Oracle the rowid does not change, when we update.

    Regards,

    Thanks.

    Gurpreet S. Gill

     

     

    Saturday, September 23, 2006 1:08 PM
  • sql server 2005 definitely has a row id feature.

    i've actually used it for paging application data.

    see my post above.

    Saturday, September 23, 2006 2:01 PM
  • you can use a Globally unique identifier(GUID) and the newid function

    B. Use NEWID in a CREATE TABLE statement

    This example creates cust table with a uniqueidentifier data type, and uses NEWID to fill the table with a default value. In assigning the default value of NEWID(), each new and existing row has a unique value for the cust_id column.

    -- Creating a table using NEWID for uniqueidentifier data type. 
    CREATE TABLE cust
    (
     cust_id uniqueidentifier NOT NULL
       DEFAULT newid(),
     company varchar(30) NOT NULL,
     contact_name varchar(60) NOT NULL, 
     address varchar(30) NOT NULL, 
     city varchar(30) NOT NULL,
     state_province varchar(10) NULL,
     postal_code varchar(10) NOT NULL, 
     country varchar(20) NOT NULL, 
     telephone varchar(15) NOT NULL,
     fax varchar(15) NULL
    )
    GO
    -- Inserting data into cust table.
    INSERT cust
    (cust_id, company, contact_name, address, city, state_province, 
     postal_code, country, telephone, fax)
    VALUES
    (newid(), 'Wartian Herkku', 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', NULL,
     '90110', 'Finland', '981-443655', '981-443655')
    INSERT cust
    (cust_id, company, contact_name, address, city, state_province,
    postal_code, country, telephone, fax)
    VALUES 
    (newid(), 'Wellington Importadora', 'Paula Parente', 'Rua do Mercado, 12', 'Resende', 'SP',
     '08737-363', 'Brazil', '(14) 555-8122', '')
    INSERT cust
    (cust_id, company, contact_name, address, city, state_province,
     postal_code, country, telephone, fax)
    VALUES
    (newid(), 'Cactus Comidas para Ilevar', 'Patricio Simpson', 'Cerrito 333', 'Buenos Aires', NULL, 
     '1010', 'Argentina', '(1) 135-5555', '(1) 135-4892')
    INSERT cust
    (cust_id, company, contact_name, address, city, state_province,
     postal_code, country, telephone, fax)
    VALUES 
    (newid(), 'Ernst Handel', 'Roland Mendel', 'Kirchgasse 6', 'Graz', NULL,
     '8010', 'Austria', '7675-3425', '7675-3426')
    INSERT cust
    (cust_id, company, contact_name, address, city, state_province,
     postal_code, country, telephone, fax)
    VALUES 
    (newid(), 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
     'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68')
    GO
    
    Saturday, September 23, 2006 3:32 PM
  • 
    No, that's not the same as Oracle's ROWID feature.  In Oracle, every table has a column called "ROWID" which is automatically created and maintained by the server.  It's soft of the same idea as a SQL Server IDENTITY column, but it's automatically added to the table by the server.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    sql server 2005 definitely has a row id feature.

    i've actually used it for paging application data.

    see my post above.

    Saturday, September 23, 2006 5:39 PM
  • how about adding a default newid() contraint  to a guid column
    Sunday, September 24, 2006 1:55 AM
  • I want to add

    SQL SERVER

    i) Having uniqueidentifier datatype, which we need to add manually as

        ROWID uniqueidentifier NOT NULL DEFAULT newid()

        this is unique in the whole database as of GUID.

    II)  Having timestamp  datatype, which we need to add manually as

        ROWID timestamp 

    in the first option the GUID remains constant, but in the second case the timestamp changed whenever the update command is executed for that row.

    ORACLE

    i)  ORACLE dont need these things, it having Pseudo coloumn called ROWID.

         this is unique in the whole database.

    so there is no direct pseudo or hidden column in sql server to do the same as rowid for oracle.

    we manually need to add column to the table, to do the rowid work, depending upon the type of rowid we can select the case I or II for SQL server.

    we are facing the same problem, as we were shifting form SQL Base to ORACLE as well as SQL Server, so for SQL Server we use the timestamp datatye.

    Rrgards,

    Thanks.

    Gurpreet S. Gill

     

       

     

       

    Monday, September 25, 2006 6:10 AM
  • In SQL Server 2000, you can acheive this by following query

    SELECT        (SELECT COUNT(i.au_id)
                   FROM pubs..authors i
                   WHERE i.au_id >= o.au_id ) AS RowID,
                  au_fname + ' ' + au_lname AS 'Author name'
    FROM          pubs..authors o
    ORDER BY      RowID

    but this is not advisable, for the query which will retrive lots of records, if you want for display purpose then try to get the same via front end.
    Monday, September 25, 2006 7:42 AM
  • Both these pseudo-columns are not available in SQL Server. Oracle pseudo-columns do not take additional space in the database and only materialized at the time of query execution. If you want mimic something like rowid you will have to persist it. So you will have additional storage cost, possibly index storage etc. You can also get something like row id using DBCC PAGE command it is not what you are looking for and it is not in any easy queryable format. Others have posted about various techniques to get the information.
     
    But the real question is what are you doing with something like rownum or rowid? Are you using rownum for paging results? If so, then there are techniques using IDENTITY column or TOP which will perform faster in SQL Server. The ROW_NUMBER solution might be straight-forward to convert but it is not the best in terms of performance. As for rowid, it is hard to say. If you are using it as a key then it is wrong usage because you need to model data based on the natural keys and keys that you can rely on. Physical locators can change if the table is recreated or moved or reorganized for example. So please explain your usage further to suggest the correct migration strategy.
    Monday, September 25, 2006 6:33 PM
  • The application paging is the issue, though.  We need to show a page full of records like this:

    1     record one

    2     record two

    3     record three

    ..

    The example given above works but in reverse order.  Can anyone fix it?  See below

    create table mytab (id int)

    insert into mytab values(1)

    ...

    insert into mytab values(5)

    select  (select count(i.id) from mytab i where i.id >= o.id) as rownum, o.id as id from mytab o order by rownum

    Rownum     ID

    1              500

    2              400

    3              300

    4              200

    5              100

     

    Doc

     

    Friday, June 27, 2008 2:42 PM