Always be learning right? So when I saw this I had to go HU?! That’s new. What’s going on here?

SELECT
IDENTITY(INT, 1,1) AS Id,

Now, looking at it a bit more closely you’ll see that this is a function call, not just a property. Now, in my research for this post I did find where I’d mentioned this function briefly in my somewhat comprehensive identity post. Technically I didn’t mention so much as it was mentioned to me in the comments so I added it to the list. I guess I either didn’t look at it closely enough at the time or it’s just one of those cases where I forgot. Either way, it’s worth highlighting now.

So what does the identity function do? It lets you add an identity column to the output of a SELECT INTO. So when we run the following code:

SELECT
IDENTITY(INT, 1,1) AS Id,
Name
INTO temp
FROM sys.databases

We get a table with the following format:

See? Identity column! You can pass in the datatype, seed, and increment just like when setting an identity property. And as with any calculated column when using an INTO you have to give it a name. Unfortunately, you can’t use an existing column to populate the column (database_id in the above sample for example). If you want to do that you’ll have to create the table with 0 rows and then set IDENTITY_INSERT on and do an INSERT INTO.