Blog Post

How can I use the identity column, without knowing identity column’s name.

,

One of the most fun parts of blogging is when you learn something completely unexpected while writing a blog. The other day I was writing my most recent SQL Homework post about using Microsoft Docs (or as I still call it Books on Line). Since I wanted some things for people to look up I was just randomly going through the different pages, picking a section, reading it, and coming up with something for people to look up. For the most part I knew, or was at least somewhat familiar with the different things I was reading. Then as I was reading about the SELECT Clause I ran into $IDENTITY. It’s kind of an interesting keyword. Unsurprisingly, it just returns the value for the identity column. (FYI there is also a $ROWGUID keyword.) Here is an example:

CREATE TABLE #Table1 (Col1 INT NOT NULL IDENTITY(1,1), Col2 INT, Col3 INT);
INSERT INTO #Table1 VALUES (100,101), (200,201), (300,301);
SELECT $IDENTITY FROM #Table1;

One thing I noticed is that the column is actually headed by the correct column name (Col1) not $IDENTITY.

So what would you use this for? There is an example from MS Docs. Generic code. I can also see using it when writing a piece of working code where I want to know if a table has an identity column or not, and if so what it’s called. Either way I thought it was a fun piece of SQL Trivia, so enjoy.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

4.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating