-- note I am reseeding a non empty table for demo purposes only!
-- from the Books Online page for DBCC CHECKIDENT;
-- If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:

-- If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.

-- If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

USE [msdb];
GO

CREATE TABLE [dbo].[ident_demo_1] (
id tinyint IDENTITY (1,1),
demo_1 VARCHAR(20));
GO

SET NOCOUNT ON;
GO

-- use a tinyint and max out table
-- you could do this with any of the ints, but let's save ourself some time
-- and use the TINYINT!
declare @i int = 1;
while @i <= 255
begin
INSERT INTO [dbo].[ident_demo_1]
values
( 'ident_demo_' + cast (@i as varchar (3)))
set @i = @i + 1;
end;

--DBCC CHECKINDENT will the current identity value and the current column value
DBCC CHECKIDENT ('dbo.ident_demo_1');

--use the "DBCC CHECKINDENT" to reset the identity down to 10
DBCC CHECKIDENT ('dbo.ident_demo_1', RESEED, 3);

--check again, this time using the "NORESEED"
DBCC CHECKIDENT ('dbo.ident_demo_1', NORESEED);

--run these commands below together; you'll see the "current identity column" change from 3 to 255
--this is because if the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.
--so you have to explicitly omit the altering of the values using NORESEED

DBCC CHECKIDENT ('dbo.ident_demo_1');

DBCC CHECKIDENT ('dbo.ident_demo_1');

--in order to keep the identity value the same, or if you just want to check the value, use the NORESEED argument

drop table [dbo].[ident_demo_1]

-- 2

USE [msdb];
GO

CREATE TABLE [dbo].[ident_demo_2] (
id tinyint IDENTITY (1,1),
demo_1 VARCHAR(20));
GO

SET NOCOUNT ON;
GO

declare @i int = 1;
while @i <= 25
begin
INSERT INTO [dbo].[ident_demo_2]
values
( 'ident_demo_' + cast (@i as varchar (3)))
set @i = @i + 1;
end;

--using noreseed argument, check the identity
DBCC CHECKIDENT ('dbo.ident_demo_2', NORESEED);

--delete all the data!
delete from [dbo].[ident_demo_2]
where id = 255

--check the identity again; the column value is now null, which is not 0 but rather "unknown"
--note that not specifying the argument NORESEED does not make any difference here as the current column value is "null" which is neither greater nor less than 255
DBCC CHECKIDENT ('dbo.ident_demo_2');

DBCC CHECKIDENT ('dbo.ident_demo_2', NORESEED);

--inserting into the table wil lfail, because the max ident number is the max value which can be inserted into a tinyint
INSERT INTO [dbo].[ident_demo_2] VALUES ('epic fail');
GO

--if we reseed the number back down to a very low number, we will be able to reinsert
DBCC CHECKIDENT ('dbo.ident_demo_2', RESEED, 3);

--cehck the identity again; yes, noreseed is not required (try it yourself) but best get into good habits now..
DBCC CHECKIDENT ('dbo.ident_demo_2', NORESEED);

INSERT INTO [dbo].[ident_demo_2] VALUES ('epic win');
GO 10

--current columns value will now take the max current value of the identity column
--so when emptying a table using a delete statement-
--until we explicitly reseed the identity value, it will retain the maximum value
--the current column value will be "null" and will stay like this until we reinsert
DBCC CHECKIDENT ('dbo.ident_demo_2');

drop table [dbo].[ident_demo_2]

--3

USE [msdb];
GO

CREATE TABLE [dbo].[ident_demo_3] (
id tinyint IDENTITY (5,1),
demo_1 VARCHAR(20));
GO

SET NOCOUNT ON;
GO

declare @i int = 1;
while @i <= 250
begin
INSERT INTO [dbo].[ident_demo_3]
values
( 'ident_demo_' + cast (@i as varchar (3)))
set @i = @i + 1;
end;

--maxxed out identity value and current column value
DBCC CHECKIDENT ('dbo.ident_demo_3', NORESEED);

--delete data using the "truncate" command
truncate table [dbo].[ident_demo_3]

--this time, both the identity value and the current column value are set to NULL
DBCC CHECKIDENT ('dbo.ident_demo_3', NORESEED);

--can we can go ahead and insert directly into the table without having to reseed..?
INSERT INTO [dbo].[ident_demo_3] VALUES ('epic win');
GO 10

--yes we can! plus, we can see that the values are reset back to the original values specified when creating the table
DBCC CHECKIDENT ('dbo.ident_demo_3', NORESEED);

--you can test this yourself by dropping and recreating this table with the ident starting on a number greater than 1
drop table [dbo].[ident_demo_3]