Moving Tables and Indexes to Filegroups En Masse

Recently I had to cover for a colleague who was taking a weeks vacation. "You will probably be kept busy" he said during the last minute handover "but I would appreciate it if you could add a separate index and data filegroup and shift all the relevant objects onto those filegroups".

And with that he vanished off into the Friday rain to a holiday of sand, sea, sangria and other alliterations that make vacations so pleasurable. It was only when I started to look at this in more detail that I realised that what he was hoping was for me to shift data around in the offline data store of a data warehouse covering 3 years of transactions!

Of course when you know how to do it the moving of such things is straight forward but very time consuming. Raj Gujar's recent article covers it nicely. http://www.sqlservercentral.com/articles/Administration/64275/

Determining the requirements

The two filegroups to which data and indexes were to be moved were named FGDATA and FGINDEX.

The offline data store is the accumulation of all transactions on all database servers that are relevant to the data warehouse. This is a large number of tables so manually scripting every drop and recreation is not really practical.

The primary keys in most of the data warehouse tables were clustered indexes so I was going to have to drop and recreate the primary keys in the majority of cases.

For reasons I won't go into the offline data store had foreign key constraints to enforce DRI and as you can't drop a primary or unique constraint if a foreign key constraint depends on it I was going to have to drop and recreate the foreign key constraints as well.

Of course dropping and recreating primary and unique key constraints and indexes is going to thrash TEMPDB and with the volumes of data involved I have to worry about disk space. I decided to record the commands I was going to use as strings in a table so I would have a log of what the process had attempted to do so I could recover manually where possible. I tend to have an administration database called DBA where all such work in progress data is stored.

So my requirements were as follows

Record all commands to create/destroy objects
- Foreign keys
- Primary/Unique clustered constraints
- Clustered indexes
- Non-clustered primary/unique constraints
- Non-clustered indexes
- XML Indexes
- Primary XML Indexes
Make the script database agnostic (it should run on any database)
Make the script as safe as possible. Running it on a database where the objects have already been moved should do no harm!
Make the script print out what it was doing so I had a log of the execution.

The drop recreate order

The drop/recreate order is dictated by the dependencies.

You cannot drop an XML index until the primary XML index has been dropped.
You cannot drop a primary key before any primary XML indexes or foreign keys have been dropped.

The drop order is as follows

Foreign keys
Secondary XML indexes
Primary XML indexes
Nonclustered keys/indexes
Clustered keys/Indexes

The create order is a reverse of this however you may have a particular reason for changing the order of recreation.

Creating the backup tables in the DBA database

As I wanted my script to run in any database but log to my DBA database I had to use dynamic SQL. This would be a bit of a nuisance but not really complicated.

If I ran my script in the AdventureWorks database then my backup table would be called AdventureWorks_FileGroupMove. It would have the following structure

Field	Type	Description
DropOrder	TINYINT	The order in which objects should be dropped. Low values first.
CreateOrder	TINYINT	The order in which objects should be recreated.
TableName	VARCHAR(256)	The table that is affected by the command
DropObject	VARCHAR(500)	The T-SQL to drop the object
CreateObject	VARCHAR(500)	The T-SQL to recreate the object.

DECLARE 
 @TableName sysname,
 @SQL VARCHAR(MAX),
 @CRLF CHAR(2)
SET @CRLF = CHAR(13)+ CHAR(10)
SET @TableName = DB_NAME()+'_FileGroupMove' 

--------------------------------------------------------------------------
-- We do not want to lose any foreign key drop/create commands so log them 
-- to a permanent table in the DBA database. 
---------------------------------------------------------------------------
    IF NOT EXISTS(SELECT 1 FROM DBA.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME=@TableName)
 BEGIN   SET @SQL='CREATE TABLE DBA.dbo.'   
  + QUOTENAME(@TableName)
  + '( DropOrder TINYINT NOT NULL,CreateOrder TINYINT NOT NULL,TableName VARCHAR(256) NOT NULL,DropObject varchar(500) NOT NULL CONSTRAINT PK_'
  + @TableName
  + ' PRIMARY KEY CLUSTERED,CreateObject VARCHAR(500) NOT NULL)'
  PRINT @SQL
  EXEC(@SQL)

