After stressing the importance of choosing the appropriate datatype, I am going to give you a rule that violates it. Unless storage space is at an absolute premium, use an integer datatype. Intel processors, which is the predominant processor SQL Server runs on, are architected to work with 4 bytes at a time. This means an integer is the optimal size for the processor. Since it is the optimal size, integers will normally process faster than smallint or tinyint. If you need to restrict the range of values on the column, defining an integer column with a constraint is actually more efficient than using a smallint or tinyint.

Exact Numeric Datatypes

This group of datatypes gets its name from the fact that it stores an exact precision for all decimal data. Within the notations, p respresents the precision and s represenst the scale. The precision specifies the total number of digits allowed. The scale specifies the total number of digits to the right of the decimal. You will note in the table below that both datatypes have the same range of values and require the same space. Each of these datatypes is exactly equivalent. You should be using decimal in yrou table definitions as numeric has been retained mainly for backward compatibility.

Space allocations

SQL Server 7.0 Datatypes 1 2 3 4 5 6