SQL Server has several datatypes to support numeric data. They can be broken into four groups:
Integers
Approximate numeric datatypes
Exact numeric datatypes
Money datatypes
The integer datatypes?bigint, int, smallint, and tinyint?as their names imply, differ in the range of numbers they can store and the amount of storage they require. Integers are efficient to store, process, and present a wide range of unique values. This makes them ideal candidates when generating values for surrogate primary keys. Table 12.4 lists the storage requirements and capacities of the integer datatypes.
Integer Datatype | Storage Size | Minimum Value | Maximum Value |
---|---|---|---|
tinyint | 1 byte | 0 | 255 |
smallint | 2 bytes | ?32,768 | 32,767 |
int | 4 bytes | ?2,147,483,648 | 2,147,483,647 |
bigint | 8 bytes | ?9223372036854775808 | 9223372036854775807 |
What if you underestimated and used tinyint when you should have used smallint? No worries; SQL Server lets you change the datatype using the ALTER TABLE command or the Table Designer in Enterprise Manager.
The float and real datatypes are used to store values that have a large range and that you want to store with variable precision. These are generally used in scientific and statistical data where a wide range of values exists, yet absolute precision is not required. Do not use float and real when absolute precision must be maintained on the data values, especially for data values containing prices or money values. When operations on the float values are performed, decimal precision is not guaranteed. Use the money or exact numeric datatypes to maintain precision.
If you require a large range of values and a high degree of accuracy, use the decimal or numeric datatype. The two are synonymous, so I'll refer to them as decimal. When you use the decimal datatype, you specify the precision, which is the total number of digits stored, and the scale, which is the number of digits stored to the right of the decimal point. For example, a decimal datatype specified as decimal(5,2) would be able to store values from ?999.99 to 999.99. The amount of storage used is dependent on the precision and is listed in Table 12.5.
Precision | Storage Bytes |
---|---|
1?9 | 5 |
10?19 | 9 |
20?28 | 13 |
29?38 | 17 |
For storing monetary data, SQL Server provides the money and smallmoney datatypes. These are similar to decimal in that they are exact datatypes, with precision to four decimal places. Table 12.6 lists the range and storage required.
smallmoney | money | |
---|---|---|
Storage | 4 bytes | 8 bytes |
Minimum value | ?214,748.3648 | ?922,337,203,685,477.5808 |
Maximum value | +214,748.3647 | +922,337,203,685,477.5807 |
SQL Server also provides additional datatypes for specialized use. These are sql variant, cursor, and table datatypes. New for SQL Server 2000, the sql variant datatype allows for the storage of multiple datatypes within a single column. This means that the storage for individual rows can vary; for example, a column could store an integer for one row, and character data for another. The cursor and table datatypes cannot be used for column definitions in a table. The cursor datatype is used when you declare variables or parameters, as in a stored procedure, and the table datatype is used in User Defined Functions and stored procedures.
For detailed discussion on sql_variant and table datatypes, see Chapter 26, "Using Transact-SQL in SQL Server 2000." For more information on the cursor datatype, see Chapter 28, "Creating and Managing Stored Procedures in SQL Server."