What is TEXTIMAGE_ON [PRIMARY]?

Question

I worked on many tables, and all of this had the following:

CREATE TABLE Persons( [id] [int] IDENTITY(1,1) NOT NULL, [modified_on] [datetime] NULL, [modified_by] [varchar](200) NULL, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

What is TEXTIMAGE_ON [PRIMARY] in SQL Server / Transact-SQL?

Answer 1

From MSDN

TEXTIMAGE_ON {filegroup | "default"}

Indicates that text, ntext, image, xml, varchar (max), nvarchar (max), varbinary (max) and CLR user type columns (including geometry and geography) are stored on the specified filegroup.

TEXTIMAGE_ON is not allowed if large columns do not have a table. TEXTIMAGE_ON cannot be specified if <partition_scheme> specified. If "default" is specified or if TEXTIMAGE_ON is not generally speaking, columns of large values ​​are stored in the standard filegroup. The storage of any column data of a large value specified in CREATE TABLE cannot be subsequently changed.

NOTE. In this context, the default value is not a keyword. It is the default filegroup identifier and should be limited, as in TEXTIMAGE_ON "default" or TEXTIMAGE_ON [default]. If "default" is specified, the QUOTED_IDENTIFIER option should be enabled for the current session. This is the default value.

Answer 2

Given that the format

 CREATE TABLE TableName(...) TEXTIMAGE_ON { filegroup | "default" } 

textimage applies to all types of large / unlimited field types: text, ntext, image, xml, varchar (max), nvarchar (max), varbinary (max) and CLR custom column types (including geometry and geography)

Then you need to know what files and filegroups are.

From the MSDN @ https://msdn.microsoft.com/en-us/library/ms189563.aspx entry

 File ------ At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes. Filegroups ------ Every database has a primary filegroup. This filegroup contains the primary data file and any secondary files that are not put into other filegroups. User-defined filegroups can be created to group data files together for administrative, data allocation, and placement purposes. 

So,

 CREATE TABLE ... ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

It would seem somewhat redundant, as he says that the mentioned columns with large text values ​​should be stored in the main filegroup, which is actually the default action.

Assuming there is a custom filegroup named CUSTOM, you will probably write something like this:

 CREATE TABLE ... ON [PRIMARY] TEXTIMAGE_ON [CUSTOM] 

You must create a user file group to store a large binary file or text, in which case information about normal values ​​will be placed in the data file in the main file group, while the corresponding "large" fields will be stored in a physically separate data file (in the secondary user file group).

You would do this in such a way that you could separate the main relational datamodel (which would supposedly be relatively small in terms of disk space) from large fields (which would require proportionally more disk space) - to allow for separate archiving or replication strategies that should apply to each filegroup.

Answer 3

If you don't have large text columns, i.e. text, ntext, image, xml, varchar (max), nvarchar (max), varbinary (max) and CLR, you can simply use:

 CREATE TABLE Persons( [id] [int] IDENTITY(1,1) NOT NULL, [modified_on] [datetime] NULL, [modified_by] [varchar](200) NULL,)ON [PRIMARY]