text, image, and unitext Datatypes

text columns are variable-length columns that can hold up to 2,147,483,647 (2³¹ - 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 (2³¹ - 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 SAP ASE. 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]

create table blurbs
(au_id id not null,
copy text null)

create table tb (ut unitext null)

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)

The SAP ASE 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 SAP ASE default character set.

• Data Structures Used for Storing text, unitext, and image Data
  When you allocate text, unitext, or image data, a 16-byte text pointer is inserted into the row you allocated. Part of this text pointer refers to a text page number at the head of the text, unitext, or image data. This text pointer is known as the first text page.
• Initialize text, unitext, and image Columns
  text, unitext, and image columns are not initialized until you update them or insert a non-null value. Initialization allocates at least one data page for each non-null text, unitext, or image data value. It also creates a pointer in the table to the location of the text, unitext, or image data.
• Save Space by Allowing NULL
  To save storage space for empty text, unitext, or image columns, define them to permit null values and insert nulls until you use the column. Inserting a null value does not initialize a text, unitext, or image column and, therefore, does not create a text pointer or allocate storage.
• Obtain Information from sysindexes
  Each table with text, unitext, or image columns has an additional row in sysindexes that provides information about these columns. The name column in sysindexes uses the form “tablename.” The indid is always 255.
• Using readtext and writetext
  Before you can use writetext to enter text data or readtext to read it, you must initialize the text column.
• Determine How Much Space a Column Uses
  sp_spaceused provides information about the space used for text data as index_size.
• Restrictions on text, image, and unitext Columns
  You cannot use text, image, or unitext columns in some places.
• Selecting text, unitext, and image Data
  text, unitext, and image values can be quite large. When the select list includes text and image values, the limit on the length of the data returned depends on the setting of the @@textsize global variable, which contains the limit on the number of bytes of text or image data a select returns.
• Converting text and image Datatypes
  You can explicitly convert text values to char, unichar, varchar, and univarchar, and image values to binary or varbinary with the convert function, but you are limited to the maximum length of the character and binary datatypes, which is determined by the maximum column size for your server’s logical page size.
• Converting to or from Unitext
  You can implicitly convert any character or binary datatype to unitext, as well as explicitly convert to and from unitext to other datatypes. The conversion result, however, is limited to the maximum length of the destination datatype.
• Pattern Matching in text Data
  Use the patindex function to search for the starting position of the first occurrence of a specified pattern in a text, unitext, varchar, univarchar, unichar, or char column. The % wildcard character must precede and follow the pattern (except when you are searching for the first or last character).
• Duplicate Rows
  The pointer to the text, image, and unitext data uniquely identifies each row. Therefore, a table that contains text, image, and unitext data does not contain duplicate rows unless there are rows in which all text, image, and unitext data is NULL. If this is the case, the pointer has not been initialized.
• Using Large Object text, unitext, and image Datatypes in Stored Procedures
  The SAP ASE server allows you to declare a large object (LOB) text, image, or unitext datatype for a local variable, and pass that variable as an input parameter to a stored procedure, as well as prepare SQL statements that include LOB parameters.
• Standards and Compliance
  ANSI SQL – Compliance level: The text, image, and unitext datatypes are Transact-SQL extensions.