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?

Allowing only two null values for a column in SQL table

My friend Subhasis Swain asked me yesterday night, Vikram, is it possible to restrict table column to have only two null values.

Though I don’t find any practical carrying out for the same, but yes we can create our logic to hold only two null values in a column.

 I said Yes Subhasis, but alas I invited sleepless night agreeing to write code for the same on SQL Server 2014, which was his demand to download and install at night 2:00 AM

Along with it, there was one more condition, If I succeed then I will give cold coffee treat to him and Shankhodeep Karmakar, one of my very good friend at CCD that to 2/3 :-O

Let’s see how we can do this, here for the demo purpose I am creating a table with two column id and name.

create table TAB_TwoNullOnly

(id int,

name varchar(20))

go

Now I am trying to write a logic using trigger to restrict two null values for name field.

create trigger TRG_AllowTwoNullValue on TAB_TwoNullOnly

for insert,update

as

       declare @row_count int

       select @row_count = COUNT(*) from TAB_TwoNullOnly where name is null

       if @row_count > 2

       begin

              rollback

              print 'you cannot have more then two null values'

       end

       print 'After trigger fired.'

go

Here this trigger is written for insert and update DML statement so when a user fire insert or update table, the trigger will come into picture.

Inside the trigger we are checking how many null values are present in table including the latest insert and update, and if it is  greater then 2 then we do rollback our transaction which insert null in name field else allow the insert or update operation to perform.

Now since trigger is in place, let’s insert data in table.

insert into TAB_TwoNullOnly values(1,'Shibani')

insert into TAB_TwoNullOnly values(2,null)

insert into TAB_TwoNullOnly values(3,null)

Now we have inserted two null values in name field

select * from TAB_TwoNullOnly

id          name

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

1           Shibani

2           NULL

3           NULL

Now if we try to insert one more record with null value in Name column, it should throw error. Lets try out

insert into TAB_TwoNullOnly values(4,null)

you cannot have more then two null values

After trigger fired.

Msg 3609, Level 16, State 1, Line 6

The transaction ended in the trigger. The batch has been aborted.

Here we saw that it is not allowing me to enter null value because already there are two null value present in the Name column.

Lets now update the name field with null where Id =1, again it should not allow me to update since updating value will make the count of null value to 3, which is not comply with the problem statement.

update TAB_TwoNullOnly

set name = null

where id=1

you cannot have more then two null values

After trigger fired.

Msg 3609, Level 16, State 1, Line 8

The transaction ended in the trigger. The batch has been aborted.

So here saw with the help of trigger we can achieve such kind of constraint on table values.

Now today I m puting in order for 2/3 coffee at CCD with Subhasis and Sankhodeep. :D

This dawn my code was so loud on bed, it woke up my roommate. I said him to rollover and go back to sleep ;) ;)

Scope of GOTO with GO

The GOTO statement is used to break the normal flow of execution to a particular label specified with GOTO statement. We will see this with examples, but the question of matter is whether GOTO statement goes/switch beyond the block defined with GO statement.  

Lets checkout with an example, here we are checking a condition in “if statement”, if the condition evaluates to true then we are instructing normal flow to break and go to a particular point i.e. VKM_POINT, but in this example we define the label VKM_POINT outside the GO block, now let’s see whether the GOTO has scope beyond the block where it is used.      

declare @tempVar int

set @tempVar = 10

if @tempVar =10

      goto VKM_POINT

go

VKM_POINT:

      print 'Honey I was waiting for you'

Msg 133, Level 15, State 1, Line 5

A GOTO statement references the label 'VKM_POINT' but the label has not been declared.

So with the output we saw that GOTO is unable to switch the flow beyond the scope of batch. Lets modify the same example to have label point (VKM_POINT) inside the batch.

declare @tempVar int

set @tempVar = 10

if @tempVar =10

      goto VKM_POINT

VKM_POINT:

      print 'Honey I was waiting for you'

go

Honey I was waiting for you

Here with the output we can see that GOTO works properly when it has to jump to point which is inside the block where GOTO is used.

Conclusion: GOTO has scope only within a block

When programmer carry out rules, the ordinary human race slight.. J

Post Reference: Vikram Aristocratic Elfin Share

Variable Scope and CONTEXT_INFO

In SQL Server the variable which we declare in the block has scope to that block only, when you try to access that variable outside that scope, you won’t be able to access it. Let see with an example

Here we are creating two blocks, in first block we are declaring a variable @tempInt of INT type and initializing it with 5. And in second block, we are just trying to print the value of that variable.

declare @tempInt int

set @tempInt = 5

print @tempInt

go --Here we finished our first block

print @tempInt

go --Here we finished our Second block

5

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@tempInt".

From the output we can see the first Print which is present in first block where the variable was declared and initialized display its value but which we try to display the value of variable in second block it fires error saying, declare variable “@tempInt.

Conclusion: The scope of variable exists only to the block where it gets declared.

This problem can be solved using CONTEXT_INFO, context_info is a variable specific to a session of type varbinary(128), to access the value of context_info we have a function context_info(). You can also set your own value in context_info variable.

Session variable storing Varchar value

declare @strSource varbinary(50)

select @strSource = convert(varbinary(50),'My testing')

set context_info @strSource

declare @strDesti varchar(50)

select @strDesti =  cast(cast(context_info() as binary(50)) as varchar)

print @strDesti       

My testing

Session variable storing int value

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),387)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print @strDestiInt

387

Session Variable scope

declare @strDesti varchar(50)

select @strDesti =  cast(cast(context_info() as binary(50)) as varchar)

print @strDesti       

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),300)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print cast(@strDestiInt as varchar) + ' from Ist Batch'

go      --Here we finished first block

print cast(cast(cast(context_info() as binary(4)) as int) as varchar) + ' from IInd Batch'

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),400)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print cast(@strDestiInt as varchar) + ' New value from IIst Batch'

go    --Here we finished second block

print cast(cast(cast(context_info() as binary(4)) as int) as varchar) + ' from IIIrd Batch'

go    --Here we finished third block

300 from Ist Batch

300 from IInd Batch

400 New value from IIst Batch

400 from IIIrd Batch

So from the output we can see that the first value which gets assigned to context_info variable was 300 and it is also accessable in second block too as we saw in second line of output then we modified the value of context_info variable in third block to 400, and again in third block we can access the modified value of context_info.

Conclusion: CONTEXT_INFO retains its value across blocks and u can also set new value to context info in any block.

When programmer do code, nothing annoys them so much.. J

Post Reference: Vikram Aristocratic Elfin Share