NB The END statement is in the code for the next section.

Recording foreign key drop and recreate commands

Generating a script to drop foreign keys is easy and I covered it in my recent article Fast Project Rollbacks.

Scripting the recreation is a bit more involved.

The first step is to take a look at the sysreferences table.

Field	Meaning
KeyCnt	The number of fields participating in the relationship. This is very important.
Constid	The id from sysobjects representing the foreign key.
Fkeyid	The id from sysobjects representing the child table.
Rkeyid	The id from sysobjects representing the referenced (parent) table.
Fkey1 …Fkey16	The colid representing the syscolumns field on the child side of the relationship
Rkey1…Rkey16	The colid representing the syscolums field on the parent side of the relationship.

All foreign keys will have at least one field participating in the relationship but we can have up to 16fields participating if we wish. The KeyCnt columns tells us how many we have to allow for.

In Adventureworks the maximum value for this field is two so the SQL below is adequate for what we want to do.

---------------------------------------------------------------------------
-- As the table name is dynamic we need to build up the SQL command 
-- to populate the table.
---------------------------------------------------------------------------
  SET @SQL=
   'INSERT INTO DBA.dbo.'
  + @TableName
  + '(DropOrder,CreateOrder,TableName,DropObject,CreateObject)'
  + @CRLF
  +  'SELECT 1 AS DropOrder,5 AS CreateOrder,''''
  + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(rkeyid,''SchemaID'')AS INT)))
  + ''.''    
  + QUOTENAME(OBJECT_NAME(rkeyid))
  + '''' AS TableName,''ALTER TABLE ''   
  + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(fkeyid,''SchemaID'')AS INT)))
  + ''.'' 
  + QUOTENAME(OBJECT_NAME(fkeyid))
  + '' DROP CONSTRAINT ''
  + QUOTENAME(OBJECT_NAME(constid)) AS DropObject,
   ''ALTER TABLE ''
  + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(fkeyid,''SchemaID'')AS INT)))
  + ''.'' 
  + QUOTENAME(OBJECT_NAME(fkeyid))
  + '' WITH NOCHECK ADD CONSTRAINT ''
  + QUOTENAME(OBJECT_NAME(constid))
  + '' FOREIGN KEY (''
  + QUOTENAME(C.[name])
  + CASE WHEN F2.id IS NOT NULL THEN '','' + QUOTENAME(F2.[name]) ELSE '''' END
  + '')''
  + '' REFERENCES ''
  + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(rkeyid,''SchemaID'')AS INT)))
  + ''.'' 
  + QUOTENAME(OBJECT_NAME(rkeyid))
  + ''(''
  + QUOTENAME(C1.[name])
  + CASE WHEN R2.id IS NOT NULL THEN '','' + QUOTENAME(R2.[name]) ELSE '''' END
  + '')'' AS CreateObject
  FROM
   sys.sysreferences AS R
   INNER JOIN sys.syscolumns AS C
   ON R.fkeyid = C.id
   AND R.fkey1 = C.colid
   LEFT JOIN sys.syscolumns as F2
   ON R.fkeyid = F2.id
   and R.fkey2 = F2.colid
   
   INNER JOIN sys.syscolumns AS C1
   ON R.rkeyid = C1.id
   AND R.rkey1 = C1.colid
   LEFT JOIN sys.syscolumns as R2
   ON R.rkeyid = R2.id
   AND R.rkey2 = R2.colid'       
  PRINT @SQL
  EXEC(@SQL)
 END GO

If the MAX(keycnt) value was greater then we would need additional LEFT JOIN statements. Let us suppose that we had a maximum value of three then the additional join statements would be as follows.

   LEFT JOIN sys.syscolumns as F3
   ON R.fkeyid = F3.id
   and R.fkey3 = F3.colid
   LEFT JOIN sys.syscolumns as R3
   ON R.rkeyid = R3.id
   AND R.rkey3 = R3.colid'

