How to reseed the identity value in SQL server ?

In SQL server we can reseed the identity value of the table using DBCC Command.

Syntax

DBCC CHECKIDENT ('Table Name',[Reseed],[Reseed Value])

Example

In the following example the current value of identity column (UID) is 3.In the next insert i want to reseed the value to be 11.

Now i reseed the value of UID column to 11.After executing this command the following message will appear.

After executing the above command if we try to insert new record, the UID will be generated as 11 rather than 4.

Now we have the UID value of new row is 11.

In the above scenario the numbers between 4 to 10 will be skipped in this case.

If you want the next value to be 100, then you need to reseed the value as 99.It will always takes the value as reseed value plus one. (i.e) 99 + 1 = 100.

If the identity column is created as primary key, if you try to reseed the value of that column then it will create primary key violation error. 

we can reseed only the identity column in the table.