SQL SERVER – Finding Out Identity Column Without Using Column Name

There is an interesting observation when querying the identity column of a table

Let us create the following tables

CREATE TABLE test1(id INT IDENTITY (1,1)
)GOCREATE TABLE test2(id INT)

Note that the table test1 has identity column and table test2 does not have it

Now you can find the values of identiy column without using actual column name

SELECT $identity FROM test1

You will not get an error and look at the column name. It is id which is the identity column of test1.
The quick way to check if the table has identity column and it it has, to know the column name use the following

SELECT $identity FROM test1 WHERE 1=0

If the table does not have identity column you will get an error

SELECT $identity FROM test2 WHERE 1=0

The error is

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