Geeks With Blogs

News
Welcome to my blog.
Here's what we've got on the menu today:

Lorin Thwaits A geek says what?

Sometimes, especially when you're working with SQL Server, it's really handy to know the scale of a decimal number.  The .NET version of decimal chews up 128 bits, i.e. 16 bytes.  The first 12 of those bytes are used for the numbers (the "mantissa").  Taken all in sequence, when converted to decimal, they offer 28 digits of numeric information.  So what about the other 4 bytes left over of the original 16?  Well, two are totally unused, one is used for the sign, and another points to where the decimal point should be in the mix.  This post focuses on that byte.  In that byte, 5 bits are used, which is precise enough to point to any position in the numbers all the way up to right before the 28th digit.  To find out exactly where the decimal is positioned (the scale of the number), you have to somehow dig into those raw bits.  Fortunately there's a neat little static method to do just that, so we don't have to resort to unsafe code to pick it apart.  Here is how to find the scale with just a little binary bit twiddling:

int scale=(System.Decimal.GetBits(myDecNum)[3]>>16)&31;

So now if you have a pile of decimal numbers and you want to create a new table in SQL to hold all of them, you can find the one with the highest scale, and define that column appropriately when you go to use CREATE TABLE or ALTER TABLE.  Here's an example in Transact SQL that builds out a decimal column with a precision of 18 and a scale of 2:

CREATE TABLE myDecimalInfo(
   decNum DECIMAL(18,2)
)

In this case, decNum has up to 18 digits in it, two of which are after the decimal point.  So this is able to represent any number between -10 quadrillion and 10 quadrillion.  (Well, .01 less than 10 quadrillion, or exactly 9,999,999,999,999,999.99.  Anyway, you get the idea.)

SQL 2005 and later also uses 16 bytes to store decimals, but it is not so wasteful.  It makes use of the unused space, effectively 3 extra bytes, allowing for up to 36 digits in a decimal number.  To allow the decimal point to be anywhere in the mix, it uses 6 bits for the scale.  Definitely a better solution.  I hate to see those bytes go wasted!

Posted on Tuesday, April 3, 2007 3:46 PM SQL | Back to top


Comments on this post: Finding the scale in decimal values in .NET

# re: Finding the scale in decimal values in .NET
Requesting Gravatar...
excellent - thanks for that snippet!
Left by Rory on May 16, 2007 5:13 AM

# re: Finding the scale in decimal values in .NET
Requesting Gravatar...
This does not help me!
Left by leah on Nov 11, 2007 10:15 AM

# re: Finding the scale in decimal values in .NET
Requesting Gravatar...
This does not help
Left by xx on Nov 11, 2007 10:16 AM

Your comment:
 (will show your gravatar)


Copyright © Lorin Thwaits | Powered by: GeeksWithBlogs.net