This is my first post. My goal with this post is to share how I moved a subset of the tables in my database to another filegroup. Why would you want to do this, you ask? A few possible use-cases:
- You want to move a set of tables to a different drive
- You like playing with SQL Server Files and Filegroups
- You want to move some tables out-of a database, but are not ready to drop them from their source DB.
The third option was my reason for researching it, but obviously you may have other reasons yet.
My overall approach to the problem above was to:
- Create a new filegroup
- Create a file in that filegroup
- Move the tables, online where possible (SQL Server Enterprise is required), to the new filegroup
- Flip that filegroup to READ_ONLY mode… unfortunately, I found out while doing this that this step requires that nobody else be in the database… I ultimately flipped the database to SINGLE_USER mode after-hours. This may not be an easy option for you.
- Using SSMS and the Import/Export Data Wizard (SSIS under-the-covers), I copied the data to its new destination where I had earlier prepared empty tables
I was only moving a sub-set of the tables… luckily for me, they all had the same prefix in their table name, so it was easy for me to grab them programmatically. It’s important to note that running my script doesn’t do anything to your database… rather it generates TSQL commands for you to review and, optionally, run.
The Issue
I wanted to move the tables… every part of them. I wanted my code to account for heaps, clustered indexes, and non-clustered indexes. I found some very useful links (included before) which did part of what I wanted… but not everything. I also wanted to accommodate as many index options as possible (and not lose settings like fill factor, filters, and ALLOW_TABLE_LOCK options). This is my first attempt, and I’ve tried to note exceptions to what I’ve tested.
The Approach
I’m sure I’ll catch a lot of flack for the iterative nature of my script… I’d normally be the first one to offer criticism! As it turns out, it’s very difficult to build a set of things into a single string (eg, building a set of index columns into a command). I found a few alternatives (including a very clever use of the STUFF() function and some XML generation… but I felt that was unintuitive and it seemed “hacky” to me. In the end, I make heavy use of WHILE loops. In TSQL. I know. Please feel free to disagree with me on this, but this is the choice I made in the end.
The Demo Setup
CREATE DATABASE FileGroupTesting;
GO
USE FileGroupTesting;
--Heap
CREATE TABLE [aHeap](id int, name nvarchar(50))
--Heap w/NC
CREATE TABLE [aHeapWithNCs](id int, name nvarchar(50))
CREATE NONCLUSTERED INDEX [HeapsNC] ON [aHeapWithNCs](name desc)
--Heap w/PK
CREATE TABLE [heapWithPK](id int primary key NONCLUSTERED, name nvarchar(50))
--Clustered no PK
CREATE TABLE [ClusteredNoPK] (id int, name nvarchar(50))
CREATE CLUSTERED INDEX [PK_ClusteredNoPK] ON [ClusteredNoPK](id)
--Clustered no PK with NCs
CREATE TABLE [ClusteredNoPK2] (id int, name nvarchar(50))
CREATE CLUSTERED INDEX [PK_ClusteredNoPK2] ON [ClusteredNoPK2](id)
CREATE NONCLUSTERED INDEX [ClusteredNoPKNC] ON [ClusteredNoPK2] (name desc)
--Clustered with PK
CREATE TABLE [ClusteredPK] (id int primary key clustered, name nvarchar(50))
--Clustered with PK with NCs
CREATE TABLE [ClusteredPK2] (id int primary key clustered, name nvarchar(50))
CREATE NONCLUSTERED INDEX [IX_ClusteredPK2] ON [ClusteredPK2] (name, id desc)
--Heap w/Unique
CREATE TABLE [HeapWithUnique](id int , name nvarchar(50))
ALTER TABLE [HeapWithUnique] ADD CONSTRAINT [UC_HeapWithUnique] UNIQUE NONCLUSTERED(name)
CREATE UNIQUE NONCLUSTERED INDEX [UC_HeapWithUnique2] ON [HeapWithUnique] (name)
--PK/Clustered w/Unique
CREATE TABLE [ClustWithUnique](id int , name nvarchar(50))
ALTER TABLE [ClustWithUnique] ADD CONSTRAINT [UC_ClustWithUnique] UNIQUE NONCLUSTERED(name)
CREATE UNIQUE NONCLUSTERED INDEX [UC_ClustWithUnique2] ON [ClustWithUnique] (name)
--PK/Clustered w/filter
CREATE TABLE [ClusteredFilter] (id int primary key clustered, name nvarchar(50))
CREATE NONCLUSTERED index [ix_clusteredfilter] on [ClusteredFilter](name) WHERE Name > 'M'
--PK/clustered w/included
CREATE TABLE [ClusteredIncluded] (id int primary key clustered, name nvarchar(50), isActive bit)
CREATE NONCLUSTERED index [IncludedInd] on [ClusteredIncluded] (name) include(isActive)
--PK/clustered w/desc
CREATE TABLE [ClusteredDesc] (id int not null, name nvarchar(50))
ALTER TABLE [ClusteredDesc] ADD CONSTRAINT [DescConstraint] PRIMARY KEY CLUSTERED (ID desc)
--Clustered with two cols
CREATE TABLE [ClusteredWithTwoCols] (id int not null, name nvarchar(50) not null)
ALTER TABLE [ClusteredWithTwoCols] ADD CONSTRAINT [PK_ClusteredWithTwoCols] PRIMARY KEY CLUSTERED (ID, name desc)
--Heap with an uncool name
CREATE TABLE [Table With A Bad Name] (id int)
Check Current Filegroup Layout
Of course, as we didn’t specify any filegroup options, we have SQL’s default: a single filegroup called [PRIMARY] with everything on it. We can verify this by running the following query:
SELECT
fg.name AS [FileGroup]
,fg.is_read_only
,s.name AS [Schema]
,o.name AS [TABLE]
,i.index_id
,i.name AS [Index]
,i.[type]
,i.type_desc AS [IndexType]
FROM sys.objects o
INNER JOIN [sys].[schemas] [s]
ON [o].[schema_id]=[s].[schema_id]
INNER JOIN [sys].[indexes] [i]
ON [o].[object_id]=[i].[object_id]
INNER JOIN [sys].[filegroups] [fg]
ON [i].[data_space_id]=[fg].[data_space_id]
WHERE 1=1
AND [o].[type]='U'
AND [o].[is_ms_shipped]=0
--AND [s].[name]='dbo'
--AND [o].[name] LIKE 'TABLEPrefix%'
ORDER BY [fg].[name], [s].[name], [o].[name], [i].[index_id]
Let’s start by setting up our new filegroup and creating a file in it:
ALTER DATABASE [FileGroupTesting] ADD FILEGROUP [FGTest]
--Be sure to replace the file path below with a valid directory on your system!
ALTER DATABASE [FileGroupTesting]
ADD FILE
( NAME = FileGroupTest,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\FileGroupTest.ndf'
)
TO FILEGROUP [FGTest]
The Solution
Next, we can use the script below (after changing to the appropriate database context and modifying any of the “setable” parameters.
/*
* Author: Ryan Snee
* Version: 1.0
* Date 20131015
*
* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
* License Details: http://creativecommons.org/licenses/by-sa/3.0/deed.en_US
*
* LIMITATIONS
* If an index is partitioned, it comes off the partition to go be put onto a filegroup
* Not sure how compression is handled... I assume we lose our compression settings, as they are per index, per partition
* Statistics_norecompute setting is defaulted back to OFF (should rarely be ON anyway, but something to be aware of)
* Assumes that no heap has a column called [TempPK]
* Does not account for ColumnStore Indexes
* Not tested with indexed views
Does handle:
Online Index Rebuilds, where possible
Filtered Indexes
*/
SET NOCOUNT ON;
DECLARE @LoopSafetyBefore int,
@LoopSafetyAfter int,
@LoopSafetyInternalBefore int,
@LoopSafetyInternalAfter int,
@Execute bit,
@CurrentObject int,
@CurrentIndex int,
@IndexColID int,
@colListBase nvarchar(max),
@colListInclude nvarchar(max),
@cmd nvarchar(max),
@TryOnlineRebuild bit,
@NewFG sysname,
@TableFilter sysname
--
set @NewFG='FGTest'
set @TryOnlineRebuild=1
set @TableFilter='%'
--
------------------
--1) Prep work----
------------------
if object_id('tempdb..#Indexes') is not null
drop table #Indexes
if object_id('tempdb..#IndexDetail') is not null
drop table #IndexDetail
CREATE TABLE #Indexes (
ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
SchemaName sysname,
ObjectName sysname,
ObjectID int,
IndexName sysname null,
IndexID int,
IndexType int,
Is_Unique bit,
Is_Primary bit,
Type_Desc nvarchar(60),
Is_Padded bit,
Ignore_Dup_Key bit,
Allow_Row_Locks bit,
Allow_Page_Locks bit,
Fill_Factor tinyint,
isDisabled bit,
FileGroupName sysname,
Has_Filter bit,
Filter_Definition nvarchar(max),
--key_ordinal tinyint
LOBColumns int,
isDone bit,
Cmd nvarchar(max)
)
CREATE TABLE #IndexDetail (
ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
SchemaName sysname,
ObjectName sysname,
ObjectID int,
IndexName sysname null,
IndexID int,
IndexType int,
Is_Unique bit,
Is_Primary bit,
Type_Desc nvarchar(60),
Is_Padded bit,
Ignore_Dup_Key bit,
Allow_Row_Locks bit,
Allow_Page_Locks bit,
Fill_Factor tinyint,
isDisabled bit,
Is_Descending_Key bit,
ColumnName sysname null,
Is_Included_Column bit,
FileGroupName sysname,
Has_Filter bit,
Filter_Definition nvarchar(max),
key_ordinal tinyint,
isDone bit default(0)
)
--First I get info on all of the indexes we need to move
--This includes the columns that make up the index, if applicable
--The set includes 1 row per index per column it includes
INSERT INTO #IndexDetail
SELECT s.name as [SchemaName]
,o.Name as ObjectName
,o.object_id as ObjectID
,si.Name as IndexName
,si.index_id
,si.type
,si.Is_Unique
,si.is_primary_key
,si.Type_Desc
,si.Is_Padded
,si.Ignore_Dup_Key
,si.Allow_Row_Locks
,si.Allow_Page_Locks
,si.Fill_Factor
,si.is_disabled
,sic.Is_Descending_Key
,sc.Name as ColumnName
,sic.Is_Included_Column
,sf.Name as FileGroupName
,si.has_filter as Has_Filter
,si.filter_definition as Filter_Definition
,sic.key_ordinal
,0 as [isDone]
FROM
sys.Objects o
INNER JOIN sys.Indexes si
ON o.Object_Id = si.Object_id
left join sys.schemas s
on o.schema_id=s.schema_id
INNER JOIN sys.FileGroups sf
ON sf.Data_Space_Id = si.Data_Space_Id
LEFT JOIN sys.Index_columns sic
ON si.Object_Id = sic.Object_Id
AND si.Index_id = sic.Index_id
LEFT JOIN sys.Columns sc
ON sic.Column_Id = sc.Column_Id
and sc.Object_Id = sic.Object_Id
WHERE
o.type='U'
and o.is_ms_shipped=0
AND o.Name LIKE COALESCE(@TableFilter, '%')
ORDER BY ObjectName, IndexName, sic.Key_Ordinal
--From the #IndexDetail table, I extract a set containing just 1 row per index
INSERT INTO #Indexes
(SchemaName, ObjectName, ObjectID, IndexID, IndexName, IndexType, is_Unique, is_primary,Type_Desc, is_padded, ignore_dup_key, allow_row_locks,allow_page_locks,fill_factor, isDisabled, FileGroupName, has_filter,filter_definition, LOBColumns, isDone, Cmd)
SELECT DISTINCT
SchemaName, ObjectName, ObjectID, IndexID, IndexName, IndexType, is_Unique, is_primary,Type_Desc, is_padded, ignore_dup_key, allow_row_locks,allow_page_locks,fill_factor, isDisabled, FileGroupName,has_filter,filter_definition, 0, 0, N''
FROM #IndexDetail;
--Find indexes that include LOB data
--Calculate how many LOB columns each index has and write it to the LobColumns column of #Indexes
WITH IndexesWithLOBs AS (
SELECT ObjectID, IndexID, COUNT(*) AS LOBColumns
FROM #Indexes i
LEFT JOIN sys.index_columns ic --Grabbing INCLUDEd columns
on i.ObjectID=ic.object_id
AND i.IndexID=ic.index_id
left join sys.columns c
on i.ObjectID=c.object_id
AND ic.column_id=c.column_id
left join sys.types t
on c.system_type_id=t.user_type_id
WHERE 1=1
AND i.Type_Desc='NONCLUSTERED'
and ((
t.system_type_id IN (231, 165, 167)-- nvarchar, varbinary, varchar
AND c.max_length = -1 -- (MAX)
)
OR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext
)
GROUP BY ObjectID, INDEXID
UNION ALL
SELECT ObjectID, IndexID, count(*) as LobColumns
FROM #Indexes i
left join sys.columns c --Grabbing columns that are part of the index
on i.ObjectID=c.object_id
left join sys.types t
on c.system_type_id=t.user_type_id
WHERE 1=1
AND (i.Type_Desc='CLUSTERED' OR i.Type_Desc='HEAP')
and ((
t.system_type_id IN (231, 165, 167)--nvarchar, varbinary, varchar
AND c.max_length = -1-- (MAX)
)
OR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext
)
GROUP BY ObjectID, IndexID
)
UPDATE I
SET i.LobColumns=LOB.LobColumns
FROM #Indexes i
INNER JOIN IndexesWithLOBs LOB
ON i.ObjectID=LOB.ObjectID
AND i.IndexID=LOB.IndexID
------------------------
--2) Process the Heaps--
------------------------
UPDATE #Indexes
SET [Cmd]=
'ALTER TABLE '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ADD [TempPK] bigint identity(1,1);'
+' CREATE CLUSTERED INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ([TempPK] ASC) WITH(ONLINE='
+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
THEN 'ON'
ELSE 'OFF'
END
+') ON ['+@NewFG+'];'
+'DROP INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' WITH(ONLINE='
+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0)
THEN 'ON'
ELSE 'OFF'
END
+');'
+'alter table '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' drop column [TempPK];'
--Handle when Page Locks were disabled
+CASE
WHEN [Allow_Page_Locks]=0
THEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET (ALLOW_Page_LOCKS = OFF );'+char(13)+char(10)
ELSE ''
END
--Handle when Row Locks were disabled
+CASE
WHEN [Allow_Row_Locks]=0
THEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET (ALLOW_Row_LOCKS = OFF ); '
ELSE ''
END
,isDone=1
WHERE Type_Desc='Heap'
AND isDone=0
--Assumption: Padding, ignore_duplicate, fillfactor, is_disabled, and filtering don't apply to a heap
------------------------------------------
--3) Process the Nonclustered Indexes-----
------------------------------------------
WHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0)
BEGIN
SET @CurrentObject= NULL
SET @CurrentIndex= NULL
select @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;
--Grab One Index
SELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;
set @colListBase=''
set @colListInclude=''
--For each NC Index, loop through every column in the core index to build up @colListBase
WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
BEGIN
select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex ORDER BY key_ordinal ASC
if @LoopSafetyInternalBefore=1
BEGIN
select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID
END
ELSE
BEGIN
select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID
END
UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID
select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter)
BEGIN
RAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT
BREAK
END
END
--Then Loop through the included columns to build up @colListInclude
WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
BEGIN
select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex
if @LoopSafetyInternalBefore=1
BEGIN
select @colListInclude=@colListInclude+quotename(ColumnName) FROM #IndexDetail WHERE ID=@IndexColID
END
ELSE
BEGIN
select @colListInclude=@colListInclude+quotename(ColumnName)+', ' FROM #IndexDetail WHERE ID=@IndexColID
END
UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID
select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter) BEGIN RAISERROR('Infinite Loop while adding included columns for object %i',11,1, @CurrentObject) WITH NOWAIT BREAK END END --Now that the column lists are unrolled, form the command SELECT @cmd= 'CREATE ' +CASE WHEN Is_Unique=1 THEN 'UNIQUE ' ELSE '' END +'NONCLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) +'('+@colListBase+')' +CASE WHEN len(@colListInclude)>0
THEN 'INCLUDE('+@colListInclude+')'
ELSE ''
END
+CASE WHEN Filter_Definition is not null
THEN ' WHERE '+Filter_Definition
ELSE ''
END
+' WITH(DROP_EXISTING=ON,PAD_INDEX='
+CASE
WHEN is_padded=1
THEN 'ON,'
ELSE 'OFF,'
END
+CASE
WHEN Fill_Factor>0
THEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','
ELSE ''
END
+'IGNORE_DUP_KEY='
+CASE
WHEN Ignore_Dup_Key=1
THEN 'ON,'
ELSE 'OFF,'
END
+'ALLOW_ROW_LOCKS='
+CASE
WHEN Allow_Row_Locks=1
THEN 'ON,'
ELSE 'OFF,'
END
+'ALLOW_PAGE_LOCKS='
+CASE
WHEN Allow_Page_Locks=1
THEN 'ON,'
ELSE 'OFF,'
END
+'ONLINE='
+CASE
WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
THEN 'ON'
ELSE 'OFF'
END
+') ON '+quotename(@NewFG)+';'
+CASE
WHEN isDisabled=1
THEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'
ELSE ''
END
FROM #Indexes
WHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex
--Write the command back to the table
UPDATE #Indexes
SET [Cmd]=@cmd, isDone=1
WHERE ObjectID=@CurrentObject
AND IndexID=@CurrentIndex
SELECT @LoopSafetyAfter=count(*)
FROM #Indexes
WHERE Type_Desc='NONCLUSTERED'
AND isDone=0;
if(@LoopSafetyBefore<=@LoopSafetyAfter)
BEGIN
RAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT
BREAK
END
END
----------------------------------------------------
--4) Repeat the pattern for Clustered Indexes-------
----------------------------------------------------
WHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0)
BEGIN
SET @CurrentObject= NULL
SET @CurrentIndex= NULL
select @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;
--Grab One Index
SELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;
set @colListBase=''
set @colListInclude=''
--For each Clustered Index, loop through every column in the core index
WHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )
BEGIN
select @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
SELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject AND IndexID=@CurrentIndex ORDER BY key_ordinal ASC
if @LoopSafetyInternalBefore=1
BEGIN
select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID
END
ELSE
BEGIN
select @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID
END
UPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID
select @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;
if(@LoopSafetyInternalBefore<=@LoopSafetyInternalAfter) BEGIN RAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT BREAK END END --Form the command SELECT @cmd= 'CREATE ' +CASE WHEN Is_Unique=1 THEN 'UNIQUE ' ELSE '' END +'CLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) +'('+@colListBase+')' +' WITH(DROP_EXISTING=ON,PAD_INDEX=' +CASE WHEN is_padded=1 THEN 'ON,' ELSE 'OFF,' END +CASE WHEN Fill_Factor>0
THEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','
ELSE ''
END
+'IGNORE_DUP_KEY='
+CASE
WHEN Ignore_Dup_Key=1
THEN 'ON,'
ELSE 'OFF,'
END
+'ALLOW_ROW_LOCKS='
+CASE
WHEN Allow_Row_Locks=1
THEN 'ON,'
ELSE 'OFF,'
END
+'ALLOW_PAGE_LOCKS='
+CASE
WHEN Allow_Page_Locks=1
THEN 'ON,'
ELSE 'OFF,'
END
+'ONLINE='
+CASE
WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)
THEN 'ON'
ELSE 'OFF'
END
+') ON '+quotename(@NewFG)+';'
+CASE
WHEN isDisabled=1
THEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'
ELSE ''
END
FROM #Indexes
WHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex
--Write the command back to the @Indexes table
UPDATE #Indexes
SET [Cmd]=@cmd, isDone=1
WHERE ObjectID=@CurrentObject
AND IndexID=@CurrentIndex
select @LoopSafetyAfter=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;
if(@LoopSafetyBefore<=@LoopSafetyAfter)
BEGIN
RAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT
BREAK
END
END
--select * from #IndexDetail
--select * FROM #Indexes
SELECT
FileGroupName
,SchemaName
,ObjectName
,IndexName
,IndexID
,IndexType
,Is_Unique
,is_Primary
,Type_Desc
,Is_Padded
,Ignore_Dup_Key
,Allow_Row_Locks
,Allow_Page_Locks
,Fill_Factor
,isDisabled
,Has_Filter
,Filter_Definition
,LOBColumns
,[Cmd]
FROM #Indexes
WHERE FileGroupName <> @NewFG
ORDER BY SchemaName, ObjectName
Remember, the script doesn’t change anything; rather it generates TSQL commands you can choose to run.
Running the Generated Commands and Verification
The final step would be to review and possibly run the commands in the last column of the table.
After doing so, we can run the query mentioned in the “Check Current Filegroup Layout” section above to verify that every index of every (user) table has moved to the new filegroup/file.
As a note, system tables are forever in the first, primary data file. It can be moved (an offline operation, unfortunately), but it cannot be removed. Attempting to do so will yield the following error:
Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
Changing the Filegroup to Read_Only
Now that my objective of moving a subset of the user tables to a new filegroup has been accomplished, I can continue on to step 4 of my approach:
use FileGroupTesting;
--You need to be the only one in the database. You may need to kick everyone else off:
alter database [FileGroupTesting] set single_user with rollback immediate;
--Make FileGroup ReadOnly
ALTER DATABASE [FileGroupTesting]
MODIFY FILEGROUP [FGTest] Read_only;
alter database [FileGroupTesting] set multi_user with rollback immediate;
SELECT
name
,is_read_only
,is_default
FROM sys.filegroups
The full script can be found here: MovingTablesToNewFG.txt.
That’s it! Please let me know if you found this script useful!
LLAP,
Ryan
A few sources I’d like to cite for contributing ideas:
- http://stackoverflow.com/questions/4237238/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server
- http://dba.stackexchange.com/questions/28872/move-primary-key-to-filegroup-sql-server-2012
- http://www.aaronlowe.net/archive/2007/09/move-heap-tables/
The text of this entire blog site (including all scripts) is under the copyright of Ryan Snee.You are free to reproduce it, as outlined by the Creative Commons Attribution-ShareAlike 3.0 Unported License.