Note that for the 3rd field in the relationship I am using fkey3 and rkey3.

If there was a 4th field in the relationship I would be using fkey4 and rkey4.

In the select statement the following line would change from

  + CASE WHEN R2.id IS NOT NULL THEN '','' + QUOTENAME(R2.[name])
    ELSE '''' END

  + CASE WHEN R2.id IS NOT NULL THEN '','' + QUOTENAME(R2.[name]) ELSE '''' END
  + CASE WHEN R3.id IS NOT NULL THEN '','' + QUOTENAME(R3.[name]) ELSE '''' END

And similarly

  + CASE WHEN F2.id IS NOT NULL THEN '','' + QUOTENAME(F2.[name])
    ELSE '''' END

would change to

  + CASE WHEN F2.id IS NOT NULL THEN '','' + QUOTENAME(F2.[name]) ELSE '''' END
  + CASE WHEN F3.id IS NOT NULL THEN '','' + QUOTENAME(F3.[name]) ELSE '''' END

If we were to run the script now then we would find that all our drop and create commands had been generated and stored in DBA.dbo.AdventureWorks_FileGroupMove

If we were to look at the record for SalesOrderDetail we would see the following commands had been generated and stored

ALTER TABLE [Sales].[SalesOrderDetail] WITH
    NOCHECK 
 ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] 
  FOREIGN KEY ([SalesOrderID]) 
   REFERENCES [Sales].[SalesOrderHeader]([SalesOrderID])
ALTER TABLE [Sales].[SalesOrderDetail] WITH
    NOCHECK 
 ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID] 
  FOREIGN KEY ([SpecialOfferID],[ProductID]) 
   REFERENCES [Sales].[SpecialOfferProduct]([SpecialOfferID],[ProductID])

There are three points to note at this stage

QUOTENAME()

I prefer any object that I create to use alphanumerics and underscore characters. Every time that I have deviated from this rule it has come back to haunt me at some stage. I find that wrapping object names up in brackets as performed by the QUOTENAME() function simply makes the code harder to read. I have used QUOTENAME here simply to produce a script that will work with whatever legacy or 3^rd party aberration may be inflicted on me.

WITH NOCHECK

I have deliberately used WITH NOCHECK because I don't want the overhead of checking the foreign key constraints for legacy data.

I can always script up the WITH CHECK equivalent later if I need to do so.

The TableName field

The script stored the table name of the referenced table for foreign keys. For example, if you wanted to move a Country table to a separate file group then before you could drop the primary key constraint you would have to remove all foreign keys that refer to the Country table.

Drop and recreate XML indexes

The following script will generate the commands to drop and recreate both the primary and secondary XML indexes.

-----------------------------------------------------------------------------
-- Record the commands to drop and recreate XML indexes 
-----------------------------------------------------------------------------
DECLARE 
 @TableName sysname,
 @SQL VARCHAR(MAX),
 @CRLF CHAR(2)
SET @CRLF = CHAR(13)+ CHAR(10)
SET @TableName = DB_NAME()+'_FileGroupMove'   SET @SQL=
   'INSERT INTO DBA.dbo.'
  + @TableName
  + '(DropOrder,CreateOrder,TableName,DropObject,CreateObject)'
  + @CRLF
