A word on the decimal data type

Was inspired to write up a few notes on the SQL Server data type.  This is pretty basic stuff, but everyone should know it.

If you declare a column as DECIMAL, it will default to the precision and scale 18, 0.  Not very useful, is it?  You might as well have used a bigint column to store integer values like that.  So first off, when you declare a decimal column, always give it precision and scale.

When you want to use decimal, figure out how many places you need to store, then choose a precision of either 9 or 19.   Then figure out the number of places to the right of the decimal point, that's the scale.

Here's why.  The decimal data type's storage requirement per row is based on that first number. 

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

As you can see, there's no different between choosing a precision of 7, 8 or 9.  So just choose 9. 

If you need to store more than that, go to 19. 

If you need to store more number than that, go to 28.  Whatever you do, don't go to float.  I've never felt forced to use float.  The benefit of float is that it allows you to store very long numbers for smaller space, but the tradeoff is a lack of accuracy that I've seen lead to rounding errors and very upset CFO's.  Stick with decimal.

Also, numeric is the same thing as decimal.  The keywords are interchangable.

Here's some code proof for you:

create table t1
( decimal decimal,decimal9 decimal(9,2),decimal19 decimal(19,2),bigint bigint,integer integer, smallint smallint)go
select t.name, c.name, max_size_in_row = max_length, precision, scale
from sys.tables tinner join sys.columns con t.object_id = c.object_idwhere t.name = 't1'