locked
auto-increment identity column RRS feed

  • Question

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

    Tuesday, March 24, 2009 11:13 PM

Answers

  • The ContactID is on auto increment..

    [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,


    This is what i copied..
    you can confirm it............. ok i know u are a newbie.. lets take it the easy way..
    right click on the table
    select "script table as"
    select "create to"
    select "new query editor window"

    you will see the code ther.. n this identity(1,1) thing means that it is auto incremented by 1
    You can not change any of the column settings, if u want to.. then you have to create a new table, with the changes, then have to paste all the records in it..


    ------------------------------
    If any of the answers help...Please do mark it as "Answer" so that others with similar problems can get help too..

    Regards,
    N_SQL

     

    Thursday, April 2, 2009 11:22 AM
  • select objectproperty(object_id('dbo.TableName') , 'TableHasIdentity'
    >>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.

    >> and how can I make ContactID auto-increment?

    YOu may have to create a new table with auto-increment and insert data. Please follow this to understand more about identity values. http://blog.beyondrelational.com/2009/02/sql-server-identity-related-functions.html


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

    Identity will use the next number based on the seed information and it can't use the absent values (gaps) automatically unless we make some programmatic changes.




    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Tuesday, March 24, 2009 11:58 PM

All replies

  • select objectproperty(object_id('dbo.TableName') , 'TableHasIdentity'
    >>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.

    >> and how can I make ContactID auto-increment?

    YOu may have to create a new table with auto-increment and insert data. Please follow this to understand more about identity values. http://blog.beyondrelational.com/2009/02/sql-server-identity-related-functions.html


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

    Identity will use the next number based on the seed information and it can't use the absent values (gaps) automatically unless we make some programmatic changes.




    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Tuesday, March 24, 2009 11:58 PM
  • Bob,

    Should you want to start (after adding the identity column) with the highest contactid as the starting point you can do:

    DBCC CHECKIDENT ("Person.Contact", RESEED, NewSeed#); 

    Obviously you can TSQL in a SELECT MAX() + 1 for the new seed value.

    -h
    Wednesday, March 25, 2009 12:20 PM
  • The ContactID is on auto increment..

    [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,


    This is what i copied..
    you can confirm it............. ok i know u are a newbie.. lets take it the easy way..
    right click on the table
    select "script table as"
    select "create to"
    select "new query editor window"

    you will see the code ther.. n this identity(1,1) thing means that it is auto incremented by 1
    You can not change any of the column settings, if u want to.. then you have to create a new table, with the changes, then have to paste all the records in it..


    ------------------------------
    If any of the answers help...Please do mark it as "Answer" so that others with similar problems can get help too..

    Regards,
    N_SQL

     

    Thursday, April 2, 2009 11:22 AM