Cannot create default constraint with sequence object on global temporary table

I was toying with SQL sequence object and all of sudden I was surprise to find that we cannot create default constraint with sequence object on Global temporary table. Let me demonstrate my finding with an example.

Here we are creating a global temporary table with two fields

create table ##temoAddSequence

(id int,

name varchar(20))

Command(s) completed successfully.

Lets insert few records in it.

insert into ##temoAddSequence values(2,'Amrapali')

insert into ##temoAddSequence values(5,'Mahin')

lets create a sequence object

create sequence seq_IdCounter

as int

       minvalue 1

       no maxvalue

       start with 4

       increment by 1

       ;

go

Command(s) completed successfully.

Lets try to associate this sequence object to Id column of ##temoAddSequence table

alter table ##temoAddSequence

add constraint id_default_sequence

default (next value for seq_Counter) for id;

Msg 208, Level 16, State 1, Line 19

Invalid object name 'seq_Counter'.

Conclusion: We cannot create default constraint with sequence object on Global temporary table.

When you are on my head SQL, I abscond from all tasks, to shower my love to you  :)

Post Reference: Vikram Aristocratic Elfin Share

Changing Column to use Default as sequence object

Here we are going to check, how we can create default constraint, as a sequence to an existing column on a table object. For our demonstration we are creating a small table with just two column id and name. ID is the field on which we will create default constraint later in this article.

create table tempAddSequence

(id int,

name varchar(20))

Command(s) completed successfully.

Lets insert few rows into tempAddSequence table object.

insert into tempAddSequence values(2,'Amrapali')

insert into tempAddSequence values(5,'Mahin')

select * from tempAddSequence

id          name

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

2           Amrapali

5           Mahin

(2 row(s) affected)

Now our table is populated with some rows, lets create a sequence object starts with 4 and increment by 1.

create sequence seq_Counter

as int

       minvalue 1

       no maxvalue

       start with 4

       increment by 1

       ;

go

Command(s) completed successfully.

Our Sequence object is ready to consume, its now turn to create default constraint on ID column of tempAddSequence table.

alter table tempAddSequence

add constraint id_default_sequence

default (next value for seq_Counter) for id;

Command(s) completed successfully.

Our Default constraint is now in place, associated with column ID. Now we are ready to insert few more records in tempAddSequence table, here won’t insert explicit value for Id field of table.

insert into tempAddSequence(name)

select 'Aashiyana' union all

select 'Anvi'

(2 row(s) affected)

Lets check out the insertion by querying the table

select * from tempAddSequence

id          name

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

2           Amrapali

5           Mahin

4           Aashiyana

5           Anvi

(4 row(s) affected)

Conclusion: we can create default constraint with sequence object on existing table with existing data.

Go Go, its your SQL soul calling :)

  

Post Reference: Vikram Aristocratic Elfin Share

Sign are treated as Numeric in SQL Server but conversion to Numeric fails

Yesterday, when I was involved in the data cleansing activity as a part of my Data Migration project, I found a strange result, when I was converting numeric number to decimal. Let me share a piece of code

select

case

when ISNUMERIC(@NumberString) =1

       then cast(@NumberString as decimal(28,8))

else 

       cast('0.00' as decimal(28,8))

end as MyNo

Msg 8115, Level 16, State 6, Line 8

Arithmetic overflow error converting varchar to data type numeric.

Now, if I go back and check my logic, it seems all fine, the first check is made for whether the number string passed is numeric, if it turns true then cast it to decimal else set it to zero, then where this problem arose. Let’s interrogate

I am taking each part separately to understand the problem

select ISNUMERIC('-')

ISNUMERIC

-----------

1

This is bit shocking to me, lets do the check with ‘+’ sign.

select ISNUMERIC('-')

ISNUMERIC

-----------

1

Great, this means SQL Server consider sign as numeric character. Now lets come to our logic

 case

when ISNUMERIC(@NumberString) =1

       then cast(@NumberString as decimal(28,8))

else 

       cast('0.00' as decimal(28,8))

end as MyNo

so here it says, if @NumberString is 1 then cast the number to decimal, so in our case our input parameter to this check is numeric i.e. ‘-‘, so now it should enter true part of the case i.e.

then cast(@NumberString as decimal(28,8))

i.e select cast('-' as decimal(28,10))

Msg 8115, Level 16, State 6, Line 17

Arithmetic overflow error converting varchar to data type numeric.

Conclusion: Here it throws the error, which means the ISNUMERIC and CAST function are working in Sync.

Get your tea cup ready, lot more code to do :)

Post Reference: Vikram Aristocratic Elfin Share