Numeric DataType & Storage, Choose wisely..!!!

SQL Server provides two datatypes decimal and numeric that have fixed precision and scale. numeric datatype is primitive datatype which is equivalent to decimal and it is kept merely for backward compatibility. Most of the time developer use these datatypes without keeping in mind the storage aspect, which i think is very important and affects performance of the reports.

Lets briefly discuss about this datatype and its storage aspect.

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Table below specifies storage structure that SQL Server uses based on the precision selected by user.

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

By default SQL Server uses 18,3 as precision and scale for these datatypes. Most of the time developers just these default precision and dont really think of what actual is the requirement for them. As a good practice always choose precision manually as per your need.

Hope you find this post useful. Feel free to provide your comments.