How to - Solve SQL Server 2012 Identity Problem. Identity increased from 6 to 1000 ............

During application development, we often input dummy data into our database for testing purposes. But then we we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete the truncate command. This will delete data from table and also reset the identity column value to 0.

Example

truncate table [table_name]

truncate table product

But truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset.

So, in this case, first you need to delete data from the child. 

After deleting data, fire this command and it will reset 

your identity column to 0.

DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])

-- for example

DBCC CHECKIDENT('product', RESEED, 0)

Question

Say I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:

1, 2, 3, 4, 5, 1004, 1005

You can use below two choices

Use trace flag 272 , this will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.

Solution 1:

     1. Open "SQL Server Configuration Manager"

      2.                Click "SQL Server Services" on the left     pane

     3.                Right-click on your SQL Server instance name on the right pane ->Default: SQL Server(MSSQLSERVER)

    4.               Click "Properties"

    5.                Click "Startup Parameters"

    6.               On the "specify a startup parameter" textbox type "-T272"

   7.               Click "Add"

8.Confirm the changes 

CLICK ON aDD bUTTON

And then restart your sql server.

Use a sequence generator with the NO CACHE setting

solution 2

CREATE SEQUENCE MySeq AS int

  START WITH 1

  INCREMENT BY 1

  NO CACHE;