Length and storage size

Character variables strip the trailing spaces from strings when the variable is populated in a varchar column of a cursor.

Use n to specify the number of bytes of storage for char and varchar datatypes. For unichar, use n to specify the number of Unicode characters (the amount of storage allocated is 2 bytes per character). For nchar and nvarchar, n is the number of characters (the amount of storage allocated is n times the number of bytes per characer for the server’s current default character set).

If you do not use n to specify the length:

Entries shorter than the assigned length are blank-padded; entries longer than the assigned length are truncated without warning, unless the string_rtruncation option to the set command is set to on. Fixed-length columns that allow nulls are internally converted to variable-length columns.

Use n to specify the maximum length in characters for the variable-length datatypes, varchar(n), univarchar(n), and nvarchar(n). Data in variable-length columns is stripped of trailing blanks; storage size is the actual length of the data entered. Data in variable-length variables and parameters retains all trailing blanks, but is not padded to the defined length. Character literals are treated as variable-length datatypes.

Fixed-length columns tend to take more storage space than variable-length columns, but are accessed somewhat faster. Table 1-17 summarizes the storage requirements of the different character datatypes:

Table 1-17: Character datatypes

Datatype

Stores

Bytes of Storage

char(n)

Character

n

unichar(n)

Unicode character

n*@@unicharsize (@@unicharsize equals 2)

nchar(n)

National character

n * @@ncharsize

varchar(n)

Character varying

Actual number of characters entered

univarchar(n)

Unicode character varying

Actual number of characters * @@unicharsize

nvarchar(n)

National character varying

Actual number of characters * @@ncharsize

Determining column length with system functions

Use the char_length string function and datalength system function to determine column length:

When a char value is declared to allow NULL values, Adaptive Server stores it internally as a varchar.

If the min or max aggregate functions are used on a char column, the result returned is varchar, and is therefore stripped of all trailing spaces.