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.