Datasheet

Data Type Stores Storage Space
Bit 0 or 1 1 byte
Tinyint Whole numbers from 0 to 255 1 bytes
Smallint Whole numbers from –32,768 to 32,767 2 bytes
Int Whole numbers from –2,147,483,648 to 2,147,483,647 4 bytes
Bigint Whole numbers from –9,223,372,036,854,775,808 to
9,223,372,036,854,775,807 8 bytes
Numeric Numbers from –10
38
+1 through 10
38
– 1 Up to 17 bytes
Decimal Numbers from –10
38
+1 through 10
38
– 1 Up to 17 bytes
Money –922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
Smallmoney –214,748.3648 to 214,748.3647 4 bytes
Numeric data types, such as
decimal and numeric, can store a variable amount of numbers to the right
and left of the decimal place.
Scale refers to the amount of numbers to the right of the decimal. Precision
defines the total size of the number, including the digits to the right of the decimal place. So 14.88531
would be a
numeric(7,5) or decimal(7,5). If you were to insert 14.25 into a numeric(5,1) column,
it would be rounded to 14.3.
Binary Data Types
Binary data types such as varbinary, binary, varbinary(max), or image store binary data such as
graphic files, Word documents, or MP3 files. The
image data type stores up to 2GB files outside the data
page. The alternative to an image data type is the
varbinary(max), which can hold more than 8K of
binary data and generally performs slightly better than an
image data type.
XML
When XML first came out, developers began to store this hierarchical data into a text or varchar col-
umn. You would typically store data in XML in a database when the columns in the application would
be variable, such as a survey application. This wasn’t optimal, as you can imagine, because you can’t
index this type of data inside a text column. In SQL Server 2005, you have the option to store XML data
into a proper XML data type that can be indexed, and schema can now be enforced. (We cover much
more about these in Chapter 15.)
DateTime
The datetime and smalldatetime types both store the date and time data for a value. The
smalldatetime is 4 bytes and stores from January 1, 1900 through June 6, 2079 and is accurate to
the nearest minute. The
datetime data type is 8 bytes and stores from January 1, 1753 through
December 31, 9999 to the nearest 3.33 millisecond.
Unfortunately, there is no
date or time data type. If you wish to store just the date, the time of midnight
will be time-stamped on each record. If you wish to insert just the time, today’s date is implicitly
11
SQL Server 2005 Architecture
04_055200 ch01.qxp 10/31/06 12:37 PM Page 11