0

I want to change the FileGroup of a table for FG01 to FG03 . but in properties of table there is the Regular Data Space Specification and Text/Image Filegroup which have the list of FileGroups, what are the differences?

Improve this question

1 Answer 1

Reset to default
1

Regular data is in-row data and it is located on the filegroup listed in Regular Data Space Specification, LOB data is located on a Text/Image Filegroup that can be different from a filegroup where in-row data is located.

You specify these filegroups when create a table that contains LOB data:

CREATE TABLE ...
[ ON { partition_scheme_name ( partition_column_name )   
           | filegroup1   
           | "default" } ]   
    [ TEXTIMAGE_ON { filegroup2 | "default" } ]

Here filegroup1 is for regular data and filegroup2 is for LOB data.

TEXTIMAGE_ON { filegroup| "default" }

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

TEXTIMAGE_ON is not allowed if there are no large value columns in the table. TEXTIMAGE_ON cannot be specified if partition_scheme is specified. If "default" is specified, or if TEXTIMAGE_ON is not specified at all, the large value columns are stored in the default filegroup. The storage of any large value column data specified in CREATE TABLE cannot be subsequently altered.

CREATE TABLE (Transact-SQL)

Note the last cited phrase in bold. If you have LOB data and think that you can just rebuild your table on another filegroup, your LOB data will not be moved at all.

Improve this answer
2
  • you said "The storage of any large value column data specified ..." Do you mean if I have very heavy table with a file of Image type ,I can not change the FileGroup of Text/Image Filegroup of that table from Primary to Secondary FileGroup?
    – nnmmss
    Commented Nov 14, 2017 at 11:24
  • 1
    That was not me to say, that is documentation cite (the corresponding link is right below the cite). Yes this means that you cannot alter it subsequently, i.e. there is no command that will move LOB data to another FG while you can do it with regular data. Maybe SSMS will do it, but it will do it by recreating the whole table and inserting your data in that new table (with further rename and drop of the old table)
    – sepupic
    Commented Nov 14, 2017 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.