IDENT_SEED function returns the value of the seed you specified when creating the identitiy column. The value returned by this function does not change even if you change the current identity value with the DBCC CHECKIDENT command.
Seed value is the starting number of the identitiy column.
Let’s make an example for a better understanding of IDENT_SEED Function.
First, we create a table with the help of the following script and add a few records into this table.
CREATE TABLE [dbo].[MyTable]
([ID] INT IDENTITY(3,2),
[Name] VARCHAR(100) NULL
VALUES ('Hakan GURBASLAR'),('Nurullah CAKIR'),('Faruk ERDEM')
SELECT * FROM [dbo].[MyTable]
The seed value of identity column created with the above script is 3. Lets query this value using IDENT_SEED.
Change the current identity value with DBCC CHECKIDENT. For more information, please read the article “DBCC CHECKIDENT in SQL Server (TSQL)“.
DBCC CHECKIDENT ( '[dbo].[MyTable]', RESEED,10 )
When we check the seed value of identity column in the table again, the result will be returned to 3 again.