Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    33

    Unanswered: data types size question

    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?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Answer: It depends.

    here is one place where it can hurt. Implicit conversions. Suppose your table is defined with a numeric (5,2) field that happens to be indexed. You query it using a variable defined as numeric (9, 2). Which side of that equals sign is going to be converted, the field, or the variable?

    The side that has the lesser accuracy gets converted to the type of the side with the greater accuracy. in this case, the field would be implicitly converted to numeric(9, 2). once that happens, the index on that field is no longer any good to you, because the index tells the optimizer nothing about how the converted values can be found. The result is usually a table (or index) scan. Not horrible, if you expect only thousands of records, but a pain in the neck, if you expect tens of thousands, or even millions of records.

    The short of it is: Always query the database with variables of the same type as the field.

  3. #3
    Join Date
    Mar 2008
    Posts
    33
    thats a good point,

    and generally speaking let's say I decide to use numeric(7,2) all across the board instead of numeric(9,2) (for both variables and columns...), will there be any performance gains of any sort or maybe there is no difference at all since both are 5 bytes,

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    So long as the data itself stays "in bounds", there should be no performance problems at all. Numeric(7, 2) should give you a max value of 99,999.99. It may not be long before inflation (or the deflating dollar) catches up with you at that point.

  5. #5
    Join Date
    Mar 2008
    Posts
    33
    hehe,

    so we agree there would be know performance problem but internally does sql server handles numeric(7,2) faster then numeric(9,2) or it's all the same?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes.
    Code:
    --  ptp  20080325  See http://www.dbforums.com/showthread.php?t=1628644
    
    DECLARE @i	BIGINT
    ,  @iMax	BIGINT
    ,  @m0		MONEY
    ,  @n5		NUMERIC(5, 2)
    ,  @n9		NUMERIC(9, 2)
    ,  @d1		DATETIME
    
    SET @iMax = 1000000
    SET @m0 = 0
    SET @n5 = 0
    SET @n9 = 0
    
    SELECT DataLength(@m0), DataLength(@n5), DataLength(@n9)
    
    --  Empty loop to see how long basic loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
       BEGIN
          SELECT @i = @i - 1
       END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'Empty'
    
    --  m loop to see how long MONEY loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
       BEGIN
          SELECT @m0 = @m0 + 1, @m0 = @m0 - 1, @i = @i - 1
       END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'MONEY'
    
    --  n5 loop to see how long NUMERIC(5, 2) loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
       BEGIN
          SELECT @n5 = @n5 + 1, @n5 = @n5 - 1, @i = @i - 1
       END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'NUMERIC(5, 2)'
    
    --  n9 loop to see how long NUMERIC(9, 2) loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
       BEGIN
          SELECT @n9 = @n9 + 1, @n9 = @n9 - 1, @i = @i - 1
       END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'NUMERIC(9, 2)'
    Use MONEY... You'll get accurate answers while you're still young enough to remember why you asked the question!

    -PatP

  7. #7
    Join Date
    Mar 2008
    Posts
    33
    thanks for the script,

    here is a modified version of your script that I just did to test the performance differences between the various data types when used in the context of INSERT or SELECT to/from a table.

    INSERT turn out to be slightly faster with NUMERIC fields (I suppose that's expected since the size is 5 bytes instead of 8 bytes)
    But the SELECT are faster with SMALLMONEY & MONEY.


    Code:
    use tempdb
    
    create table tbSmallmoney (RowID int primary key identity(1,1), data smallmoney)
    create table tbMoney (RowID int primary key identity(1,1), data money)
    create table tbNumeric5 (RowID int primary key identity(1,1), data numeric(5,2))
    create table tbNumeric9 (RowID int primary key identity(1,1), data numeric(9,2))
    
    
    DECLARE @i	BIGINT
    ,  @iMax	BIGINT
    ,  @d1		DATETIME
    ,  @sm SMALLMONEY
    ,  @m MONEY
    ,  @n5 NUMERIC(5,2)
    ,  @n9 NUMERIC(9,2)
    
    SET @iMax = 100000
     
    
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    
    --  Empty loop to see how long basic loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
       BEGIN
          SELECT @i = @i - 1
       END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'Empty'
    
    --  see how long SMALLMONEY loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		insert into tbSmallmoney(data) values(1.00)
    		set @i = @i - 1
    	END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'SMALLMONEY - INSERT'
    
    --  see how long MONEY loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		insert into tbMoney(data) values(1.00)
    		set @i = @i - 1
    	END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'MONEY - INSERT'
    
    -- see how long NUMERIC(5, 2) loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		insert into tbNumeric5(data) values(1.00)
    		set @i = @i - 1
    	END
    
    
    SELECT DateDiff(ms, @d1, GetDate()), 'NUMERIC(5, 2) - INSERT'
    
    --  see how long NUMERIC(9, 2) loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		insert into tbNumeric9(data) values(1.00)
    		set @i = @i - 1
    	END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'NUMERIC(9, 2) - INSERT'
    
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    
    ---------------------------------------------------------------------------------------------------------
    
    --  see how long SMALLMONEY loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		set @sm = (select data from tbSmallmoney where RowID = @i)
    		set @i = @i - 1
    	END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'SMALLMONEY - SELECT'
    
    --  see how long MONEY loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		set @m = (select data from tbMoney where RowID = @i)
    		set @i = @i - 1
    	END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'MONEY - SELECT'
    
    -- see how long NUMERIC(5, 2) loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		set @n5 = (select data from tbNumeric5 where RowID = @i)
    		set @i = @i - 1
    	END
    
    
    SELECT DateDiff(ms, @d1, GetDate()), 'NUMERIC(5, 2) - SELECT'
    
    --  see how long NUMERIC(9, 2) loop takes
    
    SET @i = @iMax
    SET @d1 = GetDate()
    
    while 0 < @i
    	BEGIN
    		set @n9 = (select data from tbNumeric9 where RowID = @i)
    		set @i = @i - 1
    	END
    
    SELECT DateDiff(ms, @d1, GetDate()), 'NUMERIC(9, 2) - SELECT'
    
    
    
    
    drop table tbSmallmoney
    drop table tbMoney
    drop table tbNumeric5
    drop table tbNumeric9
    Last edited by harveysburger; 03-26-08 at 00:55.

  8. #8
    Join Date
    Mar 2008
    Posts
    33
    By Jeff Moden on sqlservercentral
    ----------------------------------



    Code:
    --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 ' '

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •