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?

Click to Download Scripts

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

About these ads

7 thoughts on “SQL SERVER – RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds #051

  1. 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.

    Like

  2. 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

    Like

    • @Rao Mohsin

      Try this

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

      SELECT @var

      Like

  3. 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.

    Like

  4. 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!

    Like

  5. 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.

    Like

  6. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s