SQL Bible [Electronic resources] نسخه متنی

No Strings Attached

Generally all strings could be divided into
character strings (to store plain text) and binary strings which contain either
machine code (computer programs) or special binary instructions for other
programs.

Character strings

A character string can be defined simply
as a sequence of bytes. The length of the string is the number of bytes in the
sequence. A string of zero length is called an empty
string. It can be an equivalent to
NULL (special concept introduced at the
end of this chapter) or not, depending on implementation. SQL99 specifically
differentiates between empty strings and nulls.

All strings in SQL can be of fixed length
or varying length. The difference is quite simple, but sometimes not very easy
to understand for people with no technical background, so let us explain it in
some greater detail.

Fixed-length character
strings

If you define
string to be of a fixed length, the system preallocates the desired number of
bytes in memory and/or computer hard disk. It does not matter if the actual
value to be stored in that string is exactly that many bytes, twice smaller, or
just one character long — it is still going to occupy the whole allocated space
(unused bytes will be padded with blank characters), so all strings will have
exactly the same length. For example, imagine you defined a column
DATABASE as having a character field of
length 13, and now we want to store three strings in that column:
ORACLE,
UDB2, and
MS
SQL
SERVER.
Figure
3-1 illustrates the results of that operation. Note that all strings
are exactly 13 bytes long.

Figure 3-1: Fixed-length
character string storage

Character strings of varying
length

If you define
DATABASE column as a varying-length
string with maximum 13 characters to store, the picture will be different. The
actual memory or disk space required to hold our values is allocated
dynamically. As a result, all three strings will be of different length and
will require different numbers of bytes to hold them. String
ORACLE occupies 6 bytes,
DB2 UDB — 4 bytes and
MS
SQL
SERVER takes maximum allowed 13 bytes.
(See
Figure
3-2.)

Figure 3-2: Varying-length
character string storage

Here is the general platform-independent
recommendation: use a fixed-length data type when your values are expected to
be of the same size, and a varying-length one for values when size is expected
to vary considerably. In the example above it rather looks logical to use the
varying-length strings, but if we need a column to store, say, gender in form
M or
F, a fixed-length string is more
appropriate.

National character strings

Even though English is a very popular
language, it is not the only language on Earth. And in spite of the fact that
practically all major software companies reside in the United States, the
market dictates its own rules. For example, Oracle is a very popular database
vendor around the world with customers in China, India, Korea, Germany, France,
Israel, Russia, Saudi Arabia, and many other countries.

Now we have a little bit of a problem.
So far we've used terms "character" and "byte" as synonyms. Byte is a computer
term for a unit of information storage that consists of 8 bits. Each bit can
either be 1 or 0 and the combination of 8 bits allows us to store 256
(2⁸)
distinct values (or 256 different characters represented by numbers from 0 to
255). That looks like a lot, but ... not actually. We need separate holders for
uppercase and lowercase letters, punctuation marks, digits, math symbols, and
so on. That barely leaves space for distinct characters used in other languages
that employ the Latin alphabet. And what about the ones which don't? There are
about 3,000 different languages in the world, dead and living, in addition to
constructed languages like J.R.R. Tolkien's Quenya, Sindanin, or Entish, and
most of them have their own distinct alphabets!

The solution seems to be rather
intuitive — use two bytes per character instead of one. That allows 65,535
(2¹⁶)
distinct combinations, which is enough to store all existing characters from
every major language on Earth.

SQL has two data types to store strings
in national characters — national character string and national character
string of varying length — that behave in exactly same way as previously
described character string and character string of varying length
correspondingly, but use the two-byte Unicode standard. So, if you declared
your DATABASE column as a national character field of size 13, it would still
hold 13 characters, but would reserve 2 bytes for each letter, for a total of
26 bytes. The difference is, now it can hold the names from previous examples
spelled in practically any language, for example, in Russian.
Figure
3-3 illustrates that.

Figure 3-3: Fixed-length Unicode
character storage

Figure 3-4 shows same concept for national
characters of varying length.

Figure 3-4: Varying-length
Unicode character storage

The ultimate successor to ASCII is
Unicode. It is a standard double-byte character set that
assigns a unique number to every single character, so all of them can be
represented in one character set. To speak in database terminology, the Unicode
character number serves as a primary key to index virtually all the world's
characters. (Another good example of database use!) The Unicode standard has
been adopted by such industry leaders as Apple, HP, IBM, Microsoft, Oracle,
SAP, Sun, Sybase, Unisys, and many others. Unicode is required by modern
standards such as XML, Java, JavaScript, CORBA, WML, HTML, and is the official
way to implement ISO standard 10646. It is supported in many operating systems,
all modern browsers, major RDBMS vendors, and many other products. The
emergence of the Unicode standard and the availability of tools supporting it
are among the most significant recent global software technology trends.

