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.