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

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

Stimulating DEADLOCK in SQL Server

Here in this article we are trying to stimulate Deadlock situation, we are trying to create two table table1 and table2 for the same purpose

create table table1

(id int identity(1,1),

col2 int)

create table table2

(id int identity(1,1),

col2 int)

Command(s) completed successfully.

Now lets insert few records in both the table.

insert into table1

select 10 union all

select 20 union all

select 30

insert into table2

select 100 union all

select 200 union all

select 300

No w open a session by opening a new query window in SSMS, I will call it session1 and try to execute the below code, here we opened a transaction then updating table1 col2 where id =1

begin tran

       update table1

       set col2 = col2 + 10

       where id = 2

Now open another query window (second session) in SSMS and execute the below statement, here we are trying to update both the table the second table will wait for first transaction to unlock table1 for its update. And it goes on execution for infinite time.

begin tran

       update table2

       set col2 = col2 + 100

       where id = 2

       update table1

       set col2=col2 + 10

       where id = 2

commit tran

Now again come back to session1 window and execute the below code

update table2

set col2 = col2 + 100

where id = 2

now as you run this above statement on session1, SQL Server sense deadlock since the above code wait for table2 to get unlocked by session2 and same way session2 waits for session1 for table1 to unlock and DEADLOCK occurs, in this case SQL server chosen session1 as victim and abort session1 with a message

Msg 1205, Level 13, State 45, Line 7

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

And session 2 will complete automatically.

There are no nights for SQL Developers, if so please give me address  J

Post Reference: Vikram Aristocratic Elfin Share

Truncate can be Rollback in same session

When you are surrounding TRUNCATE in transaction block you can rollback Truncated table in the current session.

If you close the session where Truncate is surrounded by transaction block, you cannot rollback the table unlike DELETE.

Here we have a table with only one record

select * from temp2

id          c_address  zip

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

1           delhi      326595

(1 row(s) affected)

Below we are opening a transaction block and truncating the table later we are doing rollback, to check the intermediate result we have put select statement in between.

Lets execute the transaction block and see the result of rollback on truncate.                                                                                                 

begin transaction   

       select * from temp2

       truncate table temp2

       select * from temp2

rollback tran

      

id          c_address  zip

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

1           delhi      326595

(1 row(s) affected)

id          c_address  zip

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

(0 row(s) affected)

So here we saw before truncate we had one record and after truncate we had no records.

Now the next statement which we fired is rollback, here we need to check whether truncate statement gets rollback or not.

Lets fire the select query to see what affect it made on truncate after rollback

select * from temp2

id          c_address  zip

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

1           delhi      326595

Ah! Truncate has no affect when it is followed by rollback in transaction block like Delete Command.

Conclusion: Truncated record can be rollback in the same session.

Few minute spent with you my SQL Server, rejuvenate for hours  :)

Post Reference: Vikram Aristocratic Elfin Share