IDENTITY -misconceptions

1. A column defined with IDENTITY property always maintains unique values

   That is not true. A column with IDENTITY property maintains unique values only when UNIQUE or PRIMARY KEY constraints are included.

Check the below examples,



   Use tempdb
 

Create table #test(c1 int identity(1,1))

go
insert into #test default values 
go 10

go
DBCC CHECKIDENT ('#test', RESEED, 1);
go
insert into #test default values 
go 2

select * from #test

The table will have duplicate values inserted.  Now let us try with UNIQUE constraint.

Create table #test(c1 int UNIQUE identity(1,1))

go
insert into #test default values 
go 10

go
DBCC CHECKIDENT ('#test', RESEED, 1);
go
insert into #test default values --- This statement throws error.
go 2

select * from #test

2. The values generated by IDENTITY property are always sequential
     
    No, this statement is invalid in certain scenarios.
 When the row insert happens in a transaction and rolled back , then the generated identity values are not retained

.Create table #test_tran(c1 int UNIQUE identity(1,1))

go
insert into #test_tran default values
go 2

go
begin tran t1

insert into #test_tran default values
insert into #test_tran default values

rollback tran t1

go

insert into #test_tran default values

select * from #test_tran


The values 3 and 4 will be missing.