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:
[youtube=http://www.youtube.com/watch?v=V6yRvhwUs9Y]
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:
- DELETE, TRUNCATE and RESEED Identity
- Reseting Identity Values for All Tables
- Value of Identity Column after TRUNCATE command
- Identifying Column Data Type of uniqueidentifier without Querying System Tables
- Understanding Identity Beyond its Every Increasing Nature
- Negative Identity Seed Value and Negative Increment Interval
What would you like to see in the next SQL in Sixty Seconds video?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
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.
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
@Rao Mohsin
Try this
DECLARE @var VARCHAR(10)
SET @var = ‘123456’
SET @var = RIGHT(REPLICATE(‘0’, 8) + LTRIM(RTRIM(@var)), 8)
SELECT @var
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.
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.
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!
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.
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
rakesh, thank you a lot.. It really help me.
Thanks for your comment Jairo.
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
Correction: it’s half a million rows, not a million.