{"id":25,"date":"2013-10-15T22:33:42","date_gmt":"2013-10-16T03:33:42","guid":{"rendered":"http:\/\/blog.sqlsnee.com\/?p=25"},"modified":"2020-02-18T18:29:58","modified_gmt":"2020-02-19T00:29:58","slug":"moving-a-set-of-tables-to-a-read-only-filegroup","status":"publish","type":"post","link":"https:\/\/blog.sqlsnee.com\/?p=25","title":{"rendered":"Moving a Set of Tables to a Read-Only Filegroup"},"content":{"rendered":"<p>This is my first post.&nbsp; My goal with this post is to share how I moved a subset of the tables in my database to another filegroup.&nbsp; Why would you want to do this, you ask?&nbsp; A few possible use-cases:<\/p>\n<ul>\n<li>You want to move a set of tables to a different drive<\/li>\n<li>You like playing with SQL Server Files and Filegroups<\/li>\n<li>You want to move some tables out-of a database, but are not ready to drop them from their source DB.<\/li>\n<\/ul>\n<p>The third option was my reason for researching it, but obviously you may have other reasons yet.<\/p>\n<p>My overall approach to the problem above was to:<\/p>\n<ol>\n<li>Create a new filegroup<\/li>\n<li>Create a file in that filegroup<\/li>\n<li>Move the tables, online where possible (SQL Server Enterprise is required), to the new filegroup<\/li>\n<li>Flip that filegroup to READ_ONLY mode&#8230; unfortunately, I found out while doing this that this step requires that nobody else be in the database&#8230; I ultimately flipped the database to SINGLE_USER mode after-hours.&nbsp; This may not be an easy option for you.<\/li>\n<li>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<\/li>\n<\/ol>\n<p>I was only moving a sub-set of the tables&#8230; luckily for me, they all had the same prefix in their table name, so it was easy for me to grab them programmatically.&nbsp; It&#8217;s important to note that running my script doesn&#8217;t <em>do<\/em> anything to your database&#8230; rather it generates TSQL commands for you to review and, optionally, run.<\/p>\n<h3>The Issue<\/h3>\n<p>I wanted to move the tables&#8230; every part of them.&nbsp; I wanted my code to account for heaps, clustered indexes, and non-clustered indexes.&nbsp; I found some very useful links (included before) which did <em>part<\/em> of what I wanted&#8230; but not everything.&nbsp; I also wanted to accommodate as many index options as possible (and not lose settings like fill factor, filters, and ALLOW_TABLE_LOCK options).&nbsp; This is my first attempt, and I&#8217;ve tried to note exceptions to what I&#8217;ve tested.<\/p>\n<h3>The Approach<\/h3>\n<p>I&#8217;m sure I&#8217;ll catch a lot of flack for the iterative nature of my script&#8230; I&#8217;d normally be the first one to offer criticism!&nbsp; As it turns out, it&#8217;s very difficult to build a set of things into a single string (eg, building a set of index columns into a command).&nbsp; I found a few alternatives (including a very clever use of the STUFF() function and some XML generation&#8230; but I felt that was unintuitive and it seemed &#8220;hacky&#8221; to me.&nbsp; In the end, I make heavy use of WHILE loops.&nbsp; In TSQL.&nbsp; I know.&nbsp; Please feel free to disagree with me on this, but this is the choice I made in the end.<\/p>\n<h3>The Demo Setup<\/h3>\n<pre lang=\"tsql\">CREATE DATABASE FileGroupTesting;\nGO\nUSE FileGroupTesting;\n\n--Heap\nCREATE TABLE [aHeap](id int, name nvarchar(50))\n--Heap w\/NC\nCREATE TABLE [aHeapWithNCs](id int, name nvarchar(50))\nCREATE NONCLUSTERED INDEX [HeapsNC] ON [aHeapWithNCs](name desc)\n--Heap w\/PK\nCREATE TABLE [heapWithPK](id int primary key NONCLUSTERED, name nvarchar(50))\n--Clustered no PK\nCREATE TABLE [ClusteredNoPK] (id int, name nvarchar(50))\nCREATE CLUSTERED INDEX [PK_ClusteredNoPK] ON [ClusteredNoPK](id)\n--Clustered no PK with NCs\nCREATE TABLE [ClusteredNoPK2] (id int, name nvarchar(50))\nCREATE CLUSTERED INDEX [PK_ClusteredNoPK2] ON [ClusteredNoPK2](id)\nCREATE NONCLUSTERED INDEX [ClusteredNoPKNC] ON [ClusteredNoPK2] (name desc)\n\n--Clustered with PK\nCREATE TABLE [ClusteredPK] (id int primary key clustered, name nvarchar(50))\n\n--Clustered with PK with NCs\nCREATE TABLE [ClusteredPK2] (id int primary key clustered, name nvarchar(50))\nCREATE NONCLUSTERED INDEX [IX_ClusteredPK2] ON [ClusteredPK2] (name, id desc)\n\n--Heap w\/Unique\nCREATE TABLE [HeapWithUnique](id int , name nvarchar(50))\nALTER TABLE [HeapWithUnique] ADD CONSTRAINT&nbsp; [UC_HeapWithUnique] UNIQUE NONCLUSTERED(name)\nCREATE UNIQUE NONCLUSTERED INDEX [UC_HeapWithUnique2] ON [HeapWithUnique] (name)\n\n--PK\/Clustered w\/Unique\nCREATE TABLE [ClustWithUnique](id int , name nvarchar(50))\nALTER TABLE [ClustWithUnique] ADD CONSTRAINT [UC_ClustWithUnique] UNIQUE NONCLUSTERED(name)\nCREATE UNIQUE NONCLUSTERED INDEX [UC_ClustWithUnique2] ON [ClustWithUnique] (name)\n\n--PK\/Clustered w\/filter\nCREATE TABLE [ClusteredFilter] (id int primary key clustered, name nvarchar(50))\nCREATE NONCLUSTERED index [ix_clusteredfilter] on [ClusteredFilter](name)&nbsp; WHERE Name &gt; 'M'\n\n--PK\/clustered w\/included\nCREATE TABLE [ClusteredIncluded] (id int primary key clustered, name nvarchar(50), isActive bit)\nCREATE NONCLUSTERED index [IncludedInd] on [ClusteredIncluded] (name) include(isActive)\n\n--PK\/clustered w\/desc\nCREATE TABLE [ClusteredDesc] (id int not null, name nvarchar(50))\nALTER TABLE [ClusteredDesc] ADD CONSTRAINT [DescConstraint] PRIMARY KEY CLUSTERED (ID desc)\n\n--Clustered with two cols\nCREATE TABLE [ClusteredWithTwoCols] (id int not null, name nvarchar(50) not null)\nALTER TABLE [ClusteredWithTwoCols] ADD CONSTRAINT [PK_ClusteredWithTwoCols] PRIMARY KEY CLUSTERED (ID, name desc)\n\n--Heap with an uncool name\nCREATE TABLE [Table With A Bad Name] (id int)<\/pre>\n<h3>Check Current Filegroup Layout<\/h3>\n<p>Of course, as we didn&#8217;t specify any filegroup options, we have SQL&#8217;s default: a single filegroup called [PRIMARY] with everything on it.&nbsp; We can verify this by running the following query:<\/p>\n<pre lang=\"tsql\">SELECT\n\t fg.name\t\t\tAS\t\t[FileGroup]\n\t,fg.is_read_only\n\t,s.name\t\t\t\tAS\t\t[Schema]\n\t,o.name\t\t\t\tAS\t\t[TABLE]\n\t,i.index_id\n\t,i.name\t\t\t\tAS\t\t[Index]\n\t,i.[type]\n\t,i.type_desc\t\t\tAS\t\t[IndexType]\nFROM sys.objects o\nINNER JOIN [sys].[schemas] [s]\n\tON [o].[schema_id]=[s].[schema_id]\nINNER JOIN [sys].[indexes] [i]\n\tON [o].[object_id]=[i].[object_id]\nINNER JOIN [sys].[filegroups] [fg]\n\tON [i].[data_space_id]=[fg].[data_space_id]\nWHERE 1=1\n\tAND [o].[type]='U'\n\tAND [o].[is_ms_shipped]=0\n\t--AND [s].[name]='dbo'\n\t--AND [o].[name] LIKE 'TABLEPrefix%'\nORDER BY [fg].[name], [s].[name], [o].[name], [i].[index_id]<\/pre>\n<p>Let&#8217;s start by setting up our new filegroup and creating a file in it:<\/p>\n<pre lang=\"tsql\">ALTER DATABASE [FileGroupTesting] ADD FILEGROUP [FGTest]\n--Be sure to replace the file path below with a valid directory on your system!\nALTER DATABASE [FileGroupTesting]\nADD FILE\n( NAME = FileGroupTest,\nFILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.SQL2012\\MSSQL\\DATA\\FileGroupTest.ndf'\n)\nTO FILEGROUP [FGTest]<\/pre>\n<h3>The Solution<\/h3>\n<p>Next, we can use the script below (after changing to the appropriate database context and modifying any of the &#8220;setable&#8221; parameters.<\/p>\n<pre lang=\"tsql\">\/*\n* Author: Ryan Snee\n* Version: 1.0\n* Date 20131015\n*\n* License: This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.\n* License Details: http:\/\/creativecommons.org\/licenses\/by-sa\/3.0\/deed.en_US\n*\n* LIMITATIONS\n* If an index is partitioned, it comes off the partition to go be put onto a filegroup\n* Not sure how compression is handled... I assume we lose our compression settings, as they are per index, per partition\n* Statistics_norecompute setting is defaulted back to OFF (should rarely be ON anyway, but something to be aware of)\n* Assumes that no heap has a column called [TempPK]\n* Does not account for ColumnStore Indexes\n* Not tested with indexed views\n\nDoes handle:\n Online Index Rebuilds, where possible\n Filtered Indexes\n\n*\/\n\nSET NOCOUNT ON;\nDECLARE @LoopSafetyBefore int,\n@LoopSafetyAfter int,\n\n@LoopSafetyInternalBefore int,\n@LoopSafetyInternalAfter int,\n@Execute bit,\n@CurrentObject int,\n@CurrentIndex int,\n@IndexColID int,\n@colListBase nvarchar(max),\n@colListInclude nvarchar(max),\n@cmd nvarchar(max),\n@TryOnlineRebuild bit,\n@NewFG sysname,\n@TableFilter sysname\n\n--\nset @NewFG='FGTest'\nset @TryOnlineRebuild=1\nset @TableFilter='%'\n--\n\n------------------\n--1) Prep work----\n------------------\n\n\tif object_id('tempdb..#Indexes') is not null\n\tdrop table #Indexes\n\n\tif object_id('tempdb..#IndexDetail') is not null\n\tdrop table #IndexDetail\n\n\tCREATE TABLE #Indexes (\n\t\tID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,\n\t\tSchemaName sysname,\n\t\tObjectName sysname,\n\t\tObjectID int,\n\t\tIndexName sysname null,\n\t\tIndexID int,\n\t\tIndexType int,\n\t\tIs_Unique bit,\n\t\tIs_Primary bit,\n\t\tType_Desc nvarchar(60),\n\t\tIs_Padded bit,\n\t\tIgnore_Dup_Key bit,\n\t\tAllow_Row_Locks bit,\n\t\tAllow_Page_Locks bit,\n\t\tFill_Factor tinyint,\n\t\tisDisabled bit,\n\t\tFileGroupName sysname,\n\t\tHas_Filter bit,\n\t\tFilter_Definition nvarchar(max),\n\t\t--key_ordinal tinyint\n\t\tLOBColumns int,\n\t\tisDone bit,\n\t\tCmd nvarchar(max)\n\n\t)\n\n\tCREATE TABLE #IndexDetail (\n\t\tID int IDENTITY(1,1) PRIMARY KEY CLUSTERED ,\n\t\tSchemaName sysname,\n\t\tObjectName sysname,\n\t\tObjectID int,\n\t\tIndexName sysname null,\n\t\tIndexID int,\n\t\tIndexType int,\n\t\tIs_Unique bit,\n\t\tIs_Primary bit,\n\t\tType_Desc nvarchar(60),\n\t\tIs_Padded bit,\n\t\tIgnore_Dup_Key bit,\n\t\tAllow_Row_Locks bit,\n\t\tAllow_Page_Locks bit,\n\t\tFill_Factor tinyint,\n\t\tisDisabled bit,\n\t\tIs_Descending_Key bit,\n\t\tColumnName sysname null,\n\t\tIs_Included_Column bit,\n\t\tFileGroupName sysname,\n\t\tHas_Filter bit,\n\t\tFilter_Definition nvarchar(max),\n\t\tkey_ordinal tinyint,\n\t\tisDone bit default(0)\n\t)\n\n\t--First I get info on all of the indexes we need to move\n\t--This includes the columns that make up the index, if applicable\n\t--The set includes 1 row per index per column it includes\n\tINSERT INTO #IndexDetail\n\tSELECT\t s.name as [SchemaName]\n\t\t\t,o.Name as ObjectName\n\t\t\t,o.object_id as ObjectID\n\t\t\t,si.Name as IndexName\n\t\t\t,si.index_id\n\t\t\t,si.type\n\t\t\t,si.Is_Unique\n\t\t\t,si.is_primary_key\n\t\t\t,si.Type_Desc\n\t\t\t,si.Is_Padded\n\t\t\t,si.Ignore_Dup_Key\n\t\t\t,si.Allow_Row_Locks\n\t\t\t,si.Allow_Page_Locks\n\t\t\t,si.Fill_Factor\n\t\t\t,si.is_disabled\n\t\t\t,sic.Is_Descending_Key\n\t\t\t,sc.Name as ColumnName\n\t\t\t,sic.Is_Included_Column\n\t\t\t,sf.Name as FileGroupName\n\t\t\t,si.has_filter as Has_Filter\n\t\t\t,si.filter_definition as Filter_Definition\n\t\t\t,sic.key_ordinal\n\t\t\t,0 as [isDone]\n\t FROM \n\t\tsys.Objects o\n\t\tINNER JOIN sys.Indexes si\n\t\t\tON o.Object_Id = si.Object_id\n\t\tleft join sys.schemas s\n\t\ton o.schema_id=s.schema_id\n\t\tINNER JOIN sys.FileGroups sf\n\t\t\tON sf.Data_Space_Id = si.Data_Space_Id\n\t\tLEFT JOIN  sys.Index_columns sic\n\t\t\tON si.Object_Id = sic.Object_Id\n\t\t\t\tAND si.Index_id = sic.Index_id\n\t\tLEFT JOIN  sys.Columns sc\n\t\t\tON sic.Column_Id = sc.Column_Id\n\t\t\t\tand sc.Object_Id = sic.Object_Id \n\t WHERE \n\t\to.type='U'\n\t\tand o.is_ms_shipped=0\n\t\tAND o.Name LIKE COALESCE(@TableFilter, '%')\n\t ORDER BY ObjectName, IndexName, sic.Key_Ordinal\n\n\t --From the #IndexDetail table, I extract a set containing just 1 row per index\n\tINSERT INTO #Indexes \n\t(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)\n\tSELECT DISTINCT\n\t 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''\n\tFROM #IndexDetail;\n\n\t--Find indexes that include LOB data\n\t--Calculate how many LOB columns each index has and write it to the LobColumns column of #Indexes\n\tWITH IndexesWithLOBs AS (\n\n\t\tSELECT ObjectID, IndexID, COUNT(*) AS LOBColumns\n\t\tFROM #Indexes i\n\t\t\tLEFT JOIN sys.index_columns ic --Grabbing INCLUDEd columns\n\t\t\t\ton i.ObjectID=ic.object_id\n\t\t\t\tAND i.IndexID=ic.index_id\n\t\t\tleft join sys.columns c\n\t\t\t\ton i.ObjectID=c.object_id\n\t\t\t\tAND ic.column_id=c.column_id\n\t\t\tleft join sys.types t\n\t\t\t\ton c.system_type_id=t.user_type_id\n\t\tWHERE\t1=1\n\t\t\t\tAND i.Type_Desc='NONCLUSTERED'\n\t\t\t\tand ((\n\t\t\t\t\t\tt.system_type_id IN (231, 165, 167)-- nvarchar, varbinary, varchar\n\t\t\t\t\t\t\tAND c.max_length = -1 -- (MAX)\n\t\t\t\t\t)\n\t\t\t\t\tOR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext\n\t\t\t\t)\n\n\t\tGROUP BY ObjectID, INDEXID\n\n\t\tUNION ALL\n\n\t\tSELECT ObjectID, IndexID, count(*) as LobColumns\n\t\tFROM #Indexes i\n\t\t\tleft join sys.columns c --Grabbing columns that are part of the index\n\t\t\t\ton i.ObjectID=c.object_id\n\t\t\tleft join sys.types t\n\t\t\t\ton c.system_type_id=t.user_type_id\n\t\tWHERE\t1=1\n\t\t\t\tAND (i.Type_Desc='CLUSTERED' OR i.Type_Desc='HEAP')\n\t\t\t\tand ((\n\t\t\t\t\t\tt.system_type_id IN (231, 165, 167)--nvarchar, varbinary, varchar\n\t\t\t\t\t\t\tAND c.max_length = -1-- (MAX)\n\t\t\t\t\t)\n\t\t\t\t\tOR t.system_type_id IN (35, 34, 241, 99)-- text, image, xml, ntext\n\t\t\t\t)\n\t\tGROUP BY ObjectID, IndexID\n\n\t)\n\n\tUPDATE I\n\tSET i.LobColumns=LOB.LobColumns\n\tFROM #Indexes i\n\t\tINNER JOIN IndexesWithLOBs LOB\n\t\t\tON i.ObjectID=LOB.ObjectID\n\t\t\t\tAND i.IndexID=LOB.IndexID\n\n------------------------\n--2) Process the Heaps--\n------------------------\n\tUPDATE #Indexes\n\t\tSET [Cmd]=\n\t\t\t'ALTER TABLE '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ADD [TempPK] bigint identity(1,1);'\n\t\t\t+' CREATE CLUSTERED INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' ([TempPK] ASC) WITH(ONLINE='\n\t\t\t+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)\n\t\t\t\tTHEN 'ON'\n\t\t\t\tELSE 'OFF'\n\t\t\tEND\n\t\t\t+') ON ['+@NewFG+'];'\n\t\t\t+'DROP INDEX [IX_Temp] ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' WITH(ONLINE='\n\t\t\t+CASE WHEN (@TryOnlineRebuild=1 AND LOBColumns=0)\n\t\t\t\tTHEN 'ON'\n\t\t\t\tELSE 'OFF'\n\t\t\tEND\n\t\t\t+');'\n\t\t\t+'alter table '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' drop column [TempPK];'\n\t\t\t--Handle when Page Locks were disabled\n\t\t\t+CASE\n\t\t\t\tWHEN [Allow_Page_Locks]=0\n\t\t\t\tTHEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET  (ALLOW_Page_LOCKS = OFF );'+char(13)+char(10)\n\t\t\t\tELSE ''\n\t\t\t\tEND\n\t\t\t--Handle when Row Locks were disabled\n\t\t\t+CASE\n\t\t\t\tWHEN [Allow_Row_Locks]=0\n\t\t\t\tTHEN ' ALTER INDEX ALL ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName) +' SET  (ALLOW_Row_LOCKS = OFF ); '\n\t\t\t\tELSE ''\n\t\t\tEND\n\t\t,isDone=1\n\tWHERE Type_Desc='Heap'\n\t\tAND isDone=0\n\n\t--Assumption: Padding, ignore_duplicate, fillfactor, is_disabled, and filtering don't apply to a heap \n\n------------------------------------------\n--3) Process the Nonclustered Indexes-----\n------------------------------------------\n\tWHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0)\n\tBEGIN\n\n\t\tSET @CurrentObject= NULL\n\t\tSET @CurrentIndex= NULL\n\n\t\tselect @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;\n\n\t\t--Grab One Index\n\t\tSELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='NONCLUSTERED' AND isDone=0;\n\n\t\tset @colListBase=''\n\t\tset @colListInclude=''\n\n\t\t--For each NC Index, loop through every column in the core index to build up @colListBase\n\t\tWHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )\n\t\tBEGIN\n\t\t\tselect @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;\n\n\t\t\tSELECT 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\n\n\t\t\tif @LoopSafetyInternalBefore=1\n\t\t\t\tBEGIN\n\t\t\t\t\tselect @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID\n\t\t\t\tEND\n\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tselect @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID\n\t\t\t\tEND\n\n\t\t\tUPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID\n\n\t\t\tselect @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;\n\t\t\tif(@LoopSafetyInternalBefore&lt;=@LoopSafetyInternalAfter)\n\t\t\tBEGIN\n\t\t\t\tRAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT\n\t\t\t\tBREAK\n\t\t\tEND\n\n\t\tEND\n\n\t\t--Then Loop through the included columns to build up @colListInclude\n\t\tWHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )\n\t\tBEGIN\n\n\t\t\tselect @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;\n\n\t\t\tSELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and Is_Included_Column=1 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex\n\n\t\t\tif @LoopSafetyInternalBefore=1\n\t\t\t\tBEGIN\n\t\t\t\t\tselect @colListInclude=@colListInclude+quotename(ColumnName) FROM #IndexDetail WHERE ID=@IndexColID\n\t\t\t\tEND\n\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tselect @colListInclude=@colListInclude+quotename(ColumnName)+', ' FROM #IndexDetail WHERE ID=@IndexColID\n\t\t\t\tEND\n\n\t\t\tUPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID\n\n\t\t\tselect @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and Is_Included_Column=1 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;\n\n\t\t\tif(@LoopSafetyInternalBefore&lt;=@LoopSafetyInternalAfter) \t\t\tBEGIN \t\t\t\tRAISERROR('Infinite Loop while adding included columns for object %i',11,1, @CurrentObject) WITH NOWAIT \t\t\t\tBREAK \t\t\tEND \t\t \t\tEND \t \t\t--Now that the column lists are unrolled, form the command \t\tSELECT @cmd= \t\t\t'CREATE ' \t\t\t+CASE \t\t\t\tWHEN Is_Unique=1 \t\t\t\t\tTHEN 'UNIQUE ' \t\t\t\tELSE '' \t\t\tEND \t\t\t+'NONCLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) \t\t\t+'('+@colListBase+')' \t\t\t+CASE WHEN len(@colListInclude)&gt;0\n\t\t\t\tTHEN 'INCLUDE('+@colListInclude+')'\n\t\t\t\tELSE ''\n\t\t\tEND\n\t\t\t+CASE WHEN Filter_Definition is not null\n\t\t\t\tTHEN ' WHERE '+Filter_Definition\n\t\t\t\tELSE ''\n\t\t\tEND\n\t\t\t+' WITH(DROP_EXISTING=ON,PAD_INDEX='\n\t\t\t+CASE\n\t\t\t\tWHEN is_padded=1\n\t\t\t\t\tTHEN 'ON,'\n\t\t\t\tELSE 'OFF,'\n\t\t\tEND\n\t\t\t+CASE\n\t\t\t\tWHEN Fill_Factor&gt;0\n\t\t\t\t\tTHEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','\n\t\t\t\tELSE ''\n\t\t\tEND\n\t\t\t+'IGNORE_DUP_KEY='\n\t\t\t+CASE\n\t\t\t\tWHEN Ignore_Dup_Key=1\n\t\t\t\t\tTHEN 'ON,'\n\t\t\t\tELSE 'OFF,'\n\t\t\tEND\n\n\t\t\t+'ALLOW_ROW_LOCKS='\n\t\t\t+CASE\n\t\t\t\tWHEN Allow_Row_Locks=1\n\t\t\t\t\tTHEN 'ON,'\n\t\t\t\tELSE 'OFF,'\n\t\t\tEND\n\t\t\t+'ALLOW_PAGE_LOCKS='\n\t\t\t+CASE\n\t\t\t\tWHEN Allow_Page_Locks=1\n\t\t\t\t\tTHEN 'ON,'\n\t\t\t\tELSE 'OFF,'\n\t\t\tEND\n\t\t\t+'ONLINE='\n\t\t\t+CASE\n\t\t\t\tWHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)\n\t\t\t\t\tTHEN 'ON'\n\t\t\t\tELSE 'OFF'\n\t\t\tEND\n\t\t\t+') ON '+quotename(@NewFG)+';'\n\t\t\t+CASE\n\t\t\t\tWHEN isDisabled=1\n\t\t\t\t\tTHEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'\n\t\t\t\tELSE ''\n\t\t\tEND\n\n\t\tFROM #Indexes\n\t\tWHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex\n\n\t\t--Write the command back to the table\n\t\tUPDATE #Indexes\n\t\tSET [Cmd]=@cmd, isDone=1\n\t\tWHERE ObjectID=@CurrentObject\n\t\t\tAND IndexID=@CurrentIndex\n\n\t\tSELECT @LoopSafetyAfter=count(*)\n\t\tFROM #Indexes\n\t\tWHERE Type_Desc='NONCLUSTERED'\n\t\t\tAND isDone=0;\n\n\t\tif(@LoopSafetyBefore&lt;=@LoopSafetyAfter)\n\t\tBEGIN\n\t\t\tRAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT\n\t\t\tBREAK\n\t\tEND\n\n\tEND\n\n----------------------------------------------------\n--4) Repeat the pattern for Clustered Indexes-------\n----------------------------------------------------\n\n\tWHILE EXISTS (SELECT ID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0)\n\tBEGIN\n\n\t\tSET @CurrentObject= NULL\n\t\tSET @CurrentIndex= NULL\n\n\t\tselect @LoopSafetyBefore=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;\n\n\t\t--Grab One Index\n\t\tSELECT TOP 1 @CurrentObject=ObjectID, @CurrentIndex=IndexID FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;\n\n\t\tset @colListBase=''\n\t\tset @colListInclude=''\n\n\t\t--For each Clustered Index, loop through every column in the core index\n\t\tWHILE EXISTS (SELECT 1 FROM #IndexDetail WHERE isDone=0 AND ObjectID=@CurrentObject AND IndexID=@CurrentIndex )\n\t\tBEGIN\n\n\t\t\tselect @LoopSafetyInternalBefore=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;\n\n\t\t\tSELECT TOP 1 @IndexColID=ID FROM #IndexDetail WHERE isDone=0  and ObjectID=@CurrentObject AND IndexID=@CurrentIndex ORDER BY key_ordinal ASC\n\n\t\t\tif @LoopSafetyInternalBefore=1\n\t\t\t\tBEGIN\n\t\t\t\t\tselect @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC' ELSE '' END FROM #IndexDetail WHERE ID=@IndexColID\n\t\t\t\tEND\n\t\t\tELSE\n\t\t\t\tBEGIN\n\t\t\t\t\tselect @colListBase=@colListBase+quotename(ColumnName) + CASE WHEN Is_Descending_Key=1 THEN ' DESC, ' ELSE ', ' END FROM #IndexDetail WHERE ID=@IndexColID\n\t\t\t\tEND\n\n\t\t\tUPDATE #IndexDetail SET isDone=1 WHERE ID=@IndexColID\n\n\t\t\tselect @LoopSafetyInternalAfter=count(*) FROM #IndexDetail WHERE isDone=0 and ObjectID=@CurrentObject and IndexID=@CurrentIndex;\n\n\t\t\tif(@LoopSafetyInternalBefore&lt;=@LoopSafetyInternalAfter) \t\t\tBEGIN \t\t\t\tRAISERROR('Infinite Loop while adding columns for object %i',11,1, @CurrentObject) WITH NOWAIT \t\t\t\tBREAK \t\t\tEND \t\t \t\tEND \t \t\t--Form the command \t\tSELECT \t\t\t@cmd= 'CREATE ' \t\t\t+CASE \t\t\t\tWHEN Is_Unique=1 \t\t\t\t\tTHEN 'UNIQUE ' \t\t\t\tELSE '' \t\t\tEND \t\t\t+'CLUSTERED INDEX '+quotename(IndexName) +' ON '+QUOTENAME(schemaname)+'.'+QUOTENAME(ObjectName) \t\t\t+'('+@colListBase+')' \t\t\t+' WITH(DROP_EXISTING=ON,PAD_INDEX=' \t\t\t+CASE WHEN is_padded=1 \t\t\t\tTHEN 'ON,' \t\t\t\t\tELSE 'OFF,' \t\t\tEND \t\t\t+CASE \t\t\t\tWHEN Fill_Factor&gt;0\n\t\t\t\t\tTHEN 'FILLFACTOR='+cast(fill_factor as varchar(5))+','\n\t\t\t\tELSE ''\n\t\t\tEND\n\t\t\t+'IGNORE_DUP_KEY='\n\t\t\t+CASE\n\t\t\t\tWHEN Ignore_Dup_Key=1\n\t\t\t\t\tTHEN 'ON,'\n\t\t\t\tELSE 'OFF,'\n\t\t\tEND\n\t\t\t+'ALLOW_ROW_LOCKS='\n\t\t\t+CASE\n\t\t\t\tWHEN Allow_Row_Locks=1\n\t\t\t\t\tTHEN 'ON,'\n\t\t\t\tELSE 'OFF,'\n\t\t\tEND\n\t\t\t+'ALLOW_PAGE_LOCKS='\n\t\t\t+CASE\n\t\t\t\tWHEN Allow_Page_Locks=1\n\t\t\t\t\tTHEN 'ON,'\n\t\t\t\tELSE 'OFF,'\n\t\t\tEND\n\t\t\t+'ONLINE='\n\t\t\t+CASE\n\t\t\t\tWHEN (@TryOnlineRebuild=1 AND LOBColumns=0 AND isDisabled=0)\n\t\t\t\t\tTHEN 'ON'\n\t\t\t\tELSE 'OFF'\n\t\t\tEND\n\t\t\t+') ON '+quotename(@NewFG)+';'\n\t\t\t+CASE\n\t\t\t\tWHEN isDisabled=1\n\t\t\t\t\tTHEN ' ALTER INDEX '+quotename(IndexName)+' ON '+QUOTENAME(SchemaName)+'.'+QUOTENAME(ObjectName)+' DISABLE;'\n\t\t\t\tELSE ''\n\t\t\tEND\t\n\t\tFROM #Indexes\n\t\tWHERE ObjectID=@CurrentObject AND IndexID=@CurrentIndex\n\n\t\t--Write the command back to the @Indexes table\n\t\tUPDATE #Indexes\n\t\tSET [Cmd]=@cmd, isDone=1\n\t\tWHERE ObjectID=@CurrentObject\n\t\tAND IndexID=@CurrentIndex\n\n\t\tselect @LoopSafetyAfter=count(*) FROM #Indexes WHERE Type_Desc='CLUSTERED' AND isDone=0;\n\n\t\tif(@LoopSafetyBefore&lt;=@LoopSafetyAfter)\n\t\tBEGIN\n\t\t\tRAISERROR('Infinite Loop in Indexes, object %i',11,2,@CurrentObject) WITH NOWAIT\n\t\t\tBREAK\n\t\tEND\n\n\tEND\n\n--select * from #IndexDetail\n--select * FROM #Indexes\n\nSELECT\n\tFileGroupName\n\t,SchemaName\n\t,ObjectName\n\t,IndexName\n\t,IndexID\n\t,IndexType\n\t,Is_Unique\n\t,is_Primary\n\t,Type_Desc\n\t,Is_Padded\n\t,Ignore_Dup_Key\n\t,Allow_Row_Locks\n\t,Allow_Page_Locks\n\t,Fill_Factor\n\t,isDisabled\n\t,Has_Filter\n\t,Filter_Definition\n\t,LOBColumns\n\t,[Cmd]\nFROM #Indexes\nWHERE FileGroupName &lt;&gt; @NewFG\nORDER BY SchemaName, ObjectName<\/pre>\n<p>Remember, the script doesn&#8217;t change anything; rather it generates TSQL commands you can choose to run.<\/p>\n<h3>Running the Generated Commands and Verification<\/h3>\n<p>The final step would be to <b>review<\/b> and <em>possibly<\/em> run the commands in the last column of the table.<\/p>\n<p>After doing so, we can run the query mentioned in the &#8220;Check Current Filegroup Layout&#8221; section above to verify that every index of every (user) table has moved to the new filegroup\/file.<\/p>\n<p>As a note, system tables are forever in the first, primary data file.&nbsp; It can be <em>moved<\/em> (an offline operation, unfortunately), but it cannot be <span style=\"text-decoration: underline;\">re<\/span>moved.&nbsp; Attempting to do so will yield the following error:<\/p>\n<p><em><span style=\"color: #ff0000;\">Msg 5020, Level 16, State 1, Line 1<\/span><\/em><br \/>\n<em><span style=\"color: #ff0000;\">The primary data or log file cannot be removed from a database.<\/span><\/em><\/p>\n<h3>Changing the Filegroup to Read_Only<\/h3>\n<p>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:<\/p>\n<pre lang=\"tsql\">use FileGroupTesting;\n\n--You need to be the only one in the database.&nbsp; You may need to kick everyone else off:\nalter database [FileGroupTesting] set single_user with rollback immediate;\n\n--Make FileGroup ReadOnly\nALTER DATABASE [FileGroupTesting]\nMODIFY FILEGROUP [FGTest] Read_only;\n\nalter database [FileGroupTesting] set multi_user with rollback immediate;\n\nSELECT\nname\n,is_read_only\n,is_default\nFROM sys.filegroups<\/pre>\n<p>The full script can be found here: <a href=\"http:\/\/www.sqlsnee.com\/dl\/scripts\/MovingTablesToNewFG.txt\">MovingTablesToNewFG.txt<\/a>.<\/p>\n<p>That&#8217;s it! Please let me know if you found this script useful!<\/p>\n<p>LLAP,<br \/>\nRyan<\/p>\n<p>A few sources I&#8217;d like to cite for contributing ideas:<\/p>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/4237238\/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server\">http:\/\/stackoverflow.com\/questions\/4237238\/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server<\/a><\/li>\n<li><a href=\"http:\/\/dba.stackexchange.com\/questions\/28872\/move-primary-key-to-filegroup-sql-server-2012\">http:\/\/dba.stackexchange.com\/questions\/28872\/move-primary-key-to-filegroup-sql-server-2012<\/a><\/li>\n<li><a href=\"http:\/\/www.aaronlowe.net\/archive\/2007\/09\/move-heap-tables\/\">http:\/\/www.aaronlowe.net\/archive\/2007\/09\/move-heap-tables\/<\/a><\/li>\n<\/ul>\n<hr>\n<p>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 <a href=\"http:\/\/www.sqlsnee.com\/r\/cclicense\" target=\"_blank\" rel=\"noopener noreferrer\">Creative Commons Attribution-ShareAlike 3.0 Unported License<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is my first post.&nbsp; My goal with this post is to share how I moved a subset of the tables in my database to another filegroup.&nbsp; Why would you want to do this, you ask?&nbsp; A few possible use-cases: You want to move a set of tables to a \u2026 <a class=\"continue-reading-link\" href=\"https:\/\/blog.sqlsnee.com\/?p=25\"> Continue reading <span class=\"meta-nav\">&rarr; <\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,15],"tags":[21,11,12],"class_list":["post-25","post","type-post","status-publish","format-standard","hentry","category-filegroups","category-storage","tag-filegroups","tag-files","tag-sql"],"_links":{"self":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/25","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=25"}],"version-history":[{"count":10,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/25\/revisions"}],"predecessor-version":[{"id":252,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=\/wp\/v2\/posts\/25\/revisions\/252"}],"wp:attachment":[{"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=25"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=25"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlsnee.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=25"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}