On one of our customer we had to move all nonclustered indexes to another filegroup and we have found a solution for it and I wanted to share with you.
Actually if you have not too much indexes on your database you can go your all indexes one by one and get create script with DROP_EXISTING=ON option and you can create your indexes on another filegroup. if you dont know about DROP_EXISTING option, when you create your indexes with this option, SQL Server will create new index and delete the old one. So if you change ON PRIMARY to ON ANOTHERFILEGROUP on your index creation script with this option you will move your indexes.
However in many systems there are too much nonclustered indexes and sometimes that may be hard to seperate pk and nonclustered indexes. Below script have helped me alot.
Move All Nonclustered Index to Another Filegroup
This script will give the current create script of all nonclustered indexes in your database. Dont forget to change ON PRIMARY to ON AnotherFilegroup and if you want you can add ONLINE=ON,MAXDOP=10 or something else,FILLFACTOR=90. Generally I am using them.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 |
DECLARE @SchemaName VARCHAR(100) DECLARE @TableName VARCHAR(256) DECLARE @IndexName VARCHAR(256) DECLARE @ColumnName VARCHAR(100) DECLARE @is_unique VARCHAR(100) DECLARE @IndexTypeDesc VARCHAR(100) DECLARE @FileGroupName VARCHAR(100) DECLARE @is_disabled VARCHAR(100) DECLARE @IndexOptions VARCHAR(MAX) DECLARE @IsDescendingKey INT DECLARE @IsIncludedColumn INT DECLARE @TSQLScripCreationIndex VARCHAR(MAX) DECLARE @TSQLScripDisableIndex VARCHAR(MAX) DECLARE CursorIndex CURSOR FOR SELECT schema_name = SCHEMA_NAME(t.schema_id) ,t.name ,ix.name ,CASE WHEN ix.is_unique = 1 THEN 'UNIQUE ' ELSE '' END ,ix.type_desc ,IndexOptions = CASE WHEN ix.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END + CASE WHEN ix.allow_page_locks = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END + CASE WHEN ix.allow_row_locks = 1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END + CASE WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END + CASE WHEN ix.ignore_dup_key = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END + 'SORT_IN_TEMPDB = OFF, FILLFACTOR = 90' ,ix.is_disabled ,FileGroupName = FILEGROUP_NAME(ix.data_space_id) FROM sys.tables AS t INNER JOIN sys.indexes AS ix ON t.object_id = ix.object_id WHERE ix.type > 0 AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName AND t.is_ms_shipped = 0 AND t.name <> 'sysdiagrams' AND ix.data_space_id <= 32767 ORDER BY SCHEMA_NAME(t.schema_id) ,t.name ,ix.name OPEN CursorIndex FETCH NEXT FROM CursorIndex INTO @SchemaName ,@TableName ,@IndexName ,@is_unique ,@IndexTypeDesc ,@IndexOptions ,@is_disabled ,@FileGroupName WHILE (@@fetch_status = 0) BEGIN DECLARE @IndexColumns VARCHAR(MAX) DECLARE @IncludedColumns VARCHAR(MAX) SET @IndexColumns = '' SET @IncludedColumns = '' DECLARE CursorIndexColumn CURSOR FOR SELECT col.name ,ixc.is_descending_key ,ixc.is_included_column FROM sys.tables AS tb INNER JOIN sys.indexes AS ix ON tb.object_id = ix.object_id INNER JOIN sys.index_columns AS ixc ON ix.object_id = ixc.object_id AND ix.index_id = ixc.index_id INNER JOIN sys.columns AS col ON ixc.object_id = col.object_id AND ixc.column_id = col.column_id WHERE ix.type > 0 AND ( ix.is_primary_key = 0 OR ix.is_unique_constraint = 0 ) AND SCHEMA_NAME(tb.schema_id) = @SchemaName AND tb.name = @TableName AND ix.name = @IndexName ORDER BY ixc.index_column_id OPEN CursorIndexColumn FETCH NEXT FROM CursorIndexColumn INTO @ColumnName ,@IsDescendingKey ,@IsIncludedColumn WHILE (@@fetch_status = 0) BEGIN IF @IsIncludedColumn = 0 SET @IndexColumns = @IndexColumns + @ColumnName + CASE WHEN @IsDescendingKey = 1 THEN ' DESC, ' ELSE ' ASC, ' END ELSE SET @IncludedColumns = @IncludedColumns + @ColumnName + ', ' FETCH NEXT FROM CursorIndexColumn INTO @ColumnName ,@IsDescendingKey ,@IsIncludedColumn END CLOSE CursorIndexColumn DEALLOCATE CursorIndexColumn SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1) SET @IncludedColumns = CASE WHEN LEN(@IncludedColumns) > 0 THEN SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1) ELSE '' END -- print @IndexColumns -- print @IncludedColumns SET @TSQLScripCreationIndex = '' SET @TSQLScripDisableIndex = '' SET @TSQLScripCreationIndex = 'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '(' + @IndexColumns + ') ' + CASE WHEN LEN(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns + ')' ELSE '' END + CHAR(13) + 'WITH (' + @IndexOptions + ', ONLINE=ON, MAXDOP=10, DROP_EXISTING=ON) ON TABLESGROUP' + ';' IF @is_disabled = 1 SET @TSQLScripDisableIndex = CHAR(13) + 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) PRINT @TSQLScripCreationIndex PRINT @TSQLScripDisableIndex FETCH NEXT FROM CursorIndex INTO @SchemaName ,@TableName ,@IndexName ,@is_unique ,@IndexTypeDesc ,@IndexOptions ,@is_disabled ,@FileGroupName END CLOSE CursorIndex DEALLOCATE CursorIndex |
Here is the link where I have found this create script.
https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/
