Equivalent of Oracle's RowID in SQL Server
在SQL Server中,Oracle的RowID有什么相同之处?
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 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))
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
链接地址: http://www.djcxy.com/p/24710.html
上一篇: 第一次迁移:如何为新的属性设置默认值?