Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Nov 2008
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto-increment identity column

    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)?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,124
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Well, I only have the Express version of MSSM, but I would expect the answer to be the same or nearly so.

    Open up the DB in question.
    RIGHT CLICK on the table in question and click MODIFY (you're not really going to mod)
    In the list of columns that appears, click on the one you think should be an IDENTITY column.

    There should be a COLUMN PROPERTIES window/subwindow appear.
    Scroll down in the properties and you should find an entry labeled IDENTITY SPECIFICATION. Expand it to see all the properties (or lack thereof) of the IDENTITY for this column.

    90% sure if you convert a non-identity column to identity that it starts incrementing after largest existing value. Yeah, I just tested it. Make that 100%.

  • #3
    Regular Coder
    Join Date
    Nov 2008
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Old Pedant:

    In SQL Server 2008 Management Studio Express, the route to Identity Specification is somewhat different: expand table, expand Columns or Keys, right-click identity column, click Modify, in lower right frame scroll down to Identity Specification, expand it.

    Three options appear: (Is Identity), Identity Increment and Identity Seed, which are set to Yes, 1 and 1, respectively.

    So are you saying I should toggle (Is Identity) from Yes to No and back again to turn on auto-increment?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •