Using text and image Data
Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use varchar(max), nvarchar(max) and varbinary(max) data types instead. For more information, see Using Large-Value Data Types.
Microsoft SQL Server 2005 stores character strings longer than 8,000 characters and binary data longer than 8,000 bytes in special data types named text and image. Unicode strings longer than 4,000 characters are stored in the ntext data type.
For example, a large text file (.txt) of customer information must be imported into your SQL Server database. This data should be stored as one piece of data rather than integrated into the multiple columns of your data tables. You can create a column with the text data type for this purpose. However, if you must store company logos currently stored as Tagged Image File Format (TIFF) images (.tif) that are 10 KB each, create a column with the image data type.
If the textual data to be stored is in Unicode format, use the ntext data type. For example, a form letter created for international customers is likely to contain international spellings and characters used in various different languages. Store this data in an ntext column.
Each text and ntext data value has a collation. Collations define attributes such as comparison rules and sensitivity to case or accenting. The collations for text values also specify a code page that defines the bit patterns used to represent each character. Each ntext value uses the Unicode code page, which is the same for all the collations. Each database has a default collation. When a text or ntext column is created, it is assigned the default collation of the database unless you assign a specific collation using the COLLATE clause. When two text or ntext values having different collations are combined or compared, collation precedence rules determine which collation is used for the operation.
Data in an image data is stored as a string of bits and is not interpreted by SQL Server. Any interpretation of the data in an image column must be made by the application. For example, an application could store data in an image column using a BMP, TIFF, GIF, or JPEG format. The application that reads the data from the image column must recognize the format of the data and display it correctly. All an image column does is provide a location to store the stream of bits that make up the image data value.
Using text in row to Store text, ntext, and image Values
Usually, text, ntext, or image strings are large, a maximum of 2GB, character or binary strings stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored.
With SQL Server, you can store small to medium text, ntext, and image values in a data row, thereby increasing the speed of queries accessing these values.
When the text, ntext, or image string is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or write the string. This makes reading and writing the text, ntext, or image in-row strings about as fast as reading or writing varchar, nvarchar, or varbinary strings.
To store text, ntext, or image strings in the data row, enable the text in row option using the sp_tableoption stored procedure.
sp_tableoption N'MyTable', 'text in row', 'ON'
Optionally, you can specify a maximum limit, from 24 through 7000 bytes, for the length of a text, ntext, and image string stored in a data row:
sp_tableoption N'MyTable', 'text in row', '1000'
If you specify 'ON' instead of a specific limit, the limit defaults to 256 bytes. This default value provides most of the performance benefits: It is large enough to ensure that small strings and the root text pointers can be stored in the rows but not so large that it decreases the rows per page enough to affect performance.
Although in general, you should not set the value below 72, you also should not set the value too high, especially for tables where most statements do not reference the text, ntext, and image columns or there are multiple text, ntext, and image columns.
You can also use sp_tableoption to turn the option off by specifying an option value of either 'OFF' or 0:
sp_tableoption N'MyTable', 'text in row', 'OFF'
See Also
Concepts
Other Resources
Data Types (Transact-SQL)
sp_tableoption (Transact-SQL)