March 25, 2008 at 12:31 pm
Hi all,
little background,
When I need an amount column I usually declare a numeric(9,2). Anything bigger then that ends up taking 9 bytes instead of 5 bytes and for the most part (9,2) is more then enough. Also I usually don't use (5, 2), (6, 2), etc... since they end up taking the same space as a (9,2), that is unless I want to specifically restrict the amount value to something smaller. Occasionally I will use smallmoney for smaller amounts, however I pretty much never use money data type since it's 8 bytes... that is unless I need more digits after the dot...
so enough background, here is the question,
Let's say you've got a numeric(5, 2) and a numeric(9, 2), as far performance goes are the two fields the same? Both columns have a size of 5 bytes so I assume that as far storage and I/O goes there is no performance difference.... Fair assumption?
But what about CPU and other things, are there any performance gain in using a smaller numeric column?
March 25, 2008 at 8:49 pm
For the numeric datatype anything with a percision up to 9, is 5 bytes.
More info http://msdn2.microsoft.com/en-us/library/ms187746.aspx
The small money data type consumes 4 bytes, while money consumes 8.
more info http://msdn2.microsoft.com/en-us/library/ms179882.aspx
The biggest benifits to using smaller data types are:
- is a best practice
- saves on storage
- helps query performance because each row returned contains less bytes.
- faster sorting
In your case, numeric(9,2) and numeric(5,2) require the same number of bytes for storage. You could use small money and save a byte per row, but I really cannot see that big a performance gain.
March 25, 2008 at 9:25 pm
FYI
someone on dbforums posted a simple script to test the performance of the various data types,
http://www.dbforums.com/showthread.php?t=1628644
the script seems to be a proof that internally SQL Server handles MONEY much faster then numeric, and also that numeric(9,2) is slightly slower then numeric(5,2)
that says MONEY takes up more storage then NUMERIC so I wonder once you factor in I/O which data type makes more sense... i will make some experiments....
March 26, 2008 at 12:01 am
I just can't bring myself to believe in any test that uses RBAR as much as that :sick:
And, over a million rows, datatype isn't going to make much of a difference either in performance or IO. Of course, that's an extraordinary claim on my part and extraordinary claims require extraordinary proof... and we all know what that means... IT'S MILLION ROW TESTING TIME!!!!! 😛 Gentlemen and Ladies...
--drop table jbmtest
--===== Create and populate a 1,000,000 row test table.
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeDec52 = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS DECIMAL(5,2)),
SomeDec92 = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS DECIMAL(9,2)),
SomeSMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS SMALLMONEY),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeFloat24 = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS FLOAT(24)),
SomeFloat53 = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS FLOAT(53))
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD CONSTRAINT PK_JBMTest_RowNum PRIMARY KEY CLUSTERED (RowNum)
--===== Test the million rows
SET NOCOUNT ON
SET STATISTICS IO ON
PRINT REPLICATE('=',80)
PRINT '========== Addition test (123.45) =========='
PRINT REPLICATE('=',80)
PRINT '---------- Decimal(5,2) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeDec52 = SomeDec52+123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Decimal(9,2) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeDec92 = SomeDec92+123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Small Money ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeSMoney = SomeSMoney+123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Money ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeMoney = SomeMoney+123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Float(24) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeFloat24 = SomeFloat24+123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Float(53) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeFloat53 = SomeFloat53+123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT REPLICATE('=',80)
PRINT '========== Divide test (123.45) =========='
PRINT REPLICATE('=',80)
PRINT '---------- Decimal(5,2) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeDec52 = SomeDec52/123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Decimal(9,2) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeDec92 = SomeDec92/123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Small Money ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeSMoney = SomeSMoney/123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Money ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeMoney = SomeMoney/123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Float(24) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeFloat24 = SomeFloat24/123.45
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Float(53) ----------'
SET STATISTICS TIME ON
UPDATE dbo.JBMTest
SET SomeFloat53 = SomeFloat53/123.45
SET STATISTICS TIME OFF
PRINT ' '
DECLARE @Bitbucket SQL_VARIANT -- For SELECT tests
PRINT REPLICATE('=',80)
PRINT '========== SELECT test =========='
PRINT REPLICATE('=',80)
PRINT '---------- Decimal(5,2) ----------'
SET STATISTICS TIME ON
SELECT @Bitbucket = SomeDec52
FROM dbo.jbmTest
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Decimal(9,2) ----------'
SET STATISTICS TIME ON
SELECT @Bitbucket = SomeDec92
FROM dbo.jbmTest
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Small Money ----------'
SET STATISTICS TIME ON
SELECT @Bitbucket = SomeSMoney
FROM dbo.jbmTest
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Money ----------'
SET STATISTICS TIME ON
SELECT @Bitbucket = SomeMoney
FROM dbo.jbmTest
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Float(24) ----------'
SET STATISTICS TIME ON
SELECT @Bitbucket = SomeFloat24
FROM dbo.jbmTest
SET STATISTICS TIME OFF
PRINT ' '
PRINT '---------- Float(53) ----------'
SET STATISTICS TIME ON
SELECT @Bitbucket = SomeFloat53
FROM dbo.jbmTest
SET STATISTICS TIME OFF
PRINT ' '
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2008 at 7:34 am
thanks,
and sorry for the row-by-agonizing-row from the other tests 🙂
March 26, 2008 at 7:57 am
Wasn't your fault... the "other" person started it that way.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply