Oracle 的 RowID 在 SQLServer 中的等价物

在 SQLServer 中，Oracle 的 RowID 相当于什么？

239456 次浏览
小开

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 >= 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.

小开

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 >= '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.

小开

Check out the new ROW_NUMBER function. It works like this:

SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE
小开

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
小开

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

小开

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
小开

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))
小开

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.

小开

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.

小开

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.

小开

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 > 1
小开

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

小开

Please try

select NEWID()

Source: https://learn.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql


提交答案