Datasheet

12
Chapter 1: Introducing T-SQL and Data Management Systems
Data Type Storage Description
text 1 byte per
character.
Maximum
2,147,483,648
characters
(2GB).
The
text data type is similar to the varchar data type
in that it is a variable - length character data type. The
significant difference is the maximum length of about
2 billion characters (including spaces) and where the
data is physically stored. With a
varchar data type on
a table column, the data is stored physically in the row
with the rest of the data. With a
text data type, the
data is stored separately from the actual row and a
pointer is stored in the row so that SQL Server can find
the text. The
text data type is functionally equivalent
to the
varchar(MAX) data type.
nchar 2 bytes per
character.
Maximum
4000
characters
(8000 bytes).
The
nchar data type is a fixed - length type identical to
the
char data type, with the exception of the number of
characters supported.
char data is represented by a
single byte and thus only 256 different characters can be
supported.
nchar is a double - byte data type and can
support 65,536 different characters. The cost of the extra
character support is the double - byte length, so the
maximum
nchar length is 4000 characters or 8000 bytes.
nvarchar 2 bytes per
character. Up
to 2GB.
The nvarchar data type is a variable - length type
identical to the
varchar data type, with the exception
of the amount of characters supported.
varchar data
is represented by a single byte and only 256 different
characters can be supported.
nvarchar is a double -
byte data type and can support 65,536 different
characters. The cost of the extra character support is
the double - byte length, so the maximum
nvarchar
length is 4000 characters or 8000 bytes. This limit can
be exceeded by using the
(MAX) option, which allows
for the storage of 1,073,741,823 characters in 2GB.
ntext 2 bytes per
character.
Maximum
1,073,741,823
characters.
The
ntext data type is identical to the text data type,
with the exception of the number of characters
supported.
text data is represented by a single byte and
only 256 different characters can be supported.
ntext is a
double - byte data type and can support 65,536 different
characters. The cost of the extra character support is the
double - byte length, so the maximum
ntext length is
1,073,741,823 characters or 2GB. The
ntext data type is
functionally equivalent to the
nvarchar(MAX) data type.
Binary Data Types
binary 1 – 8000 bytes Fixed - length binary data. Length is fixed when created
between 1 and 8000 bytes. For example,
binary(5000)
specifies the reserving of 5000 bytes of storage to
accommodate up to 5000 bytes of binary data.
CH001.indd 12CH001.indd 12 3/26/10 11:35:38 AM3/26/10 11:35:38 AM