What is $IDENTITY?

This was in one of my slides of SQL Brain Bashers presentation which was done at SQL Server User Group Meeting.

What is $IDENTITY? Everybody knows that we use IDENTITY property on columns where we need automatically incrementing identification number. The $IDENTITY does not set anything, but allows us to reference the column using it. Here is an example;

CREATE TABLE dbo.Employee

(

    EmployeeKey int IDENTITY(1,1) PRIMARY KEY

    ,FirstName varchar(50) NULL

    ,LastName varchar(50) NOT NULL

    ,

    ,

)

GO

When query for column like EmployeeKey, $IDENTITY can be used instead on column name;

-- both queries return same resultset

SELECT EmployeeKey, LastName

FROM dbo.Employee

SELECT $IDENTITY, LastName

FROM dbo.Employee