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

    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

    上一篇: 第一次迁移:如何为新的属性设置默认值?

    下一篇: SQL Server中Oracle的RowID等效