Tip: Understand the 9 Numeric Data Types in SQL Server 2008
One of the most common data types that you will fi nd within SQL Server are numeric data types. There are nine numeric data types that ship with SQL Server 2008. Four data types are designed to store integer values of various sizes. Two data types are designed to store monetary data. Four data types are designed to store decimal-based numbers with varying accuracy. Here is an overview of the available numeric data types along with their range of values and required storage space.
| Data Type Range of Values Storage Space |
| Data Type | Range of Values | Storage Space |
| tinyint | 0 to 255 | 1 byte |
| smallint | –32,768 to 32,767 | 2 bytes |
| int | –231 to 231–1 | 4 bytes |
| bigint | –263 to 263–1 | 8 bytes |
| decimal(p,s) numeric(p,s) |
–1038+1 to 1038–1 | 5 to 17 bytes |
| smallmoney | –214,748.3648 to 214,748.3647 | 4 bytes |
| money | –922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes |
| real | –3.438 to –1.1838, 0, and 1.1838 to 3.438 | 4 bytes |
| float(n) | –1.79308 to –2.23308, 0, and 2.23308 to 1.79308 | 4 bytes or 8 bytes |
Note that the decimal and numeric data types are exactly equivalent to each other. Both data types are kept in the product for backwards compatibility. Either data type can be used when you need to store exact numeric data with decimal places, however, it is recommended that you choose either decimal or numeric for use throughout your organization for consistency.
The money and smallmoney data types are specifically designed to store monetary values with a maximum of four decimal places. It is interesting to note that while money and smallmoney were designed to store monetary values, financial institutions utilize decimal data types due to the ability to store a larger number of decimal places that are critical for interest rate and yield calculations.
The float data type takes an optional parameter of the number of digits stored after the decimal called the mantissa. If the mantissa is defined between 1 and 24, then a float will consume 4 bytes of storage. If the mantissa is defined between 25 and 53, then a float will consume 8 bytes of storage.
From the Microsoft Press book Microsoft SQL Server 2008 Step by Step by Mike Hotek.
Looking for More Tips?
For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.
For more Tips on other products, visit the TechNet Magazine Tips index.