Function

text columns are variable-length columns that can hold up to 2,147,483,647 (2³¹ - 1) bytes of printable characters.

image columns are variable-length columns that can hold up to 2,147,483,647 (2³¹ - 1) bytes of hexadecimal-like data.

Defining a text or image column

You define a text or image column as you would any other column, with a create table or alter table statement. text and image datatype definitions do not include lengths. They do permit null values. The column definition takes the form:

column_name {text | image} [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)

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)

How Adaptive Server stores text and image data

Adaptive Server stores text and image data in a linked list of data pages that are separate from the rest of the table. Each text or image page stores one logical page size worth of data (2, 4, 8, or 16K). All text and image data for a table is stored in a single page chain, regardless of the number of text and image columns the table contains.

Putting additional pages on another device

You can place subsequent text and image data pages on a different logical device with sp_placeobject.

Zero padding

image values that have an odd number of hexadecimal digits are padded with a leading zero (an insert of “0xaaabb” becomes “0x0aaabb”).

Effect of partitioning on data storage

You can use the partition option of the alter table command to partition a table that contains text 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 and image columns are stored.