Text and Image Data

Text and image data consists of character or binary data stored and managed as a linked list of 2K data pages that appear as if they were stored in a table row. A text or image column contains NULL or a pointer (assigned at initialization) to the first text page for that entry, and thus to the entire text or image value. Although text and image datatype definitions do not include length, they do allow null values.

At least one 2K data page is allocated for each non-null text or image data value. To save storage space, you can insert null values until you use the text or image column. Note that any update (even an update that sets the value to NULL) to a text or image column initializes the column; that is, it assigns a valid text pointer and causes the first 2K data page to be allocated.

These are the text and image datatypes:

text

Is a variable-length datatype that can hold as many as 2 (31) - 1 (2,147,483,647) characters. The text definitions do not include a length, and text values must be surrounded by single quotation marks in an INSERT statement. You can use the LIKE keyword and wildcard characters with text data.

You can explicitly convert text data to char or varchar with the CONVERT function, but the limit is the maximum length of these datatypes, 255 bytes.

image

Is a variable-length datatype that can hold as many as 2 (31) - 1 (2,147,483,647) bytes of binary data. The image definitions do not include a length, and image values must be preceded by 0x in an INSERT statement. The image datatype cannot be used for variables or parameters in stored procedures.

You can explicitly convert image data to binary or varbinary with the CONVERT function, but the limit is the maximum length of these datatypes (255 bytes).

When image is data entered with an odd number of bytes (but less than 255), it is padded with a leading zero. For example, an insert of "0xaaabb" becomes "0x0aaabb". You cannot insert image values with an odd number of characters greater than 255 bytes.

Using text and image Datatypes

To enter text or image data, the column must first be initialized. Initialization causes a 2K data page to be allocated. Initialize the text or image column by providing non-null data with an INSERT statement or any value (including a NULL) with an UPDATE statement. Then use the READTEXT statement to read text or image data, the WRITETEXT statement to replace text or image data, and the UPDATETEXT statement to modify text or image data.

Using UPDATE to replace existing text and image data with NULL reclaims all allocated data pages except the first page, which remains available for WRITETEXT in the future. To release all storage for the row, first delete the row, and then re-insert the non-text or non-image data.

SELECT statements return text and image data up to the limit specified in the global variable @@TEXTSIZE. The SET statement (TEXTSIZE option) changes this limit on a session basis. The initial value of @@TEXTSIZE is 4K; the maximum value for @@TEXTSIZE is 2 (31) - 1 (2,147,483,647).

Text and image columns cannot be used:

• As local variables
• In ORDER BY, COMPUTE, and GROUP BY clauses
• In an index
• In a WHERE clause (with the exception of text data¾text data can be specified with the keyword LIKE in a WHERE clause)

Each table that contains text or image columns has a row in the sysindexes system table. The name column in sysindexes uses the form t<tablename>. The indid is always 255. These columns provide information about text storage: