Database Reference
In-Depth Information
C H A P T E R 17
Working with Text and Binary Data
Some kinds of data have special formats, are very large, or vary greatly in size. Here, I'll show you
techniques for working with text and binary data. In this chapter, I'll cover the following:
•
Understanding SQL Server text and binary data types
•
Storing images in a database
•
Retrieving images from a database
•
Working with text data
I'll also present the T-SQL for creating tables in the
tempdb
database, which is intended to hold any
temporary table. I'll start by covering what data types support these kinds of data.
Understanding SQL Server Text and Binary Data Types
SQL Server provides the types
CHAR
,
NCHAR
,
VARCHAR
,
NVARCHAR
,
BINARY
, and
VARBINARY
for working with
reasonably small text and binary data. You can use these with text (character) data up to a maximum of
8,000 bytes (4,000 bytes for Unicode data,
NCHAR
, and
NVARCHAR
, which use 2 bytes per character).
For larger data, which SQL Server 2012 calls
large-value data types
,
you should use the
VARCHAR(MAX)
,
NVARCHAR(MAX)
, and
VARBINARY(MAX)
data types.
VARCHAR(MAX)
is for non-Unicode text,
NVARCHAR(MAX)
is
for Unicode text, and
VARBINARY(MAX)
is for images and other binary data.
Warning
In SQL Server 2000, large data was stored using
NTEXT
,
TEXT
, and
IMAGE
data types. These data
types were deprecated and was removed with the release of newer versions of SQL Server. If you work with
legacy applications, you should consider converting
NTEXT
,
TEXT
, and
IMAGE
to
NVARCHAR(MAX)
,
VARCHAR(MAX)
,
and
VARBINARY(MAX)
, respectively. However, the
System.Data.SqlDbType
enumeration does not yet include
members for these data types, so we use
VARCHAR(MAX)
and
VARBINARY(MAX)
for column data types, but
Text
and
Image
when specifying data types for command parameters.
An alternative to using these data types is to not store the data itself in the database but instead
define a column containing a path that points to where the data is actually stored. This can be more
efficient for accessing large amounts of data, and it can save resources on the database server by