+ 'SELECT 
 CASE WHEN PRI.name is null THEN ''3'' ELSE ''2'' END AS DropOrder,
 CASE WHEN PRI.name is null THEN ''3'' ELSE ''4'' END AS CreateOrder,
 + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(MI.Object_ID,''SchemaID'')AS INT)))
 + ''.''
 + QUOTENAME(OBJECT_NAME(MI.object_id)) AS TableName,
 ''DROP INDEX ''
 + QUOTENAME(MI.Name)
 + '' ON ''
 + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(MI.Object_ID,''SchemaID'')AS INT)))
 + ''.''
 + QUOTENAME(OBJECT_NAME(MI.object_id)),
 ''CREATE ''
 + CASE WHEN PRI.name IS NOT NULL THEN '''' ELSE ''PRIMARY '' END
 + ''XML INDEX ''
 + QUOTENAME(MI.Name)
 + '' ON ''
 + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(MI.Object_ID,''SchemaID'')AS INT)))
 + ''.''
 + QUOTENAME(OBJECT_NAME(MI.object_id))
 +''(''
 + QUOTENAME(C.name)
 + '')''
 + CASE WHEN PRI.name IS NULL THEN '''' 
  ELSE '' USING XML INDEX '' 
 + QUOTENAME(PRI.name)
 + '' FOR ''
 + CASE MI.secondary_type
  WHEN ''V'' THEN ''VALUE''
  WHEN ''P'' THEN ''PATH''
  WHEN ''R'' THEN ''PROPERTY''
  ELSE '''' END
END
FROM sys.xml_indexes AS MI
 INNER JOIN sys.index_columns AS IC
 ON MI.object_id = IC.Object_ID
 AND MI.Index_id = IC.Index_id
 INNER JOIN sys.columns As C
 ON IC.object_id = C.object_id
 AND IC.column_id = C.column_id
 LEFT JOIN sys.xml_indexes AS PRI
 ON MI.object_id = PRI.object_id
 AND MI.using_xml_index_id = PRI.index_id
WHERE PRI.secondary_type is null
ORDER BY MI.secondary_type desc'
PRINT @SQL
EXEC(@SQL)
GO

Indexes that are not also a constraint

Both PRIMARY KEY and UNIQUE constraint have entries in the sys.sysindexes table and require

the ALTER TABLE <TableName> DROP CONSTRAINT syntax. We want to identify those sys.sysindexes records that

require the DROP INDEX syntax.

The following script generates the drop and recreate commands for indexes that are not also a constraint.

-----------------------------------------------------------------------------
-- Assemble a list of tables/indexes where the index is not a constraint 
-----------------------------------------------------------------------------
DECLARE @IndexList TABLE (
 PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 Id INT NOT NULL ,
 IndID INT NOT NULL
 )
INSERT INTO @IndexList (
 Id,
 IndID
) 
SELECT I.id,I.indid
FROM sys.sysindexes AS I
WHERE INDEXPROPERTY ( I.ID, I.name, 'IsStatistics' ) =0
AND OBJECTPROPERTY(I.id,'IsUserTable')=1
AND OBJECTPROPERTY(I.id,'IsMSShipped')=0
AND I.name NOT IN (SELECT O.name FROM sys.sysobjects AS O WHERE I.ID = O.Parent_Obj AND O.xtype IN ('UQ','PK'))
AND I.groupid = FILEGROUP_ID('PRIMARY')
-----------------------------------------------------------------------------
-- Itterate through the tables dropping and recreating the indexes that are not constraints 
-----------------------------------------------------------------------------
DECLARE @TableID INT, @TableName SYSNAME SET @TableName = DB_NAME()+'_FileGroupMove' SET @TableID = 0
DECLARE @SQL VARCHAR(MAX),
 @INCLUDE VARCHAR(MAX),
 @CRLF CHAR(2)
SET @CRLF = CHAR(13)+ CHAR(10)
WHILE @TableID IS NOT NULL
 BEGIN   SET @SQL = NULL
  SELECT @TableID = MIN(PK)
  FROM @IndexList
  WHERE PK> @TableID
  
  IF @TableID IS NOT NULL
   BEGIN
    SELECT @SQL=COALESCE(@SQL+',','') 
    + QUOTENAME(C.[name])
    FROM sys.sysindexes AS I
     INNER JOIN sys.sysindexkeys AS K
     ON I.id = K.id
     AND I.indid = K.indid
     
     INNER JOIN sys.syscolumns AS C
     ON K.id = C.id
     AND K.colid = C.colid
     INNER JOIN sys.sysfilegroups AS G
     ON I.groupid = G.groupid
     
     INNER JOIN @IndexList AS IL
     ON I.id = IL.Id
     AND I.indid = IL.IndID
    WHERE IL.PK = @TableID
    AND G.groupid = FILEGROUP_ID('PRIMARY')
    AND K.KeyNo>0
    ORDER BY K.keyno
    IF @@ROWCOUNT>0
     BEGIN       set @INCLUDE = NULL
      SELECT @INCLUDE=COALESCE(@INCLUDE+',','') 
      + QUOTENAME(C.[name])
      FROM sys.sysindexes AS I
       INNER JOIN sys.sysindexkeys AS K
       ON I.id = K.id
       AND I.indid = K.indid
       
       INNER JOIN sys.syscolumns AS C
       ON K.id = C.id
       AND K.colid = C.colid
       INNER JOIN sys.sysfilegroups AS G
       ON I.groupid = G.groupid
       
       INNER JOIN @IndexList AS IL
       ON I.id = IL.Id
       AND I.indid = IL.IndID
      WHERE IL.PK = @TableID
      AND G.groupid = FILEGROUP_ID('PRIMARY')
      AND K.KeyNo=0
    set @Include= coalesce(@include,'')
    -- If the primary key was on the PRIMARY file
    group we need to move it.       SELECT 
       @SQL='INSERT INTO DBA.dbo.'       + @TableName
      + '(DropOrder,CreateOrder,TableName,DropObject,CreateObject)'
      + @CRLF
      +  'SELECT '       
+ CASE INDEXPROPERTY(I.id,I.name,'IsClustered')
        WHEN 0 THEN '4 '         
        WHEN 1 THEN '5 '         END
      + ' AS DropOrder,'
      + CASE INDEXPROPERTY(I.id,I.name,'IsClustered')
        WHEN 0 THEN '2 '         
        WHEN 1 THEN '1 '         END
      + ' AS CreateOrder,'''       
      + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(I.id,'SchemaID')AS INT)))
      + '.'       
      + QUOTENAME(OBJECT_NAME(I.id))
      + ''' AS TableName, ''DROP INDEX '       
      + QUOTENAME(I.[name])
      + ' ON '
      + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(I.id,'SchemaID')AS INT)))
      + '.'
      + QUOTENAME(OBJECT_NAME(I.id))
      + ';'' AS DropObject,'       + '''CREATE '
      + CASE INDEXPROPERTY(I.id,I.name,'IsUnique')
        WHEN 0 THEN ''
        WHEN 1 THEN 'UNIQUE '
        END
      + CASE INDEXPROPERTY(I.id,I.name,'IsClustered')
        WHEN 0 THEN ''
        WHEN 1 THEN 'CLUSTERED '         END
      + 'INDEX '
      + QUOTENAME(I.[name])
      + ' ON '
      + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(I.id,'SchemaID')AS INT)))
      + '.'
      + QUOTENAME(OBJECT_NAME(I.id))
      + ' ('
      + @SQL
      + ')'
      + CASE WHEN LEN(@INCLUDE)=0
       THEN ''
       ELSE ' INCLUDE('+ @INCLUDE + ')'
       END
      + ' ON '
      + CASE INDEXPROPERTY(I.id,I.name,'IsClustered')
        WHEN 0 THEN '[FGINDEX];'
        WHEN 1 THEN '[FGDATA];'         
