About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Showing posts with label Identity Column with DBCC CHECKIDENT. Show all posts
Showing posts with label Identity Column with DBCC CHECKIDENT. Show all posts

Wednesday, June 3, 2015

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

Saturday, November 1, 2014

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

Friday, October 31, 2014

How to have identity property set for multiple column in a table


Lets try out with a small demo, here we are creating table with three column, out of which two column has identity property set.

create table dupIdentity
(col1 int identity(1,1),
col2 int identity(1,1),
col3 varchar(10))

Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'dupIdentity'. Only one identity column per table is allowed.

Identity property is not allowing me to have on multiple columns.

So what’s the alternative, apparently SQL Server 2012 feature SEQUENCE Object? Lets try out. First we will create table without any identity property column

create table dupIdentity
(col1 int,
col2 int,
col3 varchar(10))
Command(s) completed successfully.

Now lets create two sequence object seq1 and seq1, one which will starts from 1 and another will starts from 10 and incremented by 1 after every insert.

create sequence seq1 as int
start with 1
increment by 1

create sequence seq2 as int
start with 10
increment by 1
Command(s) completed successfully.

Now since we have sequence object in place, lets apply it to our table

insert into dupIdentity values ( next value for seq1, next value for seq2, 'Pragyan')
insert into dupIdentity values ( next value for seq1, next value for seq2, 'Lopamudra')
(2 row(s) affected)

Lets query the table to find the data inserted.

select * from dupIdentity
col1        col2        col3
----------- ----------- ----------
1           10          Pragyan
2           11          Lopamudra

(2 row(s) affected)

That’s it J

Conclusion: we can stimulate multiple identity property through Sequence Object of SQL Server 2012.

Nights are bore to tears when I am far from you, my SQL Server  :)


Post Reference: Vikram Aristocratic Elfin Share

Tuesday, April 22, 2014

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?