Using the Identity Function

This is very much a niche technique, a way to add a column that has the identity property set when you create a table using SELECT INTO. While you may not use it often, it can be a useful thing to know about for the rainy day when you need it.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

kevin on 2/4/2010

good video, I had forgetten about the on the fly function

Josep on 2/4/2010

Nice way to do it, at least it's less code than the one that I do: SELECT ROW_NUMBER() OVER(ORDER BY TestID) AS MyID, * INTO NewTable2 FROM NewTable1

Stéphane Thinel on 2/4/2010

Did not know this trick. Thank you!

Douglas Kemp on 2/4/2010

I love learning little tricks like this !!

17EB042629 on 2/4/2010

yup

john on 2/4/2010

technique that's really handy!

Jeffrey Roughgarden on 2/4/2010

Good tip!

Kamen Angelov on 2/5/2010

Very usefull. Windowed functions like ROW_NUMBER() are not applicabale when need to keep the input sequence.

Clayton Smith on 2/5/2010

Rated as excellent. I often have to use identities on the fly so it's a great tip to know.

achied on 2/5/2010

interesting andy

Harvey Schwartz on 2/5/2010

It would be very helpful if the sample statement (showing the syntax to use) were available at the conclusion of the video as text that could be copied to the clipboard for use elsewhere. That would be for this and all other videos that include SQL examples. Harvey

Orlando on 2/5/2010

very cool...we have a need for this going on right now so this may be used in some refactoring work we're planning.

manu jaidka on 2/8/2010

Was useful..

concoran on 2/16/2010

I would have like some more explaination about it. For instance, is it a good idea to use this technique or just create an identity column in a new table and copy stuff in it. But really a good video. It helps you learn and ponder.