Data types in Microsoft SQL Server
Binary Data
Binary data consists of hexadecimal numbers. For example, the decimal
number 245 is hexadecimal F5. Binary data is stored using the binary,
varbinary, and image data types in Microsoft® SQL Server™ 2000. A column
assigned the binary data type must have the same fixed length (up to 8
KB) for each row. In a column assigned the varbinary data type, entries
can vary in the number of hexadecimal digits (up to 8 KB) they contain.
Columns of image data can be used to store variable-length binary data
exceeding 8 KB, such as Microsoft Word documents, Microsoft Excel spreadsheets,
and images that include bitmaps, Graphics Interchange Format (GIF), and
Joint Photographic Experts Group (JPEG) files.
In general, use varbinary for storing binary data, unless the length
of the data exceeds 8 KB, in which case you should use image. It is recommended
that the defined length of a binary column be no larger than the expected
maximum length of the binary data to be stored.
Character Data
Character data consists of any combination of letters, symbols, and
numeric characters. For example, valid character data includes "928",
"Johnson", and "(0*&(%B99nh jkJ".
In Microsoft® SQL Server™ 2000, character data is stored using the char,
varchar, and text data types. Use varchar when the entries in a column
vary in the number of characters they contain, but the length of any entry
does not exceeds 8 kilobytes (KB). Use char when every entry for a column
has the same fixed length (up to 8 KB). Columns of text data can be used
to store ASCII characters longer than 8 KB. For example, because HTML
documents are all ASCII characters and usually longer than 8 KB, they
can be stored in text columns in SQL Server prior to being viewed in a
browser.
It is recommended that the defined length of a character column be no
larger than the maximum expected length of the character data to be stored.
To store international character data in SQL Server, use the nchar,
nvarchar, and ntext data types.
Unicode Data
Traditional non-Unicode data types in Microsoft® SQL Server™ 2000 allow
the use of characters that are defined by a particular character set.
A character set is chosen during SQL Server Setup and cannot be changed.
Using Unicode data types, a column can store any character defined by
the Unicode Standard, which includes all of the characters defined in
the various character sets. Unicode data types take twice as much storage
space as non-Unicode data types.
Unicode data is stored using the nchar, nvarchar, and ntext data types
in SQL Server. Use these data types for columns that store characters
from more than one character set. Use nvarchar when a column's entries
vary in the number of Unicode characters (up to 4,000) they contain. Use
nchar when every entry for a column has the same fixed length (up to 4,000
Unicode characters). Use ntext when any entry for a column is longer than
4,000 Unicode characters.
Date and Time Data
Date and time data consists of valid date or time combinations. For
example, valid date and time data includes both "4/01/98 12:15:00:00:00
PM" and "1:28:29:15:01 AM 8/17/98". Date and time data
is stored using the datetime and smalldatetime data types in Microsoft®
SQL Server™ 2000. Use datetime to store dates in the range from January
1, 1753 through December 31, 2023 (requires 8 bytes of storage per value).
Use smalldatetime to store dates in the range from January 1, 2024 through
June 6, 2024 (requires 4 bytes of storage per value).
Numeric Data
Numeric data consists of numbers only. Numeric data includes positive
and negative numbers, decimal and fractional numbers, and whole numbers
(integers).
Integer Data
Integer data consists of negative or positive whole numbers, such as
-15, 0, 5, and 2509. Integer data is stored using the bigint, int, smallint,
and tinyint data types in Microsoft® SQL Server™ 2000. The bigint data
type can store a larger range of numbers than the int data type. The int
data type can store a larger range of integers than smallint, which can
store a larger range of numbers than tinyint.
Use the bigint data type to store numbers in the range from -2^63 (-9223372036854775808)
through 2^63-1 (9223372036854775807). Storage size is 8 bytes.
Use the int data type to store numbers in the range from -2,147,483,648
through 2,147,483,647 only (requires 4 bytes of storage per value).
Use the smallint data type to store numbers in the range from -32,768
through 32,767 only (requires 2 bytes of storage per value), and the tinyint
data type to store numbers in the range from 0 through 255 only (requires
1 byte of storage per value).
Decimal Data
Decimal data consists of data that is stored to the least significant
digit. Decimal data is stored using decimal or numeric data types in SQL
Server. The number of bytes required to store a decimal or numeric value
depends on the total number of digits for the data and the number of decimal
digits to the right of the decimal point. For example, more bytes are
required to store the value 19283.29383 than to store the value 1.1.
In SQL Server, the numeric data type is equivalent to the decimal data
type.
Monetary Data
Monetary data represents positive or negative amounts of money. In Microsoft®
SQL Server™ 2000, monetary data is stored using the money and smallmoney
data types. Monetary data can be stored to an accuracy of four decimal
places. Use the money data type to store values in the range from -922,337,203,685,477.5808
through +922,337,203,685,477.5807 (requires 8 bytes to store a value).
Use the smallmoney data type to store values in the range from -214,748.3648
through 214,748.3647 (requires 4 bytes to store a value). If a greater
number of decimal places are required, use the decimal data type instead.
Special Data
Special data consists of data that does not fit any of the categories
of data such as binary data, character data, Unicode data, date and time
data, numeric data and monetary data. Microsoft® SQL Server™ 2000 includes
four types of special data:
timestamp
Is used to indicate the sequence of SQL Server activity on a row, represented
as an increasing number in a binary format. As a row is modified in a
table, the timestamp is updated with the current database timestamp value
obtained from the @@DBTS function. timestamp data is not related to the
date and time of an insert or change to data. To automatically record
times that data modifications take place in a table, use either a datetime
or smalldatetime data type to record the events and triggers.
Note In
SQL Server, rowversion is a synonym for timestamp.
bit
Consists of either a 1 or a 0. Use the bit data type when representing
TRUE or FALSE, or YES or NO. For example, a client questionnaire that
asks if this is the client's first visit can be stored in a bit column.
uniqueidentifier
Consists of a 16-byte hexadecimal number indicating a globally unique
identifier (GUID). The GUID is useful when a row must be unique among
many other rows. For example, use the uniqueidentifier data type for a
customer identification number column to compile a master company customer
list from multiple countries.
sql_variant
A data type that stores values of various SQL Server–supported data
types, except text, ntext, timestamp, image, and sql_variant.
table
A special data type used to store a result set for later processing.
The table data type can be used only to define local variables of type
table or the return value of a user-defined function.
user-defined
Allows a user-defined data type, product_code, for example, that is
based on the char data type and defined as two uppercase letters followed
by a five-digit supplier number.
Related Topics:
 How
to change columns in an existing table
Autonumbering and
identifier columns
Column Data Types
About tables
Indexes and primary keys
Relationships
| 
