Post reply

Identity Column values.

  • October 15, 2009 at 9:38 am

    #227139

    Hello Friends

    In a table, we have a column where the values are generated using the "identity(1,1)" attribute. Now I delete the first 20 values. Now the new values when it will generate will first from 21 onwards since I have deleted the first 20 values.

    Now, is there any method available to generate the identity values from 1 onwards..? Can anybody please help me out?

    Thanks.

  • October 15, 2009 at 9:42 am

    #1066354

    Two methods

    dbcc checkident

    and

    Truncate Table

    If you do a search on the checkident command you should be able to find the parameters and usage

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 15, 2009 at 10:21 am

    #1066383

    The IDENTITY property behaves that way intentionally, to minimize blocking and deadlocking when trying to assign the next value in a sequence. If you're trying to maintain a consistent column of incremented data, then the identiy property is probably not what you should be using. If a transaction that incremented the IDENTIY column by 1 rolls back, then the next commited transaction will cause a gap in the sequence for that column.

    The other option you have to re-use the identity values that are missing is to explicity set identity insert on:

    SET IDENTITY_INSERT [your table] ON

    insert statements with the identity values you want to insert

    SET IDENTITY_INSERT [your table] OFF

  • October 15, 2009 at 11:32 am

    #1066409

    Be aware of some gotchas when using SET IDENTITY_INSERT ON. For example:

    CREATE TABLE #Tident(ID INT IDENTITY,Col1 VARCHAR(5))

    INSERT INTO #Tident

    SELECT 'A' --repeated for a total of 7 rows

    This works and the IDENTITY increments as it should.

    Now SET IDENTITY_INSER #Tident ON and try the same insert statement and you will recieve the following error message

    An explicit value for the identity column in table '#Tident' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Thus you have to modify your insert statement to

    INSERT INTO #Tident(ID,Col1)

    SELECT 1,'A' UNION ALL

    SELECT 2,'B' UNION ALL

    SELECT 3,'C' UNION ALL

    SELECT 4,'D' UNION ALL

    SELECT 5,'E' UNION ALL

    SELECT 6,'F' UNION ALL

    SELECT 7,'G'

    So far so good, but if you do not protect against or forget to execute

    SET IDENTITY_INSERT #Tident OFF

    You can end with this mess (In my example deleted only the first 5 entries)

    IDCol1

    1A

    2B

    3C

    4D

    5E

    6A

    7A

    6F

    7G

    Now you can create duplicate identity values so go carefully and test, test, test your implementation on a non production system to insure that it works properly.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • October 15, 2009 at 12:16 pm

    #1066425

    My purpose is solved by running the below query ::

    DBCC CHECKIDENT('table_name', RESEED, 0) --> [0: if I want to start the counter value from 0]

    Thanx for all your help in replying to me..

    Thanks.

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

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