Equivalent of Oracle s RowID in SQL Server

Question

What s the equivalent of Oracle s RowID in SQL Server

User · Answer

ROWID is a hidden column on Oracle tables, so, for SQL Server, build your own. Add a column called ROWID with a default value of NEWID().

How to do that: Add column, with default value, to existing table in SQL Server

User · Answer

From http   vyaskn tripod com programming faq htm q17      Oracle has a rownum to access rows of a table using row number or row id  Is there any equivalent for that in SQL Server  Or how to generate   output with row number in SQL Server       There is no direct equivalent to Oracle s rownum or row id in SQL   Server  Strictly speaking  in a relational database  rows within a   table are not ordered and a row id won t really make sense  But if you   need that functionality  consider the following three alternatives            Add an IDENTITY column to your table    Use the following query to generate a row number for each row  The following query generates a row number for each row in the authors   table of pubs database  For this query to work  the table must have a   unique key   SELECT  SELECT COUNT i au id           FROM pubs  authors i          WHERE i au id  gt   o au id   AS RowID          au fname         au lname AS  Author name  FROM          pubs  authors o ORDER BY      RowID    Use a temporary table approach  to store the entire resultset into a temporary table  along with a row id generated by the IDENTITY     function  Creating a temporary table will be costly  especially when   you are working with large tables  Go for this approach  if you don t   have a unique key in your table

User · Answer

I took this example from MS SQL example and you can see the  ID can be interchanged with integer or varchar or whatever   This was the same solution I was looking for  so I am sharing it   Enjoy    -- UPDATE statement with CTE references that are correctly matched  DECLARE  x TABLE  ID int  Stad int  Value int  ison bit   INSERT  x VALUES  1  0  10  0    2  1  20  0    6  0  40  0    4  1  50  0    5  3  60  0    9  6  20  0    7  5  10  0    8  8  220  0   DECLARE  Error int  DECLARE  id int   WITH cte AS  SELECT top 1   FROM  x WHERE Stad 6  UPDATE x -- cte is referenced by the alias  SET ison 1   id x ID FROM cte AS x  SELECT     id as  random  from  x GO

User · Answer

Please see http   msdn microsoft com en-us library aa260631 v SQL 80  aspx In SQL server a timestamp is not the same as a DateTime column   This is used to uniquely identify a row in a database  not just a table but the entire database  This can be used for optimistic concurrency   for example UPDATE  Job  SET  Name   Name   XCustomData   XCustomData WHERE   ModifiedTimeStamp   Original ModifiedTimeStamp AND  GUID   Original GUID  the ModifiedTimeStamp ensures that you are updating the original data and will fail if another update has occurred to the row

User · Answer

Check out the new ROW NUMBER function  It works like this   SELECT ROW NUMBER   OVER  ORDER BY EMPID ASC  AS ROWID    FROM EMPLOYEE

User · Answer

if you just want basic row numbering for a small dataset  how about someting like this   SELECT row number   OVER  order by getdate    as ROWID    FROM Employees

User · Answer

Please try   select NEWID    Source  https   docs microsoft com en-us sql t-sql data-types uniqueidentifier-transact-sql

User · Answer

You can get the ROWID by using the methods given below    1 Create a new table with auto increment field in it   2 Use Row Number analytical function to get the sequence based on your requirement I would prefer this because it helps in situations where you are you want the row id on ascending or descending manner of a specific field or combination of fields  Sample Row Number   Over Partition by Deptno order by sal desc   Above sample will give you the sequence number based on highest salary of each department Partition by is optional and you can remove it according to your requirements

User · Answer

Several of the answers above will work around the lack of a direct reference to a specific row  but will not work if changes occur to the other rows in a table  That is my criteria for which answers fall technically short   A common use of Oracle s ROWID is to provide a  somewhat  stable method of selecting rows and later returning to the row to process it  e g   to UPDATE it    The method of finding a row  complex joins  full-text searching  or browsing row-by-row and applying procedural tests against the data  may not be easily or safely re-used to qualify the UPDATE statement   The SQL Server RID seems to provide the same functionality  but does not provide the same performance   That is the only issue I see  and unfortunately the purpose of retaining a ROWID is to avoid repeating an expensive operation to find the row in  say  a very large table  Nonetheless  performance for many cases is acceptable   If Microsoft adjusts the optimizer in a future release  the performance issue could be addressed   It is also possible to simply use FOR UPDATE and keep the CURSOR open in a procedural program   However  this could prove expensive in large or complex batch processing   Caveat  Even Oracle s ROWID would not be stable if the DBA  between the SELECT and the UPDATE  for example  were to rebuild the database  because it is the physical row identifier   So the ROWID device should only be used within a well-scoped task

User · Answer

From the Oracle docs  ROWID Pseudocolumn For each row in the database  the ROWID pseudocolumn returns the address of the row  Oracle Database rowid values contain information necessary to locate a row   The data object number of the object The data block in the datafile in which the row resides The position of the row in the data block  first row is 0  The datafile in which the row resides  first file is 1   The file number is relative to the tablespace    The closest equivalent to this in SQL Server is the rid which has three components File Page Slot  In SQL Server 2008 it is possible to use the undocumented and unsupported   physloc   virtual column to see this  This returns a binary 8  value with the Page ID in the first four bytes  then 2 bytes for File ID  followed by 2 bytes for the slot location on the page  The scalar function sys fn PhysLocFormatter or the sys fn PhysLocCracker TVF can be used to convert this into a more readable form CREATE TABLE T X INT    INSERT INTO T VALUES 1   2   SELECT   physloc   AS    physloc            sys fn PhysLocFormatter   physloc    AS  File Page Slot  FROM T  Example Output  -------------------- ----------------         physloc         File Page Slot    -------------------- ----------------    0x2926020001000000    1 140841 0        0x2926020001000100    1 140841 1       -------------------- ----------------   Note that this is not leveraged by the query processor  Whilst it is possible to use this in a WHERE clause SELECT   FROM T WHERE   physloc     0x2926020001000100   SQL Server will not directly seek to the specified row  Instead it will do a full table scan  evaluate   physloc   for each row and return the one that matches  if any do   To reverse the process carried out by the 2 previously mentioned functions and get the binary 8  value corresponding to known File Page Slot values the below can be used  DECLARE  FileId int   1           PageId int   338           Slot   int   3  SELECT CAST REVERSE CAST  PageId AS BINARY 4    AS BINARY 4            CAST REVERSE CAST  FileId AS BINARY 2    AS BINARY 2            CAST REVERSE CAST  Slot   AS BINARY 2    AS BINARY 2

User · Answer

If you want to uniquely identify a row within the table rather than your result set  then you need to look at using something like an IDENTITY column  See  IDENTITY property  in the SQL Server help  SQL Server does not auto-generate an ID for each row in the table as Oracle does  so you have to go to the trouble of creating your own ID column and explicitly fetch it in your query   EDIT  for dynamic numbering of result set rows see below  but that would probably an equivalent for Oracle s ROWNUM and I assume from all the comments on the page that you want the stuff above  For SQL Server 2005 and later you can use the new Ranking Functions function to achieve dynamic numbering of rows   For example I do this on a query of mine   select row number   over  order by rn execution date asc  as  Row Number   rn execution date as  Execution Date   count    as  Count  from td run where rn execution date  gt    2009-05-19  group by rn execution date order by rn execution date asc   Will give you   Row Number  Execution Date           Count ----------  -----------------        ----- 1          2009-05-19 00 00 00 000  280 2          2009-05-20 00 00 00 000  269 3          2009-05-21 00 00 00 000  279   There s also an article on support microsoft com on dynamically numbering rows

User · Answer

If you want to permanently number the rows in the table  Please don t use the RID solution for SQL Server  It will perform worse than Access on an old 386  For SQL Server simply create an IDENTITY column  and use that column as a clustered primary key  This will place a permanent  fast Integer B-Tree on the table  and more importantly every non-clustered index will use it to locate rows  If you try to develop in SQL Server as if it s Oracle you ll create a poorly performing database  You need to optimize for the engine  not pretend it s a different engine   also  please don t use the NewID   to populate the Primary Key with GUIDs  you ll kill insert performance  If you must use GUIDs use NewSequentialID   as the column default  But INT will still be faster    If on the other hand  you simply want to number the rows that result from a query  use the RowNumber Over   function as one of the query columns

User · Answer

I have to dedupe a very big table with many columns and speed is important   Thus I use this method which works for any table   delete T from   select Row Number   Over Partition By BINARY CHECKSUM    order by   physloc     As RowNumber    From MyTable  T Where T RowNumber  gt  1

[sql] Equivalent of Oracle's RowID in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to rowid

Examples related to row-number