END
      + ''' AS CreateObject'
      FROM sys.sysindexes AS I
       INNER JOIN @IndexList AS IL
       ON I.id = IL.Id
       AND I.indid = IL.IndID
      WHERE IL.PK = @TableID
      RAISERROR('%s',10,1, @sql) WITH NOWAIT       
        EXEC(@SQL)
     END
    END
   END
GO

There are some points to note at this stage.

RAISERROR('%s',10,1,
@sql) WITH NOWAIT

The PRINT statement will only work at the end of the batch. Using the RAISERROR statement with NOWAIT ensures that the result is echoed to the screen straight away. For the web programmers amongst you this is the SQL equivalent of a Flush() method.

This is important because the building of the constraints could take a very long time and being able to determine the script progress reduces the blood pressure.

FILEGROUPS

The sysindexes table links to sysfilegroups. It is this that determines where data/indexes reside.

Remember that the clustered index/constraint cannot be separated from the data.

UNIQUE INDEX

In ANSI SQL there are UNIQUE CONSTRAINTs and not UNIQUE INDEXs. SQL Server allows you to have a unique index without a unique constraint but a unique constraint will build a unique index to cover it.

In AdventureWorks there the Production.BillOfMaterials uses a unique clustered index.

Indexes that ARE constraints

At first glance the script below appears so similar to the script for indexes that ARE NOT constraints that you might wonder why I haven't done anything clever to roll the two script blocks into a single block with case statements.

Primarily I kept the two separate due to time pressures. Being able to generate the scripts drop/recreate commands is the important thing.

There are also some quirks in the INDEXPROPERTY() function. Even though both primary key and unique keys are indexes the INDEXPROPERTY() function is not always correct in identifying them as CLUSTERED/NONCLUSTERED.

-----------------------------------------------------------------------------
    -- Assemble a list of tables/indexes where the index is a constraint 
-----------------------------------------------------------------------------
DECLARE @IndexList TABLE (
 PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 Id INT NOT NULL ,
 IndID INT NOT NULL
 )
INSERT INTO @IndexList (
 Id,
 IndID
) 
SELECT I.id,I.indid
FROM sys.sysindexes AS I
WHERE INDEXPROPERTY ( I.ID, I.name, 'IsStatistics' ) =0
AND OBJECTPROPERTY(I.id,'IsUserTable')=1
AND OBJECTPROPERTY(I.id,'IsMSShipped')=0
AND I.name IN (SELECT O.name FROM sys.sysobjects AS O WHERE I.ID = O.Parent_Obj AND O.xtype IN ('UQ','PK'))
AND I.groupid = FILEGROUP_ID('PRIMARY')
AND object_name(I.id)<>'sysdiagrams' 
-----------------------------------------------------------------------------
-- Itterate through the tables dropping and recreating the indexes that are constraints
----------------------------------------------------------------------------- 
DECLARE @TableID INT, @TableName SYSNAME
SET @TableName = DB_NAME()+'_FileGroupMove'
SET @TableID = 0
DECLARE @SQL VARCHAR(MAX),
 @INCLUDE VARCHAR(MAX),
 @CRLF CHAR(2)
SET @CRLF = CHAR(13)+ CHAR(10)
WHILE @TableID IS NOT NULL
 BEGIN   SET @SQL = NULL
  SELECT @TableID = MIN(PK)
  FROM @IndexList
  WHERE PK> @TableID
  
  IF @TableID IS NOT NULL
   BEGIN     SELECT @SQL=COALESCE(@SQL+',','') 
    + QUOTENAME(C.[name])
    FROM sys.sysindexes AS I
     INNER JOIN sys.sysindexkeys AS K
     ON I.id = K.id
     AND I.indid = K.indid
     
     INNER JOIN sys.syscolumns AS C
     ON K.id = C.id
     AND K.colid = C.colid
     INNER JOIN sys.sysfilegroups AS G
     ON I.groupid = G.groupid
     
     INNER JOIN @IndexList AS IL
     ON I.id = IL.Id
     AND I.indid = IL.IndID
    WHERE IL.PK = @TableID
    AND G.groupid = FILEGROUP_ID('PRIMARY')
    AND K.KeyNo>0
    ORDER BY K.keyno
    IF @@ROWCOUNT>0
     BEGIN       set @INCLUDE = NULL
      SELECT @INCLUDE=COALESCE(@INCLUDE+',','') 
      + QUOTENAME(C.[name])
      FROM sys.sysindexes AS I
       INNER JOIN sys.sysindexkeys AS K
       ON I.id = K.id
       AND I.indid = K.indid
       
       INNER JOIN sys.syscolumns AS C
       ON K.id = C.id
       AND K.colid = C.colid
       INNER JOIN sys.sysfilegroups AS G
       ON I.groupid = G.groupid
       
       INNER JOIN @IndexList AS IL
       ON I.id = IL.Id
       AND I.indid = IL.IndID
      WHERE IL.PK = @TableID
      AND G.groupid = FILEGROUP_ID('PRIMARY')
      AND K.KeyNo=0
    set @Include= coalesce(@include,'')
    -- If the primary key was on the PRIMARY file group we need to move it.       
        SELECT 
       @SQL='INSERT INTO DBA.dbo.'
      + @TableName
      + '(DropOrder,CreateOrder,TableName,DropObject,CreateObject)'
      + @CRLF
      +  'SELECT '
      + CASE I.IndID WHEN 1 THEN '5 ' ELSE '4 ' END
      + ' AS DropOrder,'
      + CASE I.IndID WHEN 1 THEN '1 ' ELSE '2 ' END
      + ' AS CreateOrder,'''       + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(I.id,'SchemaID')AS INT)))
      + '.'       + QUOTENAME(OBJECT_NAME(I.id))
      + ''' AS TableName,''ALTER TABLE '
      + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(I.id,'SchemaID')AS INT)))
      + '.'       + QUOTENAME(OBJECT_NAME(I.id))
      + ' DROP CONSTRAINT '       
      + QUOTENAME(I.[name])
      + CASE I.IndId
        WHEN 1 THEN ' WITH (MOVE TO [FGDATA]) '
        ELSE ''         END
      + ';'' AS DropObject,'       + '''ALTER TABLE '
      + QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(I.id,'SchemaID')AS INT)))
      + '.'       + QUOTENAME(OBJECT_NAME(I.id))
      + ' ADD CONSTRAINT '       + QUOTENAME(I.[name])
      + CASE OBJECTPROPERTYEX(OBJECT_ID(QUOTENAME(SCHEMA_NAME(CAST(OBJECTPROPERTYEX(I.id,'SchemaID')AS INT)))
      + '.'       + QUOTENAME(I.name)),'IsPrimaryKey')
       WHEN 1 THEN ' PRIMARY KEY ' ELSE ' UNIQUE ' END
      + CASE I.IndID
        WHEN 1 THEN 'CLUSTERED '         
        ELSE 'NONCLUSTERED '
        END
      + ' ('       + @SQL
      + ')'
      + CASE WHEN LEN(@INCLUDE)=0
       THEN ''
       ELSE 
    'INCLUDE('+ @INCLUDE + ')' END
      + ' ON '
      + CASE I.IndID
        WHEN 1 THEN '[FGDATA];'
        ELSE '[FGINDEX];'
        END
      + ''' AS CreateObject'       
FROM sys.sysindexes AS I
       INNER JOIN @IndexList AS IL
       ON I.id = IL.Id
       AND I.indid = IL.IndID
      WHERE IL.PK = @TableID
      RAISERROR('%s',10,1, @sql) WITH NOWAIT       
        EXEC(@SQL)
     END
    END
  END 
GO

Retrieving the commands

As a manual exercise I create a view in my DBA database as follows

USE DBA
GO
CREATE VIEW [dbo].FileGroupMoveCommands AS
SELECT DropOrder AS CommandOrder
      ,TableName
      ,DropObject AS SQLCommand
  FROM DBA.dbo.AdventureWorks_FileGroupMove
UNION ALL
SELECT CreateOrder +5 AS CommandOrder
      ,TableName
      ,CreateObject AS SQLCommand
  FROM DBA.dbo.AdventureWorks_FileGroupMove

This allows me to retrieve the commands I need with a simple SELECT

SELECT *
FROM DBA.dbo.FileGroupMoveCommands
ORDER BY TableName,CommandOrder
For an example for a specific table from Adventureworks try the following command.
SELECT SQLCommand
FROM DBA.dbo.FileGroupMoveCommands
where tablename = '[Sales].[Individual]'
ORDER BY TableName,CommandOrder

This will produce the following T-SQL commands

DROP INDEX [XMLPATH_Individual_Demographics] ON [Sales].[Individual]
DROP INDEX [XMLPROPERTY_Individual_Demographics] ON [Sales].[Individual]
DROP INDEX [XMLVALUE_Individual_Demographics] ON [Sales].[Individual]
DROP INDEX [PXML_Individual_Demographics] ON [Sales].[Individual]
ALTER TABLE [Sales].[Individual] DROP
    CONSTRAINT [PK_Individual_CustomerID] WITH (MOVE TO [FGDATA]) ;
ALTER TABLE [Sales].[Individual] ADD
    CONSTRAINT [PK_Individual_CustomerID] PRIMARY KEY CLUSTERED  ([CustomerID]) ON [FGDATA];
CREATE PRIMARY XML INDEX [PXML_Individual_Demographics] ON [Sales].[Individual]([Demographics])
CREATE XML INDEX [XMLPATH_Individual_Demographics] ON [Sales].[Individual]([Demographics]) USING
        XML INDEX [PXML_Individual_Demographics] FOR PATH
CREATE XML INDEX [XMLPROPERTY_Individual_Demographics] ON [Sales].[Individual]([Demographics]) USING
                    XML INDEX [PXML_Individual_Demographics] FOR PROPERTY
                    CREATE XML INDEX [XMLVALUE_Individual_Demographics] ON [Sales].[Individual]([Demographics]) USING
                            XML INDEX [PXML_Individual_Demographics] FOR VALUE

Executing the commands

I could of course write a simple piece of looping SQL to run all the commands that I have generated.

This would be extraordinarily brave in a data warehouse due to the sheer number of tables and the volumes of data involved.

My data warehouse server would need enough capacity to store twice the current volume of data plus space in TEMPDB, plus some hefty log file space to carry out this automatically.

In reality I am constrained for space so I have to go through a manual process that involves the following

Ensure that the files in FGData and FGIndex are big enough for the proposed data movement.
Select the commands for the particular data movement and copy/paste/execute them
Shrink the PRIMARY files to free up space for the next block of data movement.
Remove the records representing my move from my backup table if the move was entirely successful.process.

Perhaps in a future iteration I will generate some code that introduces basic rules for automatic selection on the basis of the output of sp_spaceused but in the absence of unlimited resources, including downtime there is always going to be a manual element to such a major move.

Conclusion

Storage planning has to be a major part of a data warehouse project. Think of the characteristics of a data warehouse

Huge volumes of data
Heavily indexed to support user queries

Moving data and indexes retrospectively is not a task you want to be doing in such a database. Doing so is very much a case of "I know where I want to go but I wouldn't start from here".

It was an interesting problem to solve but the person who dropped this on me as part of covering for their vacation had better be generous with the beer money at the office Christmas party.

Moving Tables and Indexes to Filegroups En Masse

Determining the requirements

The drop recreate order

Creating the backup tables in the DBA database

Recording foreign key drop and recreate commands

QUOTENAME()

WITH NOCHECK

The TableName field

Drop and recreate XML indexes

Indexes that are not also a constraint

RAISERROR('%s',10,1,
@sql) WITH NOWAIT

FILEGROUPS

UNIQUE INDEX

Indexes that ARE constraints

Retrieving the commands

Executing the commands

Conclusion

Resources

Rate

Share

Categories

Share

Rate

Moving Tables and Indexes to Filegroups En Masse

Determining the requirements

The drop recreate order

Creating the backup tables in the DBA database

Recording foreign key drop and recreate commands

QUOTENAME()

WITH NOCHECK

The TableName field

Drop and recreate XML indexes

Indexes that are not also a constraint

RAISERROR('%s',10,1, @sql) WITH NOWAIT

FILEGROUPS

UNIQUE INDEX

Indexes that ARE constraints

Retrieving the commands

Executing the commands

Conclusion

Resources

Rate

Share

Categories

Share

Rate

Related content

Adding database file

Moving Database Files in SQL Server

What is Denormalization?

Cluster That Index! Part Two

Cluster That Index!

RAISERROR('%s',10,1,
@sql) WITH NOWAIT