Money vs DECIMAL data types in SQL Server

Last week, one of our developers asked us in the DBA group what our standard was for currency columns. My first thought was DECIMAL(19,4), but I couldn’t give a reason why it was standard. I knew MONEY was essentially the same definition, but had heard that it was dangerous to use. Suspecting a rounding issue, I began with a simple test.

DECLARE
    @dOne DECIMAL(19,4),
    @dThree DECIMAL(19,4),
    @mOne MONEY,
    @mThree MONEY
SELECT
    @dOne = 1,
    @dThree = 3,
    @mOne = 1,
    @mThree = 3
SELECT
    @dOne / @dThree * @dThree AS DecimalResult,
    @mOne / @mThree * @mThree AS MoneyResult

--------------------------------------------------------------------
DecimalResult  MoneyResult
1.000000       0.9999

Interesting! Why did the MONEY type get truncated? So another quick query gave me the output precision and scale.

SELECT
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'basetype') AS DecimalBasetype,
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'precision') AS DecimalPrecision,
    SQL_VARIANT_PROPERTY(@dOne / @dThree * @dThree, 'scale') AS DecimalScale,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'basetype') AS MoneyBasetype,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'precision') AS MoneyPrecision,
    SQL_VARIANT_PROPERTY(@mOne / @mThree * @mThree, 'scale') AS MoneyScale

-------------------------------------------------------------------------------------------
DecimalBasetype  DecimalPrecision  DecimalScale  MoneyBasetype  MoneyPrecision  MoneyScale
Decimal          38                6             money          19              4

The decimal is now a DECIMAL(38,6) and the MONEY is still a DECIMAL(19,4). Hmmm. Why did one change and the other didn’t change.

For an answer, I started digging into MSDN for precision & scale of data types. I came across this article. It had a fantastic table that described it.

Operation Result precision Result scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 – e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 – s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

So let’s expand on the first calculation, @dOne / @dThree * @dThree, which is actually two calculations, division and then multiplication.

e1 / e2 = DECIMAL(19,4) / DECIMAL(19,4)

precision = p1 – s1 + s2 + max(6, s1 + p2 + 1) = 19 – 4 + 4 + max(6, 4 + 19+ 1) = 43

scale = max(6, s1 + p2 + 1) = max(6, 4+19+1) = 24

So that leaves us with a DECIMAL (43,24) for the division portion, which is not a legal data type. So SQL subtracts 5 from the precision to get the 43 down to a 38, and a matching 5 from the scale, which leaves us with a DECIMAL(38,19).

e1 * e2 = DECIMAL(38,19) * DECIMAL(19,4)

precision = p1 + p2 + 1 = 38 + 19 + 1 = 58

scale = s1 + s2 = 19 + 4 = 23

This leaves us with a DECIMAL(58,23) which is also an illegal data type. So, again, SQL subtracts 20 from the precision to get the 58 down to a 38, and a matching 20 from the scale, which leaves us with a DECIMAL(38,3). But wait, there is an undocumented rule that if the scale is being lowered by this process, it is never lowered below 6. So we actually end up with a DECIMAL(38,6).

With the second calculation, @mOne / @mThree * @mThree, all of the results are cast back to a MONEY or DECIMAL(19,4) which truncates data and is why we lose the accuracy of our scale.

What did we learn, … Don’t use the MONEY datatype in SQL Server.

Puzzle Solved

I have been really getting into RSS feeds lately and have subscribed to alot of ones that interest me.   I personally have been using the Google Reader on the iGoogle homepage.  It’s a neat way to keep tabs on my varied readings.  One that caught my eye was a challenge by Pinal Dave.   Pinal challenged his readers to solve a puzzle that would return the size of each index for a speficied table.

He started with a simple sp_spaceused [tableName] command that returned the index_size of the table and a selection from sys.indexes that returned a list of all of the indexes on said table.  But it was up to his readers to figure out how to get the index_size of each index, so that the sum of all of them would add up to teh result from sp_spaceused.  Read the article to follow along.  My answer is the 7th down (mine is simpler and suprising different from the other answers).  Pinal will post the solution with due credit on his blog.  Hopefully, I did well, seeing that he has almost 11 million readers.

Here is a quick overview of the puzzle:

My answer was:

