Setting the Identity Property of a Column

SQL provides the identity property to give us the ability to have a column that auto increments (auto number) and it's very easy to work with. The default works well of starting at one and incrementing by one, but you do have other options, and it doesn't have to always be an integer column either. A quick run through that shows you what you need to know to add this to your table design.

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.

Dennis Allen on 2/5/2010

Something that comes up for me from time to time is needing a user friendly record identity. My favored solution at present is to use an int Identity() but then present that value to the user base 36 encoded and decoding it when they provide it back. Do you have any best-practices for this need of an identity that is easy for a user to remember and to enter into an input form of some kind?

AB0047AF23 on 2/5/2010

Can we go ahead and Change the SEED to a Value that is less than the highest number in the table. What would happen if we insert a new row. If that could have been explained it would be great. Btw, Thanks for such short videos.

Raj on 2/6/2010

It's nice video

Diana Dee on 2/6/2010

You did not mention "not for replication" when setting the identity property.

Prashant Thakwani on 2/6/2010

Good demo Andy. However it would also be nice if resetting the identity is also shown in this demo. I know there are people who dont know that and can be interested into that. But overall, a fanstastic video.

Jiwa on 2/7/2010

detailed one. thanks.

manu jaidka on 2/8/2010

Its nice. Hope it continued for few more minutes!

ricardoacaraujo@hotmail.com on 2/8/2010

Muito Bom

Ed Svastits on 2/8/2010

Great basic stuff. It would be great to have a follow-up video on using identity columns (or not) in replicated database table scenarios.

Josep on 2/8/2010

Nice test after watching the video :D:D

Andre on 2/8/2010

I did not know that there was something called change script. Thanks; keep on the good work.