SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051

This is the 51th episode of SQL in Sixty Seconds Video and we will see in this episode how to RESEED identity of the table column. Identity column is every increasing (or decreasing) value based on the interval specified in its property. In today’s SQL in Sixty Seconds video we will see that how we can reseed the identity value to any other value. In the video I demonstrate that we can set the value to any value which is greater than the current column value however, you can also set the identity value to any value lower than the current column as well.

In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100));
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select data
SELECT *
FROM TestTable
GO
-- Returns current Identity Value
DBCC CHECKIDENT ('TestTable', NORESEED);
-- Resets the current Identity value to maximum value
DBCC CHECKIDENT ('TestTable', RESEED, 11);
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select Table
SELECT *
FROM TestTable
ORDER BY ID;
-- Drop Table
DROP TABLE TestTable;

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

Database, SQL Identity, SQL in Sixty Seconds, SQL Scripts
Previous Post
SQLAuthority News – Sharding or No Sharding of Database – Working on my Weekend Project
Next Post
SQL SERVER – A New Approach to Scale .NET Applications

Related Posts

12 Comments. Leave new

  • Sanjay Monpara
    May 8, 2013 3:43 pm

    In one of our software we have given feature of purge database at UI side,
    in this case I have used reseed option with value 0.

    Reply
  • I HAVE TO SELECT A VARCHAR COLUMN HAVING LENGTH 4 OR 5 CHARACTERS I HAVE TO PUT ‘000’ (ZEROS) BEFORE THIS COLUMN IF IT’S LENGTH IS 4 THEN I HAVE TO PUT 4 ZEROS BEFORE IT’S AND IF IT’S LENGTH IS 5 THEN I HAVE TO PUT 3 ZEROS I HAVE TO MAKE TOTAL LENGTH EQUAL TO 8
    LIKE THIS
    SELECT POSTCODE,
    CASE LEN(POSTCODE) < 8 THEN
    WHILE LEN(POSTOCDE) < 8 LOOP
    POSTCODE = '0'+POSTCODE
    END LOOP
    END
    FROM ABCTABLE.

    please resolve this asap

    Reply
    • @Rao Mohsin

      Try this

      DECLARE @var VARCHAR(10)
      SET @var = ‘123456’
      SET @var = RIGHT(REPLICATE(‘0’, 8) + LTRIM(RTRIM(@var)), 8)

      SELECT @var

      Reply
  • Neil Macehiter
    June 18, 2013 6:06 pm

    In response to your question: “However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity”.

    Reseeding an identity can sometimes be necessary in certain replication scenarios with automatic identity range management.

    Reply
    • I reseed the database when I am merging two database into one database.
      this allows the users to keep on adding row while I merge the data into the “HOLE” I created by reseeding 100k above the current seed.

      Reply
  • or in a scenario where so many rows are deleted and inserted, that eventually you reach the maximum of the identity column’s data type, while there are substantially less records in the table than is possible to hold by the identity column. In these scenarios working through foreign key properties gets a little tiresome when reseeding though!

    Reply
  • Thank you very much…this is not the first time I have learnt from your blog…just the first time I am leaving a message. Your work is excellent and you have helped me very many times. Thank you.

    Reply
  • This is the script which i have written ,i hope it may help to some one

    Declare @id int
    Declare @count int
    Declare @name varchar(100)
    Declare @countercheck int
    Declare @temp table (id int identity(1,1) ,name varchar(200))
    Declare @sqlquery NVARCHAR(500)
    Declare @ParmDefinition NVARCHAR(500)
    Declare @column_name varchar(50)
    Declare @maxvalue int
    set @id=0
    set @count=0
    set @countercheck=0

    Insert into @temp(name)
    select name=table_name from information_schema.tables where OBJECTPROPERTY( object_id(table_name), ‘TableHasIdentity’)=1 order by table_name desc
    set @countercheck=@@rowcount

    While @count<@countercheck
    Begin
    select top 1 @name=name ,@id =id from @temp order by name desc
    select @column_name=name from sys.columns where object_id=object_id(@name) and is_identity=1
    set @sqlquery = 'select @maxvalue = max(['+@column_name+']) from '+ @name
    set @ParmDefinition=' @maxvalue int OUTPUT ,@column_name varchar(50),@name varchar(100)'
    Execute sp_executesql @sqlquery,@ParmDefinition, @maxvalue OUTPUT ,@column_name ,@name
    set @maxvalue =isnull(@maxvalue,0)
    dbcc checkident(@name,reseed ,@maxvalue)
    Delete from @temp where id=@id
    set @count=@count+1
    End

    Reply
  • Jairo A. Elias
    May 14, 2015 6:55 pm

    rakesh, thank you a lot.. It really help me.

    Reply
  • Why would I need to reseed? In my case there is a bulk insert several times a day of almost a million rows. After they are processed, the table data is deleted. The identity column is int, so there can 8500 bulk inserts be done, then the max value for int is reached. Now I can drop the table and recreate it or just reseed the identity.

    Nils

    Reply
  • Correction: it’s half a million rows, not a million.

    Reply

Leave a Reply