Suppose you are asked to find out Identity column used in a table and get its values while you do not know table column names and which columns are having an identity.
Lets take an example to demonstrate scenario, First we create a table having identity column.
CREATE TABLE #T (SNO INT IDENTITY(1,1) , REMARKS VARCHAR(50)) INSERT INTO #T (REMARKS) VALUES ('Accepted')
then we insert one sample records into table.
SELECT * FROM #T
We have created a table #T having identity column – SNO .
Following statement returns identity columns with values from a table without using column name in query.
SELECT $IDENTITY FROM #T
As we see, It returns identity column name with values even without using any column name in T-SQL Query.
$IDENTITY returns error if table does not have an identity column.
Now we create a table that does not have any identity column.
CREATE TABLE #T1 (SNO INT , REMARKS VARCHAR(50)) INSERT INTO #T1 (SNO,REMARKS) VALUES (1,'Accepted')
SELECT $IDENTITY FROM #T
As you can see, this time it returns an error as we do not have any identity column in our table.