SSIS: Copy Index from one table to another table
Introduction
This article is based on a recent forum post asking whether there is any way to copy index from table to another table as they want to use in SSIS package. (Copy Index widely used in SSIS package to load data fast)
Details
I'm trying to put together all queries how once can copy index Non-Clustered Index or Clustered Index.
Create a two table one is source table(dbo.SourceTable) and another one is destination table (dbo.DestinationTable)
-- Create Source Table with PrimaryKey
CREATE TABLE [dbo].[SourceTable]
(
[ID] [ int ] IDENTITY(1,1) NOT NULL,
[ MONTH ] [ varchar ](8) NOT NULL,
[SALES] [ decimal ](8, 2) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Create NonClustered Index on SourceTable
CREATE NONCLUSTERED INDEX [SourceTable] ON [dbo].[SourceTable]
(
[ MONTH ] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
-- Create Destination Table without any Index.
CREATE TABLE [dbo].[DestinationTable]
(
[ID] [ int ] NOT NULL,
[ MONTH ] [ varchar ](8) NOT NULL,
[SALES] [ decimal ](8, 2) NOT NULL,
) ON [PRIMARY]
n Let's See how table index structure looks like now.
Now let Create a Store procedure, to create copy Index.
CREATE PROCEDURE [dbo].[UspCopyIndex]
@STName varchar (50)
,@SSName varchar (50)
,@DTName varchar (50)
,@DSName varchar (50)
,@ClusteredOnly bit
AS
BEGIN
-- Get all existing indexes
DECLARE cIX CURSOR FOR
SELECT DISTINCT
SCHEMA_NAME(tbl.schema_id) AS [TableSchema],
tbl. name AS [TableName]
,i.Object_ID AS [ObjectId],
i. name AS [IndexName]
,i.Index_ID
,i.is_primary_key IsPrimaryKey
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
WHERE tbl.name = @STName AND SCHEMA_NAME(tbl.schema_id) = @SSName
DECLARE @IxSchema SYSNAME
,@IxTable SYSNAME
, @IxTableID INT
, @IxName SYSNAME
, @IxID INT
,@IsPrimaryKey BIT
-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @IsPrimaryKey
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Check if clustered
IF @ClusteredOnly = 0 OR (@ClusteredOnly = 1 AND INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
BEGIN
DECLARE @IXSQL NVARCHAR(4000)
SET @IXSQL = 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @DSName + '].[' + @DTName + ']'') AND name = N''' + @IxName + ''')' + CHAR(10) + CHAR(13)
IF @IsPrimaryKey = 0
BEGIN
SET @IXSQL = @IXSQL + 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique' ) = 1) SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered' ) = 1) SET @IXSQL = @IXSQL + 'CLUSTERED '
ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @DSName + '].' + @DTName + ' ('
END
ELSE IF @IsPrimaryKey = 1
BEGIN
SET @IXSQL = @IXSQL + 'ALTER TABLE [' + @DSName + '].' + @DTName + ' ADD CONSTRAINT [Aux' + @IxName + '] PRIMARY KEY '
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered' ) = 1) SET @IXSQL = @IXSQL + 'CLUSTERED ('
ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED ('
END
-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name, IC.is_descending_key
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
DECLARE @IxIsDescending BIT
-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1) SET @IxFirstColumn = 0
ELSE SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
IF (@IxIsDescending = 1) SET @IXSQL = @IXSQL + ' DESC' ELSE SET @IXSQL = @IXSQL + ' ASC'
FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
SET @IXSQL = @IXSQL + ') '
-- Get all included columns of the index
DECLARE cIxIncColumn CURSOR FOR
SELECT SC.Name, IC.is_descending_key
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
SET @IxFirstColumn = 1
-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxIncColumn
FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
SET @IXSQL = @IXSQL + 'INCLUDE ('
END
ELSE SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending
IF (@@FETCH_STATUS != 0) SET @IXSQL = @IXSQL + ') '
END
CLOSE cIxIncColumn
DEALLOCATE cIxIncColumn
SET @IXSQL = @IXSQL + ' WITH (MAXDOP=6)'
EXEC (@IXSQL)
END
FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @IsPrimaryKey
END
CLOSE cIX
DEALLOCATE cIX
END
Now Let's Execute the Store procedure to copy Indexes from SourceTable to Destination Table
DECLARE @return_value int
EXEC @return_value = [dbo].[UspCopyIndex]
@STName = N 'SourceTable' ,
@SSName = N 'dbo' ,
@DTName = N 'DestinationTable' ,
@DSName = N 'dbo' ,
@ClusteredOnly = 0
SELECT 'Return Value' = @return_value
GO
Now Let's see our index copied from SourceTable to Destination Table
