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)
)
GO
CREATE 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'.
Reference : Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Nice Post Pinal
Hi Pinal,
Is “where part is required here”? i am getting the same result without using it.
Hi Pinal,
Is “where” part is required here? I am getting the same result without it.
without WHERE clause, it will pull the identity column including data……
Correct SekaR. Thanks for answering when I was busy.