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.
.jpg){kind=small}
_1269_507.jpg){kind=small}
_1256_502.jpg){kind=small}
6 comments:
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?
Hi Susantha,
Thank you very much. i didn't notice how it's dealing with NULL. you are pointed out valuable information.
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
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"
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...
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
Post a Comment