DECLARE
@objname nvarchar(776),
@id int

SELECT
@objname = 'TableToExamine',
@id =  object_id(@objname)

SELECT
i.*,
CASE
WHEN ps.usedpages  > ps.pages  THEN (ps.usedpages - ps.pages)
ELSE 0
END  * 8 indexsize
FROM sys.indexes i
INNER JOIN (
SELECT
OBJECT_ID,
index_id,
SUM (used_page_count)  usedpages,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)pages
FROM  sys.dm_db_partition_stats
WHERE object_id  = @id
GROUP BY object_id,  index_id
) ps on i.index_id = ps.index_id
WHERE  i.object_id  = @id

Well, that wasn’t my exact answer.  I had a variable declaration that I really didn’t need, but hey, I did it in like 5 mins.  So this version is a little cleaner. 🙂

ENJOY!  And in the spirit of fun, See if you can do it better!

Nullability and Default Constraint Behavior

When adding a new column with a default constraint to an existing table, keep in mind what you want your existing records to contain.  If you add the new column and set it to be allow nulls, then all of the existing records will contain a NULL, and any inserted records will have the default value.  However, if you set new column’s nullability to be NOT NULL, then the existing records will get back-filled with the default value.

Here is a quick sql snippet to show the behavior:


CREATE TABLE Test (Id INT  IDENTITY(1,1) NOT NULL, Name  VARCHAR(10) NULL)
INSERT Test (Name) VALUES ('you')
INSERT Test (Name) VALUES ('me')
SELECT * FROM Test

ALTER  TABLE Test ADD  FlagNull BIT NULL CONSTRAINT  DF_Test_FlagNull DEFAULT ((0))
ALTER TABLE Test  ADD FlagNotNull BIT NOT NULL CONSTRAINT  DF_Test_FlagNotNull DEFAULT ((0))
SELECT * FROM Test

INSERT  INTO Test (Name) VALUES ('us')
SELECT * FROM  Test
Id  Name
1   you
2   me

Id  Name  FlagNull  FlagNotNull
1   you   NULL      0
2   me    NULL      0

Id  Name  FlagNull  FlagNotNull
1   you   NULL      0
2   me    NULL      0
3   us    0         0

As shown the FlagNull column has a NULL for the first 2 records as opposed to the FlagNotNull column has the default value for the same records.

Playing in the leaves at the Nature Center

Today, I got to go play in the fall leaves with my family out at the Nature Center.  What a great day! We are still having awesome weather and the leaves are perfect right now.  We even got to actually watch them fall from the trees.  The kids ate it up!  It was Suzanne’s idea to go take them out.  In the past years, we never needed to go somewhere to experience leaves.  In our old house, we could just look out the windows and see all of the 50-yr old trees in our neighborhood in full color.  But our new house is in an area of newer homes, so there’s not any established trees to enjoy.  That’s one of the main things that I really miss about the old house.  Oh well, my kids are still getting to experience the fantastic fall leaves of the Ozarks.  Thanks to my wife’s resourcefulness.

Golfing with my son in November

Yesterday, my 5-yr old son, Logan, and my step-father-in-law, Jerry, and I went to play golf … in November!  Being in the hign-60’s, it was just TOO nice of a day to not take advantage of it.  Logan really has a  surprisingly good tee-shot.  He made it on the green with one tee-shot and chipped one in from 15-ft away in the rough!  Pretty stinking amazing for a kid that doesn’t get to play too much.  We were playing out at Rivercut.  I think it’s a well kept secret that Rivercut has a short 4-hole, chip-n-putt course next to the main 18-hole course.  Most golfers can see the short course from the 3rd hole on the main course, but don’t realize what it is.  All you need to play is a wedge and a putter, but having a lob-wedge will help the ball stop on the hard greens a little better.  Each of the holes are par 3 (I think) and about 50-75 yards in length.  The tee-box and green is turf, but the rest of the field is grass.    The best part is that it’s free!  It’s a fantastic place to practice and let kids learn.  Jerry and I were able to just watch Logan and help him with his mechanics.  We didn’t have anyone in front of or behind of us, so we played through a second round.  It was such a great experience.  We played a few balls at a time on each hole, so there was a lot of practice time.   Now Logan is ready to go back real soon.  So am I.