Yet another SQL Server enthusiast

2017-05-02

Moving LOB data to another filegroup

Filed under: Uncategorized — sa @ 9:19 AM

Note #1: This post is (more or less) a reminder for me.

Note #2: Original solutions are described here and here.

The problem (and a partial solution)

Sometimes, within a SQL Server database, we have to move data to another filegroup. In such cases, the simplest solution is to use CREATE INDEX statement with the ON NewFileGroup clause and also with DROP_EXISTING = ON thus:

image

This solution is available for both clustered and non-clustered indexes. In this cases, SQL Server will rebuild current index using the new filegroup (ON NewFileGroup). The drawback of this solution is that Database Engine will not move also LOB data (Large OBject data types include: old data types [n]text, image but also the new data types [n]varchar(max), varbinary(max), xml, CLR, geometry, geography, sql_variant) to new filegroup. The [main] reason of this behavior can be seen in CREATE TABLE syntax:

image

The CREATE TABLE statement includes the TEXTIMAGE_ON clause what allows us to define which filegroup should be used to store LOB data. Above solution will move only row data without moving LOB data (pages) into new filegroup. So, LOB data remain within old (TEXTIMAGE_ON) filegroup.

Note #3: For heap tables, one solution is to create a clustered index on the new filegroup and then drop it. After dropping clustered index,  the LOB data remain [stored] within new filegroup.

Final solution

The final solution is to recreate clustered index using a partition scheme. In this case, the behavior of SQL Server Database Engine is to move to target filegroup(s) (defined by partition scheme) both ROW and LOB data pages:

image

In order to create partition scheme we need a partition function. The main difference between original solution and this one is that in this case, I’ve used an empty partition function.

Demo

Initial status of ProductPhoto table (AdventureWorks2014 database):

image

Partition function (empty partition scheme), partition scheme (with all “partitions” assigned to [UserObjects] filegroup) + ProductPhoto table is recreated using the new partition scheme:

image

After these operations, the new status of ProductPhoto table is

image

but current table is partitioned. In order to remove partitioning (partition scheme) of current table the simplest solution is to recreate (again) the main table structure (clustered index) on the new filegroup:

image

And the final status of ProductPhoto table (unpartitioned) is:

image

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: