none
Resetting IDENTITY Seed in Table Variable

Answers

  • For a "normal" table, you would use: DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]).

    But as mentioned above, you cannot reseed a table variable but a workaround might be to use ROW_NUMBER() instead.

    For example:

    DECLARE @myTableVariable table (idx int IDENTITY(1,1), somevalue nvarchar(50))
    INSERT INTO @myTableVariable (somevalue) VALUES( 'one')
    INSERT INTO @myTableVariable (somevalue) VALUES('two')
    INSERT INTO @myTableVariable (somevalue) VALUES('three')
    SELECT row_number() OVER (ORDER BY idx), somevalue FROM @myTableVariable
    DELETE FROM @myTableVariable
    INSERT INTO @myTableVariable (somevalue) VALUES('four')
    SELECT row_number() OVER (ORDER BY idx), somevalue FROM @myTableVariable


    Hope this helps, Jerry

    Tuesday, August 27, 2013 7:08 AM
  • Can't be done.

    Tom

    Tuesday, August 27, 2013 2:57 AM

All replies

  • Can't be done.

    Tom

    Tuesday, August 27, 2013 2:57 AM
  • TRUNCATE TABLE is the only way I've ever heard of.
    Tuesday, August 27, 2013 5:24 AM
  • I agree with Tom's comment. There is no straight way to do so.

    I would like to share the following connect item for IDENTITY on Table Variable.

    http://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, August 27, 2013 5:24 AM
  • Hi Pantelis44999

    TRUNCATE TABLE do not work in the case of table variable !

    Adding to others...table variable is designed as a light weight , simple to use, in-memory (though not in all cases) temporary storage mechanism that do not have statistics and index capabilities (index can be created using PRIMARY KEY, UNIQUE KEY during the table definition). So table variable do not supposed to have much data or structure changes. This may be the reason why table variable do not support IDENTITY reset or IDENTITY_INSERT etc.


    Krishnakumar S

    Tuesday, August 27, 2013 6:05 AM
  • Not sure this will work or not but i want to share my thoughts.

    Can you please remove this column from table and add this column again?

    Sorry for the stupid reply/answer.

    Tuesday, August 27, 2013 6:12 AM
  • Not sure this will work or not but i want to share my thoughts.

    Can you please remove this column from table and add this column again?

    Sorry for the stupid reply/answer.

    No worries.

    But the fact is that we can not modify/alter the table variable structure once its created.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, August 27, 2013 6:26 AM
  • For a "normal" table, you would use: DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]).

    But as mentioned above, you cannot reseed a table variable but a workaround might be to use ROW_NUMBER() instead.

    For example:

    DECLARE @myTableVariable table (idx int IDENTITY(1,1), somevalue nvarchar(50))
    INSERT INTO @myTableVariable (somevalue) VALUES( 'one')
    INSERT INTO @myTableVariable (somevalue) VALUES('two')
    INSERT INTO @myTableVariable (somevalue) VALUES('three')
    SELECT row_number() OVER (ORDER BY idx), somevalue FROM @myTableVariable
    DELETE FROM @myTableVariable
    INSERT INTO @myTableVariable (somevalue) VALUES('four')
    SELECT row_number() OVER (ORDER BY idx), somevalue FROM @myTableVariable


    Hope this helps, Jerry

    Tuesday, August 27, 2013 7:08 AM