Post reply

SQL Identity

  • March 8, 2005 at 12:52 am

    #163809

    In SQl, I want to make the one of the field names of "int" data type to be the identity and identiti incerment automatically. But the problem is i don't want to start from. I want to start on some number (eg. 100). How can i fix in this setting to start from 100?

    Any ideas!

  • March 8, 2005 at 1:26 am

    #545820

    That's not a problem.

    When you create the table, or add a column, you can specify the start value and increment of the identity column

    CREATE TABLE TestTable

       (Col1  int  IDENTITY(100, 2)

       ,COl2  ....

       )

    This starts the identity at 100, and increments by 2.  You can specify any integer value (positive or negative) for either parameter. 

    Do note that you can not alter an existing column to give it the identity property.  If you are trying to do this, you may need to add a new column with identity and then drop the old one.

     

    Hope this helps,

    Scott Thornburg

  • March 8, 2005 at 1:58 am

    #545824

    So many thanks. I got it.

  • March 9, 2005 at 8:54 am

    #546167

    Expanding on this, can I change the identity number on an existing field?  We would like to use our identity field as contract number, but have the number start with fiscal year, 2005nnnnnn.  Then when new fiscal year starts, reseed field with 2006000000.

    Is this possible? 

  • March 9, 2005 at 10:01 am

    #546185

    yes  you can use

    dbcc checkident ('TblName', RESEED, 200600000)

    just keep in mind the limits on the maximum int value could be exceded I would recommend to use two digits instead

    Cheers,

     


    * Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply