Adbrite

Saturday, October 16, 2010

Some doubt about integer (INT) data type columns

Some doubt about integer (INT) data type columns

Recently I have some doubt about integer (INT) data type column. Let me explain this doubtful problem, if you insert or update blank records to integer column it will update as 0.But if it is decimal data type situation bit different. Check following example,

use tempdb
go
drop table T1
go
create table T1(col1 int primary key
,col2 tinyint
,col3 smallint
,col4 int
,col5 bigint
,col6 decimal(9,1)
,col7 varchar(5));
go
select * from T1
go
begin tran
insert into T1 select 1,11,111,1111,11111,111111,'R1'
insert into T1 select '','','','','',-1,''
commit tran
go
select * from T1
go

If you try following example then you may come up with this error,

begin tran
insert into T1 select 1,11,111,1111,11111,111111,'R1'
insert into T1 select '','','','','','','' --error
insert into T1 select ' ',' ',' ',' ',' ',' ',' ' --error
commit tran

Result,
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Finally I come up with some conclusion but I have some doubt when we dealing with decimal data type. MSSQL consider blank value as 0 if you use only integer data type but if you dealing with string data type(varchar) it will remain as same. I will try to find more information about decimal data type and update it in future
post.

6 comments:

Susanthab said...

Hi Tharindu,
This is interesting. It seems like SQL has another value for empty. Empty can not be considered as NULL value. See the below code block.

declare @i varchar(1)
declare @i1 varchar(1)
Set @i = ''
set @i1 = null
SELECT len(@i) QuoteLen
SELECT len(@i1) Nulllen

Both variables are varchar(1). First SELECT returns 0 and second returns NULL. Now the question is how SQL Server store empty value? Is it better to use empty value than NULL value?

Tharindu Dhaneenja said...

Hi Susantha,
Thank you very much. i didn't notice how it's dealing with NULL. you are pointed out valuable information.

Preethiviraj Kulasingham said...

SQL Server does not consider empty string as 0. Rather it CONVERTS the value into the data type it needed.
The issue is related to implicit conversion from varchar into decimal

Tharindu Dhaneenja said...

Hi Preethi,
Thanks you very much for the comments. but my problem is, it only works for INT datatype.implicit conversion from varchar into decimal wont work.pls check this,(hope i didn't done any mistakes :))

--for decimal
declare @decimal decimal(9,0)
select @decimal=''
select @decimal
--result ->
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
--

--for int
declare @int int;
select @int=''
select @int
--result -> "0"

Anonymous said...

In response to Susanthab...

An empty string is VERY different to a NULL string. NULL basically means undefined whereas an empty string IS very much defined. Don't confuse NULLs with empty strings!

Try this query to prove my point:

--NULL is undefined so any operation including a NULL is also undefined
SELECT '' + NULL
SELECT 0 / NULL
--etc...

Anonymous said...

Just checked MSDN and this is expected behaviour:

"SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal."

http://msdn.microsoft.com/en-us/library/ms187928.aspx