Welcome to another episode of SQL in Sixty Seconds! In today’s video, we will explore the concept of reseeding the identity value of a table column in SQL Server.

First, let’s understand what an identity column is. An identity column is a column in a table that automatically generates a unique value for each new row inserted. This value is typically an increasing or decreasing integer based on the interval specified in its property.

There are scenarios where developers may need to reseed the identity value of a table column. For example, if some rows are deleted from a table and the developer wants to reset the identity value to a lower value, this feature can be very useful.

In the video, we demonstrate how to reseed the identity value to any desired value. You can set the identity value to a value greater than the current column value or even to a lower value. This flexibility allows developers to customize the identity value according to their specific requirements.

Let’s take a look at the script used in the video:

USE tempdb

-- 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;

-- Returns current Identity Value
DBCC CHECKIDENT ('TestTable', NORESEED);

-- Resets the current Identity value to a specified 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;

By using the DBCC CHECKIDENT command, we can check the current identity value and reset it to a desired value. In the script above, we first insert some data into the TestTable and then reseed the identity value to 11. Subsequently, we insert more data into the table and retrieve the results to verify the changes.

It’s important to note that reseeding the identity value should be done with caution, as it can potentially lead to data inconsistencies if not handled properly. It’s recommended to thoroughly understand the implications before making any changes to the identity value.

That’s all for today’s episode of SQL in Sixty Seconds. We hope you found this information helpful. Stay tuned for more exciting SQL Server tips and tricks in our upcoming videos!

What would you like to see in the next SQL in Sixty Seconds video? Let us know in the comments below!