auto-increment identity column

[thedougster]

Using Visual C# 2008 Express and SQL Server 2008 Express, I would like to insert new records into database "AdventureWorks", table "Person.Contact".

To my surprise, this table's int-value identity column "ContactID" does not appear to be auto-increment. I don't know how to confirm for sure that this is so. (I have installed SQL Server Management Studio, if that helps.)

How can I confirm this, and how can I make ContactID auto-increment?

And incidentally, will auto-increment just start incrementing from the highest existing ContactID, or will it first use lesser ContactID values that are absent from the table (presumably due to deletions)?

 

[gmmastros]

You can determine if a column is an identity column with this...

[tt][blue]Select ColumnProperty(Object_Id('[!]YourTableNameHere[/!]'), '[!]YourColumnNameHere[/!]', 'IsIdentity')[/blue][/tt]

The easiest way to change a column to be an identity is...

Open SQL Server Management Studio.
Drill down to your table.
Right Click -> Design
Click on your column
in the column properties (at the bottom of the window), scroll down to 'Identity Specification' and change the value to YES.

Now, when you close the table, the changes will be made (making your column be an identity column).

This will retain your existing id's. Any gaps in your Id's will NOT be filled it. New rows will get a value 1 greater than the current max value.

Behind the scenes, SQL Server will create another table with the same structure (except the identity specification). Then, it sets identity insert on, copies your existing data to the new table, drops the original table and renames the temp table it created to the same name as your original table.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Please check the thread above in the forum. I accidentally answered in the wrong thread on your question. I checked my AdventureWorks and the Person.Contact table has ContactID set to be identity.

 

[danvlas]

thedougster: It's well worth saying that you shouldn't expect your Identity field to contain a continuous sequence in the future.
Whenever an insert fails or a transaction is rolled back, or a record is deleted, the Identity values are lost.

If a bulk insert is done with Identity Insert set to ON, the gaps in the batch will be transferred to your table.

If you intend to give any user meaning to that field, (except unicity) do NOT use Identity.

markros: the thread above should already be the thread below. Maybe 3-4 posts down.




Daniel Vlas
Systems Consultant

 

[danvlas]

markros: I believe you wanted to point to:

thread183-1538554

It's easy, just copy the thread number and paste it into your post.



Daniel Vlas
Systems Consultant

 

[markros]

Thanks, Daniel.