Database Reference
In-Depth Information
lows you to store numbers from approximately negative 9 quintillion all the
way to 9 quintillion. (A quintillion is a 1 followed by 18 zeros.) Bigger num-
bers require more storage; bigint data requires 8 bytes.
Smallint
On the other side of the int data type, we have smallint. Smallint can hold
numbers from -32,768 through 32,767 and requires only 2 bytes of storage.
Tinyint
Rounding out the int family of data types is the tinyint. Requiring only
1 byte of storage and capable of storing numbers from 0 through 255, tinyint
is perfect for status columns. Note that tinyint is the only int data type that
cannot store negative numbers.
Bit
The bit data type is the SQL Server equivalent of a flag or a Boolean. The
only valid values are 0, 1, or NULL, making the bit data type perfect for
storing on or off, yes or no, or true or false. Bit storage is a bit more com-
plex (pardon the pun). Storing a 1 or a 0 requires only 1 bit on disk, but the
minimum storage for bit data is 1 byte. For any given table, the bit columns
are lumped together for storage. This means that when you have 1-bit to
8-bit columns they collectively take up 1 byte. When you have 9- to 16-bit
columns, they take up 2 bytes, and so on. SQL Server implicitly converts
the strings TRUE and FALSE to bit data of 1 and 0, respectively.
Decimal and Numeric
In SQL Server 2008, the decimal and numeric data types are exactly the
same. Previous versions of SQL Server do not have a numeric data type; it
was added in SQL Server 2005 so that the terminology would fall in line
with other RDBMS software. Both these data types hold numbers com-
plete with detail to the right of the decimal. When using decimal or nu-
meric, you can specify a precision and a scale. Precision sets the total
number of digits that can be stored in the number. Precision can be set to
any value from 1 through 38, allowing decimal numbers to contain 1
through 38 digits. Scale specifies how many of the total digits can be stored
to the right of the decimal point. Scale can be any number from 0 to the
precision you have set. For example, the number 234.67 has a precision of
5 and a scale of 2. The storage requirements for decimal and numeric vary
depending on the precision. Table 3.2 shows the storage requirements
based on precision.
Search WWH ::
Custom Search