Let's talk about SQL99 standards and
implementation specifics for all types of character strings. These are
summarized in
Table
3-1.

SQL99

SQL99 has two major character sets:
CHARACTER and
CHARACTER
VARYING. In addition, there are also
NATIONAL
CHARACTER and
NATIONAL
CHARACTER
VARYING.

CHARACTER can also be abbreviated with
CHAR. The size can optionally be
specified in the form
CHARACTER(n). For example,
CHARACTER(15) can hold character
strings up to 15 characters long. If size is omitted, the default is 1. An
error occurs if one tries to store a string that is bigger than the size
declared.

CHARACTER
VARYING can be abbreviated with
CHAR
VARYING or
VARCHAR. You have to specify the
maximum size for strings to be stored, for example,
CHARACTER
VARYING(15) holds 15-character
strings, or smaller.

NATIONAL
CHARACTER (NATIONAL
CHAR,
NCHAR,
CHARACTER
CHARACTER
SET
<char_set_name>) specifies the
default data type for the country of implementation. This is a fixed-length
character string data type.

NATIONAL
CHARACTER
VARYING (NATIONAL
CHAR
VARYING,
NCHAR
VARYING,
CHARACTER
VARYING
CHARACTER
SET
< char_set_name>,
CHAR
VARYING
CHARACTER
SET
< char_set_name>) is a
varying-length country-specific character string data type.

CLOB is
a new SQL99 data type to store large nondatabase-structured text objects of
varying size and complexity, such as employees' resumes, collections of papers,
books, and other similar data.

Oracle
9i

Oracle is fully compliant with SQL99
standards for character strings.

CHAR is used for fixed-length
strings. The default length for a
CHAR column is 1 byte with a
maximum of 2,000 bytes.

VARCHAR2 is an Oracle data
type to store varying-length character strings. It does not have the default
length, so you have to specify a value from 1 to 4,000 (maximum number of bytes
for
VARCHAR2).

NCHAR and
NVARCHAR2 are used to store
fixed-length and varying-length national character strings. Beginning with
Oracle9i, they were redefined to be Unicode-only data
types and can hold up to 2,000 and 4,000 characters (not
bytes!) correspondingly. That means if you declare a column to be
CHAR(100) it will allocate 100
bytes per column, but
NCHAR(100) Unicode-based column
requires 200 bytes.

CLOB and
NCLOB can store up to four
gigabytes of data in Oracle. Both fixed-length and variable-length character
sets are supported.
CLOB uses the
CHAR database character set, and
NCLOB stores Unicode data using
the national character set.

LONG is an old Oracle data
type to store variable-length character strings containing up to two gigabytes.
It is similar to
VARCHAR2, but has many
limitations. For example, you cannot use
LONG in the
WHERE clause of a
SELECT statement (discussed in
Chapter
8), a table can't have more than one
LONG column, it can't be indexed,
and so on. Oracle strongly recommends to discontinue the use of the
LONG data type and use
CLOB instead.

DB2 UDB 8.1

DB2 has
following character string data types:

CHARACTER is compliant with
SQL99 standards. The maximum length is 254 characters. The default length is
1.

VARCHAR is used for
varying-length strings and has a maximum of 32,672 characters.

LONG
VARCHAR is virtually same as
VARCHAR, but can hold larger
values (up to 32,700) and can't be limited to a certain number of
characters.

CLOB types are SQL99
compliant varying-length strings of up to two gigabytes. An optional maximum
length can be supplied in kilobytes (K|k), megabytes (M|m), or gigabytes (G|g).
For example,
CLOB (10M) would allow maximum of
10,048,576 characters.

GRAPHIC is a rough DB2
equivalent to
NATIONAL
CHARACTER. It is a double-byte
data type, which may range from 1 to 127 characters. If the length
specification is omitted, a length of 1 is assumed.

VARGRAPHIC is a
varying-length double-byte character string data type, comparable to SQL99
NATIONAL
CHARACTER
VARYING. The range is from 1 to
16,336.

LONG
VARGRAPHIC is similar to
VARGRAPHIC with a maximum length
of 16,350. It does not have an optional length limit to be supplied by
user.

DBCLOB is a double-byte
equivalent to
CLOB. Maximum storage is one
gigabyte of character data.
DBCLOB accepts a maximum length
in the same way as
CLOB.

See
Table
3-1 for more information.

MS SQL Server 2000

The following character string data
types are supported by MS SQL Server:

CHAR and
VARCHAR are used for fixed-length
and variable-length character data correspondingly. The maximum length is 8,000
characters. Unlike Oracle, you don't have to specify length for
VARCHAR — it defaults to 1 like
CHAR.

TEXT is similar to
VARCHAR, but can hold much larger
values. Its maximum length is two gigabytes or 2³¹ – 1
(2,147,483,647) characters.

