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

Leave a comment

May 12, 2022 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,746 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: