SQL identity reset & ignore

1. Create table with identity column

 

CREATE TABLE tb
(
  id INT IDENTITY ,
  name VARCHAR(100)
 );

 

insert into tb values ('aa')
GO 10

 

 

2. Insert into table tb (-1 ,'bb') - ignore idenity 

 

--set identity_insert tablename on

 

set identity_insert tb on
go
insert into tb(id,name) values (-1,'bb')

set identity_insert tb off
go

 

3.Get current identity number

 

DBCC CHECKIDENT ( 'dbo.tablename' )

 

4. Reset identity current number

 

-- DBCC CHECKIDENT ('tablename', RESEED,17)

 

DBCC CHECKIDENT ('tb', RESEED,17)

 

set tb identity number now =  17.