NCHAR and
NVARCHAR, and
NTEXT are Unicode equivalents to
CHAR,
VARCHAR, and
TEXT.
NCHAR and
NVARCHAR can hold up to 4,000
characters;
NTEXT is much larger — one
gigabyte or 2³⁰ – 1
(1,073,741,823) characters.

For SQL99 compatibility synonyms see
Table
3-1. If one data type has more than one name (or synonym) the most
widely used name is given in italics.

Character string literals

The terms literal
and constant refer to a fixed data value, for
instance

'Frozen Margarita' 'ALEX'
'2003/08/07' '10101101'

are all character literals. Character
literals are enclosed in single quotes. To represent one single quotation mark
within a literal, you can enter two single quotation marks:

'O''Neil'

Character literals are surprisingly
consistent between all our three major vendors, with only slight variations.
For example, MS SQL Server allows double quotes for character literals instead
of single ones if the option
QUOTED_IDENTIFIER is set off for a
connection. To represent a national character set literal, it has to be
preceded by capital letter
N (DB2 understands
G in addition to
N):

N'Jack Smith' N'Boris M.
Trukhnov' N'123 OAK ST.'

Text entered using this notation is
translated into the national character set.

Binary strings

A binary
string is a sequence of bytes in the same way that a character string is, but
unlike character strings that usually contain information in the form of text,
a binary string is used to hold nontraditional data such as images, audio and
video files, program executables, and so on. Binary strings may be used for
purposes similar to those of character strings (e.g., to store documents in MS
Word format), but the two data types are not compatible; the difference being
like text and a photo of the same text. Binary string data types are summarized
in
Table
3-2.

It might sound a little bit confusing —
why plain text documents can be stored as character strings, and a Word
document has to be treated as a binary string. The thing is, a Word file is a
text document from user's point of view, but from computer storage perspective
it is not. In addition to plain text characters it contains many special signs
and instructions that only MS Word software can interpret. The same is true for
any other special files — bitmaps, spreadsheets, audio and video files, and so
forth. You can think of it in this way: a special file (e.g., of the DOC, XLS,
BMP, or AVI type) is like a tape for VCR, whereas a program (MS Word, Excel,
Paint, QuickTime Player) is like a VCR. You have to have a VCR to play a tape,
and it has to be the right VCR — if you try to play a standard US VHS tape in
NTSC format on a European video recorder (PAL format), it's not going to work.
You might see some blinking on your screen, you will hear some noise, but you
will definitely not be able to watch the movie. Just try to open a Word file
with, say, Notepad and you will see what we are talking about.

SQL99

SQL99 has following data types to store
binary strings:
BIT,
BIT
VARYING, and
BLOB.

BIT is a fixed-length binary
string somewhat similar to
CHAR. If you declare a column to be
BIT(100), 100 bytes will be allocated
in memory/disk, and if the object you store is just 60 bytes, it's still going
to occupy all 100 bytes.

BIT
VARYING is similar to
VARCHAR — even if you specify
BIT
VARYING(100) to be the data type
lasting the previous example, it will only take 60 bytes to store the
object.

BLOB is a binary equivalent to
CLOB.

Oracle
9i

Oracle doesn't have an equivalent to
SQL99 BIT, but has two data types that correspond to
BIT
VARYING —
RAW and
LONG
RAW.
BLOB data type is also supported.

RAW can hold a maximum of 2,000
bytes. The size has to be specified.

LONG
RAW can accumulate up to two
gigabytes of data. This data type is obsolete, and Oracle strongly recommends
converting it to
BLOB.

BLOB can store up to four
gigabytes of binary data in Oracle.

DB2
UDB 8.1

The only data type for binary strings in
DB2 is
BLOB, which can be up to 2 gigabytes
long.

MS SQL Server
2000

MS SQL Server has three different data
types for binary strings:
BINARY,
VARBINARY, and
IMAGE.

BINARY is a fixed-length data
type to store binary data. The size can be specified from 1 to 8,000; the
actual storage volume is size
+ 4 bytes.

VARBINARY can hold
variable-length binary data. The size is from 1 through 8,000. Storage size is
the actual length of the data entered
+ 4 bytes. The data entered can be 0
bytes in length.

IMAGE is a variable-length binary
data type that can hold from 0 through 2,147,483,647 bytes (two gigabytes) of
data.

Binary string literals

MS SQL Server allows literals for
binary string fields (BINARY,
VARBINARY,
IMAGE) either in the form of
hexadecimal numbers prefixed with
0x or as binary strings. The value has
to be unquoted:

0xAE
0101010010100110

MS SQL Server implicitly converts these
literals into appropriate binary format. Oracle and DB2 don't have binary
string literals; the values have to be converted into proper format using
special functions (see
Chapter
10).