It is easy to get the details of identity column created on table, as you can quickly check the table schema.
But it would be interesting too see, if someone asks you to find out an identity column created on table along with its values while you are not allowed to see table schema, and do not know identity column name.
Lets take an example to demonstrate scenario, first we create a table having an identity column.
CREATE TABLE #T (SNO INT IDENTITY(1,1) , REMARKS VARCHAR(50)) INSERT INTO #T (REMARKS) VALUES ('Accepted')
Next, we insert one sample records in this table.
SELECT * FROM #T
So far, we have created a table named #T having an identity column named SNO.
Following statement returns an identity columns with values in identity column from 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.
Thank you!
This solved a problem I had when retrieving the identity value using Output.
Now I can use INSERTED.$identity instead of the specific identity column name!
Example:
CREATE TABLE #RowsInserted (Id INT);
INSERT INTO MyTable (ColOne, ColTwo)
OUTPUT INSERTED.$identity INTO #RowsInserted
values (‘Val1′,’Val2’);
SELECT Id FROM #RowsInserted;