Chapter 6: Using and Creating Datatypes

Character datatypes

Use the character datatypes to store strings consisting of letters, numbers, and symbols entered within single or double quotes. You can use the like keyword to search these strings for particular characters and the built-in string functions to manipulate their contents. Strings consisting of numbers can be converted to exact and approximate numeric datatypes with the convert function, and then used for arithmetic.

The char(n) datatype stores fixed-length strings, and the varchar(n) datatype stores variable-length strings, in single-byte character sets such as English. Their national character counterparts, nchar(n) and nvarchar(n), store fixed- and variable-length strings in multibyte character sets such as Japanese.The unichar and univarchar datatypes store Unicode characters, which are a constant size. You can specify the maximum number of characters with n or use the default column length of one character. For strings larger than the page size, use the text datatype.

**Table 6-3: Character datatypes**
Datatype	Stores
char(n)	Fixed-length data, such as social security numbers or postal codes
varchar(n)	Data, such as names, that is likely to vary greatly in length
unichar	Fixed-length Unicode data, comparable to char
univarchar	Unicode data that is likely to vary greatly in length, comparable to varchar
nchar(n)	Fixed-length data in multibyte character sets
nvarchar(n)	Variable-length data in multibyte character sets
text	Up to 2,147,483,647 bytes of printable characters on linked lists of data pages
unitext	Up to 1,073,741,823 Unicode characters on linked lists of data pages.

Adaptive Server truncates entries to the specified column length without warning or error, unless you set string_rtruncation on. See the set command in the Reference Manual for more information. The empty string, “ ”or ‘ ’, is stored as a single space rather than as NULL. Thus, “abc” + “ ” + “def” is equivalent to “abc def”, not to “abcdef”.

Fixed- and variable-length columns behave somewhat differently:

Data in fixed-length columns is blank-padded to the column length. For char and unichar datatypes, storage size is n bytes, (unichar = n*@@unicharsize); for nchar, n times the average national character length (@@ncharsize). When you create a char, unichar, or nchar column that allows nulls, Adaptive Server converts it to a varchar, univarchar, or nvarchar column and uses the storage rules for those datatypes. This is not true of char and nchar variables and parameters.
Data in variable-length columns is stripped of trailing blanks; storage size is the actual length of the data. For varchar or univarchar columns, this is the number of characters; for nvarchar columns, it is the number of characters times the average character length. Variable-length character data may require less space than fixed-length data, but it is accessed somewhat more slowly.

View this book as PDF