When researching a question from one of the students in my class last week, I was reading the documentation for CREATE TABLE about storing LOB columns at http://msdn.microsoft.com/en-us/library/ms174979.aspx. For this discussion LOB columns includes text, image, ntext, xml and the MAX columns when they are over 8000 bytes and stored outside the regular data row. I knew that SQL Server gives us the capability of storing LOB columns in a separate filegroup with the TEXTIMAGE_ON clause, but I was surprised at one thing the docs mentioned.
The documentation says:
TEXTIMAGE_ON { filegroup | "default" }
Are keywords that indicate that the text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns 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.
The way I read this highlighted sentence is that if you don’t put the LOB data on a particular filegroup, it will always go on the default filegroup. The default filegroup has a specific meaning for SQL Server. It is the filegroup where all objects are placed if you don’t use the ON clause to put them on a specific filegroup. By default, the default filegroup is the PRIMARY filegroup, but with the ALTER DATABASE / MODIFY FILEGROUP command, you can change the default filegroup.
I did not think that what this sentence was saying was true, so I decided to test it.
I first created a new database with three filegroups. The PRIMARY filegroup is the default filegroup, because I am not changing the default to be anything else. The two additional filegroups are FG1 and FG2.
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TestFGs')
DROP DATABASE TestFGs;
GO
CREATE DATABASE TestFGs
ON PRIMARY
(NAME = Data ,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestFGPrimary.mdf'
, SIZE = 10 MB
, MAXSIZE = 1 GB
, FILEGROWTH = 25%),
FILEGROUP FG1
(NAME = FG1_data ,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG1_data.mdf'
, SIZE = 10 MB
, MAXSIZE = 1 GB
, FILEGROWTH = 25%),
FILEGROUP FG2
(NAME = FG2_data ,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG2_data.mdf'
, SIZE = 10 MB
, MAXSIZE = 1 GB
, FILEGROWTH = 25%)
LOG ON
(NAME = TestFG_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestFG_log.ldf'
, SIZE = 20 MB
, MAXSIZE = 2 GB
, FILEGROWTH = 25%);
GO
I then use the new database, and create three tables, each with the same column definitions. One of the column is a TEXT column which will be stored separately from the regular data rows unless I specify otherwise.
The first table does not specify any filegroups so everything should go on the default filegroup. The second table specifies a filegroup for the table (FG1) but not for the LOB data. The third table specifies a filegroup for the table (FG1) and separate filegroup for the LOB data (FG2).
To determine where the data has been stored, I examine the sys.allocation_units view. I need to join it to sys.partitions in order to get the corresponding object_id. The function object_name will give me the name of the table, and the function filegroup_name will translate the column data_space_id into a filegroup name.
USE TestFGs
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'bigrows')
DROP TABLE bigrows;
GO
CREATE TABLE bigrows
(a char(1000),
b varchar(8000),
c text );
GO
INSERT INTO bigrows
SELECT REPLICATE('a', 1000), REPLICATE('b', 1000),
REPLICATE('c', 50);
go
SELECT OBJECT_NAME(object_id) as object_name, FILEGROUP_NAME(data_space_id) as FG_name, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE OBJECT_NAME(object_id) = 'bigrows';
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'bigrows2')
DROP TABLE bigrows2;
GO
CREATE TABLE bigrows2
(a char(1000),
b varchar(8000),
c text )
ON FG1;
GO
INSERT INTO bigrows2
SELECT REPLICATE('a', 1000), REPLICATE('b', 1000),
REPLICATE('c', 50);
go
SELECT OBJECT_NAME(object_id) as object_name, FILEGROUP_NAME(data_space_id) as FG_name, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE OBJECT_NAME(object_id) LIKE 'bigrows%';
GO
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'bigrows3')
DROP TABLE bigrows3;
GO
CREATE TABLE bigrows3
(a char(1000),
b varchar(8000),
c text )
ON FG1 TEXTIMAGE_ON FG2;
GO
INSERT INTO bigrows3
SELECT REPLICATE('a', 1000), REPLICATE('b', 1000),
REPLICATE('c', 50);
GO
SELECT OBJECT_NAME(object_id) as object_name, FILEGROUP_NAME(data_space_id) as FG_name, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE OBJECT_NAME(object_id) like 'bigrows%';
And here are my results. The first table, bigrows1 has both its regular rows and its LOB data on the default filegroup, PRIMARY. You might also notice that an allocation unit for row-overflow data was created, even though we didn’t have any data that needed that space.
The second table, bigrows2, has all its data on FG1 even though I just specified to put the table there. The LOB data is stored with the rest of the table, if I don’t specifically place the LOB data on a different filegroup.
Only for the third table, bigrows3, is the LOB data stored separately, and only because I specifically used the TEXTIMAGE_ON clause when creating the table.
So now you know.
~Kalen