text columns are variable-length columns that can hold up to 2,147,483,647 (231 - 1) bytes of printable characters.
The variable-length unitext datatype can hold up to 1,073,741,823 Unicode characters (2,147,483,646 bytes).
image columns are variable-length columns that can hold up to 2,147,483,647 (231 - 1) bytes of raw binary data.
A key distinction between text and image is that text is subject to character-set conversion if you are not using the default character set of Adaptive Server default. image is not subject to character-set conversion.
Define a text, unitext, or image column as you would any other column, with a create table or alter table statement. text, unitext, or image datatype definitions do not include lengths. text, unitext, and image columns do permit null values. Their column definition takes the form:
column_name {text | image | unitext} [null]
For example, the create table statement for the author’s blurbs table in the pubs2 database with a text column, blurb, that permits null values, is:
create table blurbs (au_id id not null, copy text null)
This example creates a unitext column that allows null values:
create table tb (ut unitext null)
To create the au_pix table in the pubs2 database with an image column:
create table au_pix (au_id char(11) not null, pic image null, format_type char(11) null, bytesize int null, pixwidth_hor char(14) null, pixwidth_vert char(14) null)
Adaptive Server stores text, unitext, and image data in a linked list of data pages that are separate from the rest of the table. Each text, unitext, or image page stores one logical page size worth of data (2, 4, 8, or 16K). All text, unitext, and image data for a table is stored in a single page chain, regardless of the number of text, unitext, and image columns the table contains.
You can place subsequent allocations for text, unitext, and image data pages on a different logical device with sp_placeobject.
image values that have an odd number of hexadecimal digits are padded with a leading zero (an insert of “0xaaabb” becomes “0x0aaabb”).
You can use the partition option of the alter table command to partition a table that contains text, unitext, and image columns. Partitioning the table creates additional page chains for the other columns in the table, but has no effect on the way the text, unitext, and image columns are stored.
You can use unitext anywhere you use the text datatype, with the same semantics. unitext columns are stored in UTF-16 encoding, regardless of the Adaptive Server default character set.
