ROWID function [Miscellaneous]

Returns an unsigned 64-bit value that uniquely identifies a row within a table.

Syntax
ROWID( correlation-name )
Parameters
  • correlation-name   The correlation name of a table used in the query. The correlation name should refer to a base table, a temporary table, a global temporary table or a proxy table (permitted only when the underlying proxy server supports a similar function). The argument of the ROWID function should not refer to a view, derived table, common table expression or a procedure.

Remarks

Returns the row identifier of table corresponding to the given correlation as an unsigned 64-bit value (BIGINT).

The value returned by the function is not necessarily constant between queries as various operations performed on the database may result in changes to the row identifiers of a table. In particular, the REORGANIZE TABLE statement is likely to result in changes to row identifiers. Additionally, row identifiers may be reused after a row has been deleted. Hence users should refrain from using the ROWID function in ordinary situations; retrieval by primary key value should be used instead. It is recommended that ROWID be used only in diagnostic situations.

Although the result of this function is an UNSIGNED BIGINT, the results of most arithmetic operations on this value have no particular meaning. For example, you should not expect that adding one to a row identifier will give you the row identifier of the next row. Moreover, only equality and IN predicates are sargable if they involve the use of ROWID. If necessary, predicates involving ROWID, such as ROWID( T ) = literal, cast to a 64-bit UNSIGNED INTEGER value. If the conversion cannot be performed a data exception will occur. If the value of literal is an invalid row identifier then the comparison predicate evaluates to FALSE.

The ROWID function cannot be used inside a CHECK constraint on either a table or a column, nor can it be used in the COMPUTE expression for a computed column.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

The following statement returns the row identifier of the row in Employee where id is equal to 105:

SELECT ROWID( Employees ) FROM Employees WHERE Employees.EmployeeID = 105;

The following statement returns a list of the locks on rows in the Employees table along with the contents of those rows:

SELECT * 
  FROM sa_locks() S JOIN Employees WITH( NOLOCK ) 
     ON ROWID( Employees ) = S.row_identifier 
  WHERE S.table_name = 'Employees';