First insertion will always takes the seed value, all other insertion following first will take seed/reseed value and add increment factor to form the identity value

Lets demonstrate it with the help of a temporary table, here we are creating a table with identity column and set the seed value to 1 and increment factor to 1

create table #tempReseed

(id int identity(1,1),

col1 varchar(10));

Command(s) completed successfully.

Now before we execute any insert statement on the table we are Reseed the Identity value to 15

dbcc CHECKIDENT('#tempReseed',reseed,15)

Checking identity information: current identity value 'NULL'.

DBCC execution completed.

Now lets insert a records and check what value will get inserted to id column.

insert into #tempReseed(col1) values('ABC')

(1 row(s) affected)

Querying the table after insertion.

select * from #tempReseed

id          col1

----------- ----------

15          ABC

Here we found the id is set with reseed value i.e. 15

Lets again reseed the identity value, this time to 25

dbcc CHECKIDENT('#tempReseed',reseed,25)

Checking identity information: current identity value '15'.

DBCC execution completed.

Lets fire an insertion after reseeding identity value to 25

insert into #tempReseed(col1) values('DEF')

(1 row(s) affected)

Lets query the table to find what gets inserted this time.

select * from #tempReseed 

id          col1

----------- ----------

15          ABC

26          DEF

Cool!!! This time Reseed value was added with the incremented factor i.e. 1 an new value formed was 25+1 =26 which gets inserted.

Conclusion: First insertion will always takes the seed value, all other insertion following first will take seed/reseed value and add increment factor to form the identity value.

Code and enjoy … very sync :)

Post Reference: Vikram Aristocratic Elfin Share

Workaround to have Default value in Identity column

If you try to define default value for identity column, it will result in error saying “Not Allowed. Lets try to stimulate the problem.

Here we are taking a table with id column

create table Tab_DefaultIdentity

(id int identity(1,1) default 1000,

name varchar(10)

)

Msg 1754, Level 16, State 0, Line 47

Defaults cannot be created on columns with an IDENTITY attribute. Table 'Tab_DefaultIdentity', column 'id'.

Msg 1750, Level 16, State 0, Line 47

Could not create constraint or index. See previous errors.

So what is the workaround for the same, lets check out with Sequence feature of SQL Server 2012

Workaround

Here we creating a new table without Default constraint set at id field.

create table Tab_DefaultIdentity

(id int default 1000,

name varchar(10)

)

Command(s) completed successfully.

Now let’s create a sequence with incremental factor of 1 then use this sequence to populate the table data

create sequence seq1 as int

start with 1

increment by 1

Command(s) completed successfully.

Now lets insert few rows in the table

insert into Tab_DefaultIdentity(name) values('Rinny')

insert into Tab_DefaultIdentity(id,name) values(next value for seq1,'Binny')

lets query the table data

select * from Tab_DefaultIdentity

id          name

----------- ----------

1000        Rinny

1           Binny

So here we can we can see, by using Sequence feature one can add default value as well as can get the incremental identity for a column.

No SQL no Night :)

Post Reference: Vikram Aristocratic Elfin Share

Workaround to have null value in Identity column

When we are trying to explicitly enter null values it to an identity column we get an error message saying not allowed. Lets try to stimulate the problem.

Here we are taking a table with id column

create table Tab_nullableIdentity

(id int identity(1,1),

name varchar(10)

)

Command(s) completed successfully.

Let now insert null value, but to explicitly insert value we need to set IDENTITY_INSERT property to ON

set identity_insert Tab_nullableIdentity  on

go

insert into Tab_nullableIdentity(id,name) values(null,'Rinny')

Msg 339, Level 16, State 1, Line 31

NULL are not allowed as explicit identity values.

Workaround

Lets create a new table without identity property set on id column

create table Tab_nullableIdentityWithSeQ

(id int ,

name varchar(10)

)

Command(s) completed successfully.

Now let’s create a sequence with incremental factor of 1 then use this sequence to populate the table data

create sequence seq1 as int

start with 1

increment by 1

Command(s) completed successfully.

Now lets insert few rows in the table

insert into Tab_nullableIdentityWithSeQ(id,name) values(null,'Rinny')

insert into Tab_nullableIdentityWithSeQ(id,name) values(next value for seq1,'Binny')

lets query the table data

select * from Tab_nullableIdentityWithSeQ

id          name

----------- ----------

NULL        Rinny

1           Binny

So here we can we are able to generate sequence auto generated number as well as we are able to insert null value to  the id column.

That’s it J

Conclusion: we can Stimulating null value insert identity property column through Sequence Object of SQL Server 2012.

Have your tea and sit for code :)

 

Post Reference: Vikram Aristocratic Elfin Share

Reseeding IDENTITY Seed Value of Column with PK; Beware

Beware while reseed with DBCC Checkident('Tab_Name',Reseed,1) because, the moment identity value reaches to the values which is present in ur identity column, it won’t allow you to enter record due to duplicity, since u said ur id column is PK and throw error...

For explanation we are taking creating a table, this table has no Identity property set for ID field

--TABLE WITHOUT iDENTTITY

create table tab_test

(id int primary key,

name varchar(10))

Command(s) completed successfully.

Now lets insert a record with Id value 2

insert into tab_test values(2,'vikram')

(1 row(s) affected)

Lets set the identity property if ID column with the help of designer.

Go the Table Designer à Select ID column à Go to Property à Expand Identity specification àset YES to isIdentity àset seed and increment value to 1.

Now since our identity value is set lets find the current identity value

select ident_current('tab_test') as CurrentIdentity

CurrentIdentity

---------------------------------------

2

So now if we try to insert record, it will take 3 as ID value. Lets insert a record in the table

insert into tab_test values('keshari') --IT Will take identity value as 3

select * from tab_test

id          name

----------- ----------

2           vikram

3           keshari

Now lets reseed the identity value to 0

dbcc checkident('tab_test',reseed,0)

Checking current identity value

select ident_current('tab_test') as CurrentIdentity

CurrentIdentity

---------------------------------------

0

Now lets insert record in the table and see the ID field value

insert into tab_test values('mahapatra') --It will take identity values as 1

select * from tab_test

id          name

----------- ----------

1           mahapatra

2           vikram

3           keshari

Now here we saw the new id value was taken as 1, lets find what is the current and next identity value

select ident_current('tab_test') as CurrentIdentity

CurrentIdentity

---------------------------------------

1

Now we can see the current value is 1, so the next identity value will be 2, but record with 2 as ID value is already exist so will it take 2 as next identity value?

insert into tab_test values('Sibani') --now since the identity current value is 2 and Id column with 2 value is present, it wont allow

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__tab_test__3213E83FE45A5CA7'. Cannot insert duplicate key in object 'dbo.tab_test'. The duplicate key value is (2).

The statement has been terminated.

Conclusion: While Reseed value for identity column having primary Key defined on it, be aware else you will get error in some point of time if the next identity value exist in the table records.

Ask yourself, are you having fun with SQL Server?