9

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.

0

1 Answer 1

14

$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];
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.