Sql-server – Is $IDENTITY documented and reliable in SQL Server

identitysql server

I learned that SQL Server can return the value of the (single) identity column of a table using the pseudo-column $IDENTITY:

SELECT $IDENTITY FROM Table

Is this feature documented and reliable? The only official mention of it is on the IDENTITY page but it's buried in a code sample. This suggest that it might be intended to be undocumented. There are remarkably few Google matches for this feature as well.

Best Answer

$IDENTITY (and $ROWGUID) are documented in SELECT Clause (Transact-SQL).

I would caution against using it because it will throw an error when a table doesn't have an identity column. Take these two examples - the first one works (check your Results tab), but the second one throws an error:

CREATE TABLE #EmployeesWithIdentity (EmployeeID INT IDENTITY(1,1), EmployeeName VARCHAR(50));
SELECT $IDENTITY FROM #EmployeesWithIdentity;
GO

CREATE TABLE #Employees (EmployeeName VARCHAR(50));
SELECT $IDENTITY FROM #Employees;
GO

Here's what the second one produces:

Msg 207, Level 16, State 1, Line 6
Invalid column name '$IDENTITY'.

If you're looking to find which columns have an identity field, I'd use the method described in this Stack Overflow post instead:

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

Or, using the catalog views:

SELECT s.name,t.name,c.name
FROM sys.schemas AS s 
JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
JOIN sys.identity_columns AS c ON t.[object_id] = c.[object_id];
Related Question