Numeric Datatypes

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

Integer 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.

Table 12.4. Storing Integer Information

Integer Datatype	Storage Size	Minimum Value	Maximum Value
tinyint	1 byte		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.

Approximate Numeric Datatypes

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.

exact numeric Datatypes

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.

Table 12.5. Storing Decimal or Numeric Information

Precision	Storage Bytes
1 “9	5
10 “19	9
20 “28	13
29 “38	17

money Datatypes

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.

Table 12.6. Storing Monetary Information

	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

Special Datatypes

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."