When a decimal isn’t in TSQL

I saw a posting in SimonS Blog on SQL Server Stuff that intrigued me concerning decimals and SQL Server. I wanted to leave a comment but I don’t like having to register to do so I’ll do it here.

Simon’s point is that you should beware about making assumptions as to how code will treat expressions. An expression like 10/4 is simple for us to interpret in our heads but a strongly typed language like T-SQL sometimes needs a little help when you ‘say’ integer but really mean ‘decimal’. Simon is a little unclear because he has a typo in the following statement: ‘In TSQL however you get 4’. I think he meant ‘2’.

But what he’s saying is important. I’ve fleshed out his concern in some code so we can easily see the problem:

declare @decnumb decimal(10,4);
set @decnumb = 10/4;
select 'set @decnumb = 10/4';
select @decnumb;

set @decnumb = cast ((10/4) as decimal);
select 'set @decnumb = cast ((10/4) as decimal)';
select @decnumb;

set @decnumb = (cast (10 as decimal))/(cast (4 as decimal));
select 'set @decnumb = (cast (10 as decimal))/(cast (4 as decimal))';
select @decnumb;

set @decnumb = (cast (10 as decimal))/(4);
select 'set @decnumb = (cast (10 as decimal))/(4)';
select @decnumb;

set @decnumb = (10)/(cast (4 as decimal));
select 'set @decnumb = (10)/(cast (4 as decimal))';
select @decnumb;

set @decnumb = 10/4.0;
select 'set @decnumb = 10/4.0';
select @decnumb;

set @decnumb = 10.0/4;
select 'set @decnumb = 10.0/4';
select @decnumb;

And here are the results:

set @decnumb = 10/4
2.0000

set @decnumb = cast ((10/4) as decimal)
2.0000

set @decnumb = (cast (10 as decimal))/(cast (4 as decimal))
2.5000

set @decnumb = (cast (10 as decimal))/(4)
2.5000

set @decnumb = (10)/(cast (4 as decimal))
2.5000

set @decnumb = 10/4.0
2.5000

set @decnumb = 10.0/4
2.5000

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s