Understanding the TEXTIMAGE_ON Clause in SQL Server

What is TEXTIMAGE_ON?

In SQL Server, TEXTIMAGE_ON is a clause used in CREATE TABLE statements to specify the filegroup for storing large-value columns such as text, images, XML, and variable-length strings with maximum size. According to official documentation, it allows these columns to be allocated to designated filegroups, enabling flexible data management and storage optimization. By default, if not specified or set to "default", these columns are stored in the default filegroup.

Supported Data Types

TEXTIMAGE_ON applies to various large-value data types, including:

• text
• ntext
• image
• xml
• varchar(max)
• nvarchar(max)
• varbinary(max)
• CLR user-defined types (e.g., geometry and geography)

Syntax and Usage

The syntax for TEXTIMAGE_ON is: TEXTIMAGE_ON { filegroup | "default" }. For example, the following code creates a table with large-value columns stored in a custom filegroup CUSTOM:
CREATE TABLE MyTable (id INT, data VARCHAR(MAX)) ON [PRIMARY] TEXTIMAGE_ON [CUSTOM];
If unspecified, as in CREATE TABLE ... ON [PRIMARY] TEXTIMAGE_ON [PRIMARY], it defaults to the primary filegroup, which can be redundant.

Why Use TEXTIMAGE_ON?

The primary purpose of TEXTIMAGE_ON is to separate data storage. By storing core relational data (typically smaller) in the primary filegroup and large-value columns (e.g., documents or media files) in a secondary filegroup, benefits include:

• Improved performance: Distributes I/O load.
• Flexible management: Enables independent backup, restore, or replication strategies.
• Storage optimization: Applies specific storage techniques for different data types.

Key Considerations

When using TEXTIMAGE_ON, note:

• It is only allowed if the table has large-value columns.
• Cannot be specified alongside partition schemes (<partition_scheme>).
• Once set, the storage location for large-value columns cannot be altered.
• When using "default", ensure the QUOTED_IDENTIFIER option is ON and delimit it with quotes or brackets.