Desde SQL Server 2005, y versiones superiores, se
añadió al comando ALTER TABLE para permitir mover una tabla a un grupo de
archivos diferente (filegroups) la cláusula "MOVE TO", Se trata de
repartir las tablas que mas uso tienen y más grandes son, en la medida de lo
posible, entre los dos (o más) Filegrups. En el Filegrups que alojéis el indice
cluster, se moverán-alojaran los datos de las tablas
Siendo un poco escueto, y parto de que tenemos creado
2º y nuevo Filegrups: por ejemplo con el nombre “SECONDARY”, a el cual le
asignamos su archivo .mdf y si es en otro disco distinto donde se encuentre los
.mdf del Filegrups: “PRIMARY”, más que mejor y es lo que recomiendo.
ALTER DATABASE TuBD ADD FILEGROUP SECONDARY
GO
ALTER DATABASE TuBD
ADD FILE
( NAME = File_Data_2,
FILENAME = 'E:...MSSQLDATAFile_Data_2.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP SECONDARY
GO
Pongo como ejemplo una tabla "TabaEjemplo",
que tengo creada. Esta tabla tiene el indice cluster (el cual obtengo y os paso
su script de su incice cluster):
ALTER TABLE [dbo].[TabaEjemplo] ADD CONSTRAINT [PK_TabaEjemplo]
PRIMARY KEY CLUSTERED
(
[ID_FORMULAS_RECALCULO] ASC
)WITH
(PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90) ON [PRIMARY]
GO
Pues bien, para mover la tabla al nuevo Filegrups
SECONDARY, lo hacemos de la forma:
ALTER TABLE [dbo].[TabaEjemplo] DROP CONSTRAINT [PK_TabaEjemplo]
WITH (MOVE TO SECONDARY)
GO
ALTER TABLE [dbo].[TabaEjemplo] ADD CONSTRAINT [PK_TabaEjemplo]
PRIMARY KEY(ID_FORMULAS_RECALCULO) WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90)
GO
Para las tablas que también tengan índices que no sean
Clustered, no se os olvide chequear que
se han movido al Filegrups: “SECONDARY”, si no es así también hay que moverlos,
de no hacerlo, el índice se quedaría almacenado en distinto Filegrups de donde
se almacenan los datos de las tablas... Lo que en o casiones puede ser bueno
por rendimiento, pero no siempre es lo que se quiere
La finalidad de mover los índices nonclustered de un
filegroup a otro, está en la ganancia de rendmimiento que se consigue separando
los índices no agrupados de los datos. No es una mejora muy sustancial, pero es
una recomendación de rendimiento. Este procedimiento nos ayuda a preparar las
sentencias necesarias para llevar a cabo tal acción:
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = Object_id(N'[dbo].[sp_MoveNonClusterIndexes]')
AND Objectproperty(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_movenonclusterindexes]
GO
CREATE PROCEDURE sp_MoveNonClusterIndexes(
@FromFilegroup VARCHAR(250)
,@ToFilegroup VARCHAR(250)
,@OnlyGenerateSentences int=1)
AS
/*
This procedure generates the DML sentences to move
indexes currently in
in file group @FromFilegroup to file group
@ToFilegroup.
Then, the sentences can be either run at your own
convenience
or directly depending on the value of
@OnlyGenerateSentences.
This script could be run periodically to locate indexes
in the
wrong file group
*/
SET nocount ON
DECLARE @objname SYSNAME
,@indid INT
,@keys VARCHAR(2500)
,@objid INT
,@isunique INT
,@isuniqueclause VARCHAR(50)
,@Index_name VARCHAR(255)
,@Query NVARCHAR(4000)
,@res int
,@i INT
,@thiskey NVARCHAR(131) -- 128+3
IF object_id('tempdb..#indexes') > 0
DROP TABLE #indexes
SELECT user_name(uid) + '.' + o.name AS table_name
,o.id AS objid
,i.name AS index_name
,s.groupname AS filegroup
,i.indid
,Isnull(Indexproperty(o.id,i.name,'IsUnique'),0) IsUnique
,CAST(NULL AS VARCHAR(2500)) AS keys
,CAST(NULL AS VARCHAR(4000)) AS query
INTO #indexes
FROM sysindexes i
INNER JOIN sysobjects o
ON i.id = o.id
INNER JOIN sysfilegroups s
ON i.groupid = s.groupid
WHERE indid > 1 --Exclude clustered indexes
AND indid < 255 --Exlude indexes on text fields
AND Indexproperty(o.id,i.name,'IsStatistics') = 0 --Exclude statistics
AND Indexproperty(o.id,i.name,'IsHypothetical') = 0 --Exclude Hypothetical
AND o.TYPE = 'u' --Only user tables,
exclude system tables
AND s.groupname = @FromFilegroup
DECLARE cur CURSOR FOR
SELECT table_name
,objid
,index_name
,indid
,isunique
FROM #indexes
ORDER BY table_name,isunique desc,index_name
OPEN cur
FETCH NEXT FROM cur
INTO @objName
,@objid
,@Index_name
,@indid
,@isunique
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @keys = Quotename(Index_col(@objname,@indid,1),'[')
,@i = 2
IF (indexkey_property(@objid,@indid,1,'isdescending') = 1)
SELECT @keys = @keys + ' DESC'
SELECT @thiskey = Quotename(Index_col(@objname,@indid,@i),'[')
IF ((@thiskey IS NOT
NULL )AND
(indexkey_property(@objid,@indid,@i,'isdescending') = 1))
SELECT @thiskey = @thiskey + ' DESC'
WHILE (@thiskey IS NOT
NULL )
BEGIN
SELECT @keys = @keys + ', ' + @thiskey
,@i = @i + 1
SELECT @thiskey = Index_col(@objname,@indid,@i)
IF ((@thiskey IS NOT
NULL ) AND
(indexkey_property(@objid,@indid,@i,'isdescending') = 1))
SELECT @thiskey = @thiskey + ' DESC'
END
IF @isunique= 0
SET @isuniqueclause=''
else
SET @isuniqueclause=' unique '
SELECT @Query = 'Create ' + @isuniqueclause + ' nonclustered index ' +
@Index_name + ' on ' + @objName + ' (' + @keys + ') with (drop_existing=on)
on [' + @ToFilegroup + '] '
IF @OnlyGenerateSentences =1
PRINT @Query + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
ELSE
BEGIN
RAISERROR ('Moving index %s.%s from %s
to %s...
',0,1,@objName,@Index_name,@FromFilegroup,@ToFilegroup)with nowait
exec @res=sp_executesql @query
IF @res=0
RAISERROR (' Successfully moved index
%s.%s',0,1,@objName,@Index_name)with nowait
ELSE
RAISERROR (' ERROR moving index %s.%s
',0,1,@objName,@Index_name)with
nowait
END
FETCH NEXT FROM cur
INTO @objName
,@objid
,@Index_name
,@indid
,@isunique
END
DEALLOCATE cur
Go
print 'Now, you can execute :'
print ' exec sp_MoveNonClusterIndexes
@FromFilegroup=''FromFileGroup'',
@ToFilegroup=''ToFilegroup'' , @OnlyGenerateSentences =
1 '
print 'For example: '
print ' exec sp_MoveNonClusterIndexes @FromFilegroup=''PRIMARY'',
@ToFilegroup=''INDEXES'' ,
@OnlyGenerateSentences = 1'
Para
entornos SQL 2000:
Para entornos SQL 2000, solo se puedes creando una
copia de la tabla en ese nuevo filegroup para luego mover los datos. Lo que
hará que en un momento dado estén en dos sitios, y hay que tener cuidado para
que no cause problemas con la integridad refernencial. :(. Pero no tiene por
qué ser tan dramático ni que conllevar infinidad de problemas con la integridad
referencial a poco que tengas algo de margen.
Así, creas la tabla con otro nombre en el nuevo filegroup,
le llevas el grueso de los datos (digamos, todos los de la tabla menos los de
hoy). Luego, haciendo un corte en el acceso a la tabla (cuando te dejen, una
ventana de mantenimiento), eliminas las relaciones con la tabla vieja (con un
script), pasas los datos restantes (los de hoy), renombras ambas tablas para
darle a la nueva el nombre bueno y por último vuelves a crear las relaciones de
integridad (también con un script) y abres de nuevo la producción.
Si son muchas tablas, lo puedes hacer de a poco. Si
sólo te dan una ventana de mantenimiento, pero más grande, lo haces de todas
las tablas.
Os dejo este script para SQL Server 2000, que use en su día:
/* Move a table between filegroups */
SET NOCOUNT ON
-- Note - @SourceFileGroupID and @TargetFileGroupID are the IDs of the
-- source and destination file groups. If you are not sure what
-- the IDs of your file groups are, simply run sp_helpfilegroup
-- and look at the groupid column of the result set.
DECLARE @SourceFileGroupID INT
DECLARE @TargetFileGroupID INT
DECLARE @TableToMove NVARCHAR(128)
DECLARE @MovePKAndAllUniqueConstraints BIT
DECLARE @MoveAllNonClusteredIndexes BIT
SET @SourceFileGroupID = 1
SET @TargetFileGroupID = 2
SET @TableToMove = '[test main]'
SET @MovePKAndAllUniqueConstraints = 1
SET @MoveAllNonClusteredIndexes = 1
DECLARE @ScriptMsg NVARCHAR(512)
DECLARE @DatabaseName SYSNAME
DECLARE @ServerName SYSNAME
DECLARE @TableHasCI BIT
DECLARE @TableHasIdent BIT
DECLARE @TableHasPK BIT
DECLARE @TableHasUQ BIT
DECLARE @File1Name NVARCHAR(128)
DECLARE @File2Name NVARCHAR(128)
DECLARE @IdentColName NVARCHAR(128)
DECLARE @ColList NVARCHAR(1024)
DECLARE @indid NVARCHAR(128)
DECLARE @Type CHAR(2)
DECLARE @KeyName NVARCHAR(128)
DECLARE @AssocFKeyName NVARCHAR(128)
DECLARE @FKTableName NVARCHAR(128)
DECLARE @CIName NVARCHAR(128)
DECLARE @IsPadIndex BIT
DECLARE @i INT
DECLARE @j INT
DECLARE @SQLStr NVARCHAR(4000)
-- This temp table holds the column names of keys/constraints, and such.
IF OBJECT_ID('tempdb..#tblColTable', 'U') IS NOT NULL
DROP TABLE #tblColTable
CREATE TABLE #tblColTable (
Idx INT IDENTITY(1, 1),
ColName NVARCHAR(128),
IdxOrder CHAR(4))
-- This temp table is used to store the key/constraint properties
-- of the moved table.
IF OBJECT_ID('tempdb..#tblKeysTable', 'U') IS NOT NULL
DROP TABLE #tblKeysTable
CREATE TABLE #tblKeysTable (
Idx INT IDENTITY(1, 1),
KeyName NVARCHAR(128),
indid INT,
Type CHAR(2))
-- This temp table holds the foreign keys of the table.
-- The SQLStmt column is used to build dynamic SQL statements
-- that are related to these foreign keys.
IF OBJECT_ID('tempdb..#tblFKTable', 'U') IS NOT NULL
DROP TABLE #tblFKTable
CREATE TABLE #tblFKTable (
Idx INT IDENTITY(1, 1),
ForeignTableName NVARCHAR(128),
KeyName NVARCHAR(128),
SQLStmt NVARCHAR(1024))
-- This temp table holds the colunms of the foriegn key of the table.
IF OBJECT_ID('tempdb..#tblFKColTable', 'U') IS NOT NULL
DROP TABLE #tblFKColTable
CREATE TABLE #tblFKColTable (
Idx INT IDENTITY(1, 1),
ColName NVARCHAR(128),
FOrP CHAR(1))
-- Get server and database names
SET @ServerName = CAST(ISNULL(SERVERPROPERTY('ServerName'), 'Unknown') AS SYSNAME)
SET @DatabaseName = db_name()
-- Some basic verifications:
-- 1. Check that file groups exist, and that the table exists.
SET @File1Name = FILEGROUP_NAME(@SourceFileGroupID)
IF @File1Name IS NULL
BEGIN
IF @SourceFileGroupID IS NULL
SET @SourceFileGroupID = 'NULL'
SET @ScriptMsg = N'The source file group ' + CAST(@SourceFileGroupID AS VARCHAR(64)) + N' does not exist on the database ' + @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid filegroup id.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
SET @File2Name = FILEGROUP_NAME(@TargetFileGroupID)
IF @File2Name IS NULL
BEGIN
IF @TargetFileGroupID IS NULL
SET @TargetFileGroupID = 'NULL'
SET @ScriptMsg = N'The target file group ' + CAST(@TargetFileGroupID AS VARCHAR(64)) + N' does not exist on the database ' + @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid filegroup id.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF @SourceFileGroupID = @TargetFileGroupID
BEGIN
SET @ScriptMsg = N'The file groups provided are the same. This is not allow in this script.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
SET @TableToMove = LTRIM(RTRIM(@TableToMove))
IF RIGHT(@TableToMove, 1) = ']'
SET @TableToMove = LEFT(@TableToMove, LEN(@TableToMove) - 1)
IF LEFT(@TableToMove, 1) = '['
SET @TableToMove = RIGHT(@TableToMove, LEN(@TableToMove) - 1)
-- Validate the table name and check that it exists in the system catalog.
IF @TableToMove IS NULL OR @TableToMove = ''
BEGIN
SET @ScriptMsg = N'The table name provided in the script is either null or empty, on server '
+ @ServerName + N' and database ' + @DatabaseName
+ N'. Please provide a valid table name.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableToMove AND TABLE_TYPE = 'BASE TABLE')
BEGIN
SET @ScriptMsg = N'The table name provided in the script is not found in database '
+ @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid table name.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- Check that the table is indeed defined on the source file group.
IF (SELECT TOP 1 groupid FROM sysindexes WHERE id = OBJECT_ID(@TableToMove) and indid IN (0, 1)) <> @SourceFileGroupID
BEGIN
SET @ScriptMsg = N'The table ' + @TableToMove + ' is not found on filegroup ' + CAST(@SourceFileGroupID AS VARCHAR(32))
+ N'. Please provide a valid table name and source file group.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- 2. Check that the target file group is not read-only.
IF FILEGROUPPROPERTY(FILEGROUP_NAME(@TargetFileGroupID), 'IsReadOnly') = 1
BEGIN
SET @ScriptMsg = N'The taget file group (i.e., with file group id = ' + CAST(@TargetFileGroupID AS VARCHAR(32)) + N') is read-only. Aborting table move.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- 3. If we have gotten this far, then it is ok to move the table to the
-- requested filegroup.
-- First thing first: Check whether the table has a clustered index.
SET @TableHasCI = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasClustIndex')
-- If not - check whether the table has an identity column.
-- If it does - apply the CI with the new filegroup on the identity column.
-- Once done - remove the CI. If it does not - check whether the table has a primary
-- key and apply the CI there on the new file group, and then drop the CI.
-- If the table does not have an identity column, or a primary key,
-- then a new identity column is created for the table and the CI
-- is applied on it, and then the CI and the identity column are removed.
-- This whole shabang is done in order to make the CI creation as fast as possible.
-- The case where the table does not have a clustered index to begin with implies
-- bad table design, and should not be common anyhow.
IF @TableHasCI = 0
BEGIN
SET @TableHasIdent = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasIdentity')
IF @TableHasIdent = 0
BEGIN
SET @TableHasPK = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasPrimaryKey')
SET @TableHasUQ = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasUniqueCnst')
-- Only if the table has no PK/UQ or clustered index, then create an identity
-- column on it. This new column will hold the CI.
IF @TableHasPK = 0 AND @TableHasUQ = 0
BEGIN
EXEC(N' ALTER TABLE [' + @TableToMove + N'] ADD
[This_Is_My_Ident_Col_Name] BIGINT IDENTITY (1, 1) ')
SET @IdentColName = 'This_Is_My_Ident_Col_Name'
-- Apply the CI on the identity column. We don't create the CI
-- as unique, since the identity column may be non-unique,
-- due to reseeding.
EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
ON [' + @TableToMove + N']([' + @IdentColName + '])
ON [' + @File2Name + N']')
-- The table is now moved -> Remove the CI.
EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
-- Finally, remove the added identity column
EXEC(N' ALTER TABLE [' + @TableToMove + N']
DROP COLUMN [This_Is_My_Ident_Col_Name] ')
END
ELSE
BEGIN
-- In this case, the table has a PK/UQ, so we might as well
-- apply the CI on the column(s) of the PK/UQ.
-- First, get the column(s) of the PK/UQ.
SELECT @KeyName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH (NOLOCK)
WHERE TABLE_NAME = @TableToMove
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
IF @@ROWCOUNT = 0
SELECT TOP 1 @KeyName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH (NOLOCK)
WHERE TABLE_NAME = @TableToMove
AND CONSTRAINT_TYPE = 'UNIQUE'
-- The varialbe @KeyName now holds the name of the PK/UQ
INSERT INTO #tblColTable (ColName, IdxOrder)
SELECT COL_NAME(OBJECT_ID(@TableToMove), colid),
-- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID(@TableToMove),
INDEXPROPERTY(OBJECT_ID(@TableToMove),
@KeyName,
'IndexID'),
keyno,
'IsDescending') = 1
THEN 'DESC'
ELSE 'ASC'
END
FROM sysindexkeys
WHERE id = OBJECT_ID(@TableToMove)
AND indid = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IndexID')
IF @@ROWCOUNT > 0
SET @i = 1
SET @ColList = N''
WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
BEGIN
SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
FROM #tblColTable
WHERE Idx = @i
SET @i = @i + 1
END
SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
-- Now, apply the CI on the primary key columns. The CI is not
-- created as a unique CI, since if the PK/UQ was added with the NOCHECK
-- option, there could be duplicate entries in the PK/UQ.
EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
ON [' + @TableToMove + N'](' + @ColList + ')
ON [' + @File2Name + N']')
-- The last command moved the CI (and thus the table), so we
-- can now drop the CI.
EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
END
END
ELSE
BEGIN
-- Here, the table originally had an identity. We apply the CI
-- on the identity column, and then remove it.
SELECT @IdentColName = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
WHERE TABLE_NAME = @TableToMove
AND COLUMNPROPERTY(OBJECT_ID(@TableToMove), COLUMN_NAME, 'IsIdentity') = 1
EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
ON [' + @TableToMove + N']([' + @IdentColName + '])
ON [' + @File2Name + N']')
-- The table is now moved -> Remove the CI.
EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
END
END
ELSE
BEGIN
-- Now, for the big ELSE. The ELSE applies to the case where the
-- table already has a clustered index. Here, we select the name of the
-- existing clustered index, then drop it from the table, and recreate
-- it on the other filegroup (on the same columns and order as was
-- originally defined for the table).
-- If the CI is also a PK/UQ/unique index, then we first check all foreign
-- keys for the PK/UQ/UI, drop them if they exist, drop the PK/UQ/UI
-- then recreate the PK/UQ/UI as CLUSTERED, and then reapply all the
-- foreign keys constraints. If the CI is non-unique (thus is not
-- associated with a PK/UQ/UI), we just drop and recreate it on the
-- target file group.
SELECT @CIName = [name]
FROM sysindexes WITH (NOLOCK)
WHERE id = OBJECT_ID(@TableToMove)
AND indid = 1
DELETE FROM #tblColTable
INSERT INTO #tblColTable (ColName, IdxOrder)
SELECT COL_NAME(OBJECT_ID(@TableToMove), colid),
-- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID(@TableToMove),
INDEXPROPERTY(OBJECT_ID(@TableToMove),
@CIName,
'IndexID'),
keyno,
'IsDescending') = 1
THEN 'DESC'
ELSE 'ASC'
END
FROM sysindexkeys WITH (NOLOCK)
WHERE id = OBJECT_ID(@TableToMove)
AND indid = 1
ORDER BY keyno ASC
SELECT @i = MIN(Idx)
FROM #tblColTable
SET @ColList = N''
WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
BEGIN
SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
FROM #tblColTable
WHERE Idx = @i
SET @i = @i + 1
END
SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
-- Check whether the clustered index is also the PK, or a unique constraint (UQ),
-- or a unique index (UI) that is neither a PK or a UQ.
-- If the CI is either one of the above, we first check whether any foreign keys
-- reference this PK/UQ/UI. If so - we drop the FKs, then drop the PK/UQ/UI,
-- then recreate the PK/UQ/UI on the target filegroup, and then recreate all
-- the foreign keys dropped earlier.
-- If the CI is other than the above (i.e., it is a non-unique clustered index)
-- then we simply drop it and recreate it on the target filegroup.
IF OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsPrimaryKey') = 1
OR OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsUniqueCnst') = 1
OR INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IsUnique') = 1
BEGIN
-- This case stands for a CI which is a PK/UQ/UI.
-- First, we drop all foreign keys associated with the PK/UQ/UI.
-- These FK constraints will be reapplied on the PK later,
-- (i.e., after the PK/UQ/UI is recreated on the target filegroup).
DELETE FROM #tblFKTable
-- Get all the FK constraints associated with the PK/UQ/UI.
-- Here, we query sysreferences so we could get our hands on all the
-- foreign keys that reference the PK/UQ/UI of the table
-- that needs to be moved.
INSERT INTO #tblFKTable (ForeignTableName, KeyName)
SELECT OBJECT_NAME(fkeyid), OBJECT_NAME(constid)
FROM sysreferences WITH (NOLOCK)
WHERE rkeyid = OBJECT_ID(@TableToMove)
AND rkeyindid = INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IndexID')
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHILE @AssocFKeyName IS NOT NULL
BEGIN
-- Get the list of primary and then foreign columns
-- for the collected FK constraints. The CASCADE UPDATE,
-- CASCADE DELETE, and NOT FOR REPLICATION properties
-- of the FK are considered later.
SELECT @FKTableName = ForeignTableName
FROM #tblFKTable
WHERE KeyName = @AssocFKeyName
DELETE FROM #tblFKColTable
-- First, the tables of the foreign table. The select is ordered by keyno
-- so the order of columns in the FK will remain unchanged by the
-- drop/recreate operation.
INSERT INTO #tblFKColTable (ColName, FOrP)
SELECT COL_NAME(fkeyid, fkey), 'F'
FROM sysforeignkeys
WHERE constid = OBJECT_ID(@AssocFKeyName)
ORDER BY keyno
-- Similarly, for the primary table.
INSERT INTO #tblFKColTable (ColName, FOrP)
SELECT COL_NAME(rkeyid, rkey), 'P'
FROM sysforeignkeys
WHERE constid = OBJECT_ID(@AssocFKeyName)
ORDER BY keyno
-- We now build the FK creation statement
SELECT @j = MIN(Idx)
FROM #tblFKColTable
SET @SQLStr = N'ALTER TABLE [' + @FKTableName + N'] '
+ N' WITH NOCHECK ADD CONSTRAINT [' + @AssocFKeyName + N'] '
+ N' FOREIGN KEY ('
-- Foreign table columns
WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'F')
BEGIN
SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
FROM #tblFKColTable
WHERE Idx = @j
SET @j = @j + 1
END
SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + N') REFERENCES [' + @TableToMove + N']('
-- Primary table columns
WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'P')
BEGIN
SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
FROM #tblFKColTable
WHERE Idx = @j
SET @j = @j + 1
END
SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + ') '
IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsDeleteCascade') = 1
SET @SQLStr = @SQLStr + N' ON DELETE CASCADE '
IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsUpdateCascade') = 1
SET @SQLStr = @SQLStr + N' ON UPDATE CASCADE '
IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsNotRepl') = 1
SET @SQLStr = @SQLStr + N' NOT FOR REPLICATION '
-- Now, store this SQL statement, and drop the FK constraint
UPDATE #tblFKTable
SET SQLStmt = @SQLStr
WHERE KeyName = @AssocFKeyName
-- Drop the constraint
EXEC(N'ALTER TABLE [' + @FKTableName + '] DROP CONSTRAINT [' + @AssocFKeyName + N']')
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHERE @AssocFKeyName < KeyName
END
-- The CREATE statement is different for PK or UQ
IF OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsPrimaryKey') = 1
BEGIN
EXEC(N' ALTER TABLE [' + @TableToMove + N']
DROP CONSTRAINT [' + @CIName + N'] ')
EXEC(N' ALTER TABLE [' + @TableToMove + N']
WITH NOCHECK ADD CONSTRAINT [' + @CIName + N']
PRIMARY KEY CLUSTERED (' + @ColList + N')
ON [' + @File2Name + N']')
END
ELSE
BEGIN
IF OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsUniqueCnst') = 1
BEGIN
EXEC(N' ALTER TABLE [' + @TableToMove + N']
DROP CONSTRAINT [' + @CIName + N'] ')
EXEC(N' ALTER TABLE [' + @TableToMove + N']
WITH NOCHECK ADD CONSTRAINT [' + @CIName + N']
UNIQUE CLUSTERED (' + @ColList + N')
ON [' + @File2Name + N']')
END
ELSE -- the CI here is a unique index
BEGIN
SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IsPadIndex')
EXEC(N'DROP INDEX [' + @TableToMove + N'].[' + @CIName + N']')
-- Recreate the index on the same columns and column order, as
-- they were defined on the original table, and in this
-- case, the CI is kept unique.
IF @IsPadIndex = 1
EXEC(N'CREATE UNIQUE CLUSTERED INDEX [' + @CIName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
WITH PAD_INDEX
ON [' + @File2Name + N']')
ELSE
EXEC(N'CREATE UNIQUE CLUSTERED INDEX [' + @CIName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
ON [' + @File2Name + N']')
END
END
-- Recreate the FK constraint of the table.
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHILE @AssocFKeyName IS NOT NULL
BEGIN
SELECT @SQLStr = SQLStmt
FROM #tblFKTable
WHERE KeyName = @AssocFKeyName
EXEC(@SQLStr)
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHERE @AssocFKeyName < KeyName
END
END
ELSE
BEGIN
-- Here, the CI is not a PK/UQ/UI, so we drop the CI from
-- the current filegroup, and recreate it on the
-- target filegroup, as a non-unique index.
SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IsPadIndex')
EXEC(N'DROP INDEX [' + @TableToMove + N'].[' + @CIName + N']')
-- Recreate the index on the same columns and column order, as
-- they were defined on the original table, and in this
-- case, the CI is not unique.
IF @IsPadIndex = 1
EXEC(N'CREATE CLUSTERED INDEX [' + @CIName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
WITH PAD_INDEX
ON [' + @File2Name + N']')
ELSE
EXEC(N'CREATE CLUSTERED INDEX [' + @CIName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
ON [' + @File2Name + N']')
END
END
-- Great. Now the table is on the new file group.
-- We now check the @MovePKAndAllUniqueConstraints bit,
-- and if it is 1, we move the PK and all unique constraints
-- of the table, to the new file group.
-- Similarly, if the @MoveAllNonClusteredIndexes = 1 then
-- we move the non-clustered indexes of the table to
-- the new file group as well. The structure of the code
-- for the UQ, PK and non-clustered indexes is the same,
-- so we move them all together (with minor syntax changes where needed).
-- One comment: The fillfactor and padindex are not carried over,
-- for the indexes and constraints. A good DBA would set the defaults
-- on both filegroups the same.
-- Gather all the unique constraints that need to be moved to
-- the new file group.
IF @MovePKAndAllUniqueConstraints = 1
BEGIN
-- Get all unique keys (including the PK) for the table.
INSERT INTO #tblKeysTable (KeyName, indid, type)
SELECT a.[name], a.indid, b.xtype
FROM sysindexes a WITH (NOLOCK)
INNER JOIN sysobjects b WITH (NOLOCK)
ON a.[name] = b.[name]
WHERE b.parent_obj = OBJECT_ID(@TableToMove)
AND b.xtype IN ('PK', 'UQ')
AND a.groupid = @SourceFileGroupID
AND a.indid > 0 AND a.indid < 255
END
-- Gather all the non-clustered indexes that need to be moved to
-- the new file group.
IF @MoveAllNonClusteredIndexes = 1
BEGIN
INSERT INTO #tblKeysTable (KeyName, indid, type)
SELECT a.[name],
a.indid,
CASE
WHEN INDEXPROPERTY(OBJECT_ID(@TableToMove), a.[name], 'IsUnique') = 1
THEN 'UI' -- to denote unique index.
ELSE 'I' -- to denote a non-unique index
END
FROM sysindexes a WITH (NOLOCK)
LEFT OUTER JOIN #tblKeysTable b
ON a.name = b.KeyName
WHERE a.id = OBJECT_ID(@TableToMove)
AND INDEXPROPERTY(OBJECT_ID(@TableToMove), a.[name], 'IsStatistics') = 0
AND a.groupid = @SourceFileGroupID
AND b.KeyName IS NULL -- do not collect the PK and UQs again!
AND a.indid > 0 AND a.indid < 255
END
-- Now, loop through all keys/indexes collected in #tblKeysTable
-- and move them one by one to the new filegroup, while
-- maintaining the same column order they were previously defined on.
WHILE EXISTS(SELECT * FROM #tblKeysTable)
BEGIN
SET @indid = NULL
SET @Type = NULL
SET @KeyName = NULL
SELECT TOP 1 @indid = indid,
@Type = Type,
@KeyName = KeyName
FROM #tblKeysTable
DELETE FROM #tblColTable
INSERT INTO #tblColTable (ColName, IdxOrder)
SELECT COL_NAME(OBJECT_ID(@TableToMove), colid),
-- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID(@TableToMove),
INDEXPROPERTY(OBJECT_ID(@TableToMove),
@KeyName,
'IndexID'),
keyno,
'IsDescending') = 1
THEN 'DESC'
ELSE 'ASC'
END
FROM sysindexkeys WITH (NOLOCK)
WHERE id = OBJECT_ID(@TableToMove)
AND indid = @indid
ORDER BY keyno ASC
SELECT @i = MIN(Idx)
FROM #tblColTable
SET @ColList = N''
WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
BEGIN
SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
FROM #tblColTable
WHERE Idx = @i
SET @i = @i + 1
END
SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
-- Drop the object, and then recreate it on the new filegroup.
-- Note: If a PK/UQ or a unique index (UI) is on the source file group,
-- then it must be a NONCLUSTERED PK/UQ, since the CLUSTERED PK/UQ/UI was
-- already handled above.
-- Also - we first check whether the PK/UQ/UI are used in any foreign keys, before
-- we drop/recreate them. If they are - then the FKs are first dropped,
-- then the PK/UQ/UI is dropped and recreated, and the FK constraints
-- are then recreated as well.
IF @Type IN ('PK', 'UQ', 'UI')
BEGIN
DELETE FROM #tblFKTable
-- Get all the FK constraints associated with the PK/UQ/UI (UI = unique index).
-- Here, we must query sysreferences since it can identify which
-- foreign keys reference the table that we are moving, as well as
-- each individual unique constraint/index.
INSERT INTO #tblFKTable (ForeignTableName, KeyName)
SELECT OBJECT_NAME(fkeyid), OBJECT_NAME(constid)
FROM sysreferences WITH (NOLOCK)
WHERE rkeyid = OBJECT_ID(@TableToMove)
AND rkeyindid = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IndexID')
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHILE @AssocFKeyName IS NOT NULL
BEGIN
-- Get the list of primary and then foreign columns
-- for the collected FK constraints. The CASCADE UPDATE,
-- CASCADE DELETE, and NOT FOR REPLICATION properties
-- of the FK are considered later.
SELECT @FKTableName = ForeignTableName
FROM #tblFKTable
WHERE KeyName = @AssocFKeyName
DELETE FROM #tblFKColTable
-- First, the tables of the foreign table. The select is ordered by keyno
-- so the order of columns in the FK will remain unchanged by the
-- drop/recreate operation.
INSERT INTO #tblFKColTable (ColName, FOrP)
SELECT COL_NAME(fkeyid, fkey), 'F'
FROM sysforeignkeys
WHERE constid = OBJECT_ID(@AssocFKeyName)
ORDER BY keyno
-- Similarly, for the primary table.
INSERT INTO #tblFKColTable (ColName, FOrP)
SELECT COL_NAME(rkeyid, rkey), 'P'
FROM sysforeignkeys
WHERE constid = OBJECT_ID(@AssocFKeyName)
ORDER BY keyno
-- We now build the FK creation statement
SELECT @j = MIN(Idx)
FROM #tblFKColTable
SET @SQLStr = N'ALTER TABLE [' + @FKTableName + N'] '
+ N' WITH NOCHECK ADD CONSTRAINT [' + @AssocFKeyName + N'] '
+ N' FOREIGN KEY ('
-- Foreign table columns
WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'F')
BEGIN
SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
FROM #tblFKColTable
WHERE Idx = @j
SET @j = @j + 1
END
SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + N') REFERENCES [' + @TableToMove + N']('
-- Primary table columns
WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'P')
BEGIN
SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
FROM #tblFKColTable
WHERE Idx = @j
SET @j = @j + 1
END
SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + ') '
IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsDeleteCascade') = 1
SET @SQLStr = @SQLStr + N' ON DELETE CASCADE '
IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsUpdateCascade') = 1
SET @SQLStr = @SQLStr + N' ON UPDATE CASCADE '
IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsNotRepl') = 1
SET @SQLStr = @SQLStr + N' NOT FOR REPLICATION '
-- Now, store this SQL statement, and drop the FK constraint
UPDATE #tblFKTable
SET SQLStmt = @SQLStr
WHERE KeyName = @AssocFKeyName
-- Drop the constraint
EXEC(N'ALTER TABLE [' + @FKTableName + '] DROP CONSTRAINT [' + @AssocFKeyName + N']')
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHERE @AssocFKeyName < KeyName
END
-- Now, drop the PK/UQ/UI and recreate it
IF @Type = 'PK'
BEGIN
EXEC(N' ALTER TABLE [' + @TableToMove + N']
DROP CONSTRAINT [' + @KeyName + N'] ')
EXEC(N' ALTER TABLE [' + @TableToMove + N']
WITH NOCHECK ADD CONSTRAINT [' + @KeyName + N']
PRIMARY KEY NONCLUSTERED (' + @ColList + N')
ON [' + @File2Name + N']')
END
ELSE
BEGIN
IF @Type = 'UQ'
BEGIN
EXEC(N' ALTER TABLE [' + @TableToMove + N']
DROP CONSTRAINT [' + @KeyName + N'] ')
EXEC(N' ALTER TABLE [' + @TableToMove + N']
WITH NOCHECK ADD CONSTRAINT [' + @KeyName + N']
UNIQUE NONCLUSTERED (' + @ColList + N')
ON [' + @File2Name + N']')
END
ELSE -- must be UI
BEGIN
SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IsPadIndex')
EXEC(N' DROP INDEX [' + @TableToMove + N'].[' + @KeyName + N']')
IF @IsPadIndex = 1
EXEC(N' CREATE UNIQUE NONCLUSTERED INDEX [' + @KeyName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
WITH PAD_INDEX
ON [' + @File2Name + N']')
ELSE
EXEC(N' CREATE UNIQUE NONCLUSTERED INDEX [' + @KeyName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
ON [' + @File2Name + N']')
END
END
-- And recreate the FK constraints attached to the PK/UQ
-- that was just recreated.
-- Recreate the FK constraint of the table.
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHILE @AssocFKeyName IS NOT NULL
BEGIN
SELECT @SQLStr = SQLStmt
FROM #tblFKTable
WHERE KeyName = @AssocFKeyName
EXEC(@SQLStr)
SELECT @AssocFKeyName = MIN(KeyName)
FROM #tblFKTable
WHERE @AssocFKeyName < KeyName
END
END
IF @Type = 'I'
BEGIN
SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IsPadIndex')
EXEC(N' DROP INDEX [' + @TableToMove + N'].[' + @KeyName + N']')
IF @IsPadIndex = 1
EXEC(N' CREATE NONCLUSTERED INDEX [' + @KeyName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
WITH PAD_INDEX
ON [' + @File2Name + N']')
ELSE
EXEC(N' CREATE NONCLUSTERED INDEX [' + @KeyName + N']
ON [' + @TableToMove + N'](' + @ColList + N')
ON [' + @File2Name + N']')
END
DELETE FROM #tblKeysTable
WHERE KeyName = @KeyName
AND indid = @indid
END
GO
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.