Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 198

First, choose the smallest data type available that will satisfy the needs for column. For example,
use a smalldatetime instead of a datetime if the smalldatetime will suffice for the column in
question.

❑

Second, favor the non-variable data types over the variable ones. For example, use CHAR instead
of VARCHAR when possible. Note that this doesn't mean that variable data types shouldn't be
used. When column data is going to vary significantly in length from row to row, then variable
length data types should definitely be used.

❑

For SQL Server 2005, Microsoft has organized the data types into the following groups (see Table 8-2):

❑ Exact numerics

Approximate numerics

❑

Date and time

❑

Character strings

❑

Unicode character strings

❑

Binary strings

❑

Other

❑

With SQL Server 2005 SP2, Microsoft introduced the VARDECIMAL storage format. This is not a
data type. Rather it is a mechanism for storing decimal data in a variable-length structure. It is activated
first by enabling the feature at the database level. This is done using the sp_db_vardecimal_storage_
format command. Once that is done, each table for which you want to enable vardecimal storage must
also have it enabled. This is done by using the sp_tableoption command. For further details see BOL.

Table 8-2: Data Types

Group Type name Range Storage Comments

Exact
numerics

Tinyint 0 to 255 1 byte n/a

Smallint − 32,768 to 32,767 2 bytes n/a

Int − 2,147,483,648 to

2,147,483,647

4bytes n/a

Bigint − 9,223,372,036,

854,775,808 to
9,223,372,036,
854,775,807

8bytes n/a

Bit 0 or 1 (also

NULL)

1bytefor
every 8 bit
columns

n/a

10 38 Decimal (p, s) or

Numeric (p, s)

− + 1to Precision

Storage
1

P (precision) The
maximum number of
digits that can be stored.

10 38 − 1

− 95 t s