SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Identity in a SELECT

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


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:

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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...