索引迁移-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

索引迁移

简介:

I have a client that was in need of moving all there indexes from the primary file group to an index file group. There were a total of 25 databases so the time needed to script each one individually would have been overwhelming not to mention the need to perform this across multiple environments.

I scoured the web and found some examples but many either didn't work the way I needed or were complex in their logic and difficult to understand. I took the best ideas of each of those and created the two attached scripts. After execution the results can be copied to a new query window and executed.

Unique constraints associated with indexes required using the alter table command where as the normal indexes only used the drop/create index command. Clustered indexes and foreign keys were excluded for ease of execution, and because I didn’t want to move the data.

Lets breakdown the process and show how it all fits together. These scripts were written for SQL 2000 so system tables rather than management views were used. The scripts will execute on SQL2005 as written but could be modified to use the management views provided.

First we need to collect all the meta-data needed to drop and recreate the indexes, this is the same for both non-unique and unique indexes:


select dbo.sysobjects.id, -- id of table
dbo.sysobjects.name, -- name of table
dbo.sysindexes.indid, -- id of index
dbo.sysindexes.name indname, -- name of index
dbo.sysindexes.origfillfactor, -- fillfactor for index
dbo.sysindexes.groupid -- filegroup where index is currently

The joins needed to gather the information is between sysindexes and sysobjects based on the object id:


from sysindexes inner join
dbo.sysobjects
on dbo.sysindexes.id = dbo.sysobjects.id

The following where clause removes clustered indexes from the selection. Clustered indexes need to be excluded based on the fact that moving a clustered index also moves the associated data. This would in effect nullify the moving of the other indexes since the data and index would once again be located in the same filegroup. All user indexes will be associated with objects of xtype 'U', user table. Only including index ids between 2 and 254 exclude entries associated with text and image data. indexproperty is a system function that display properties of an index. IsClustered is used to exclude the clstered indexes. The default filegroup, Primary, is 1. This can be changed if indexes are already stored in a different filegroup


where (dbo.sysobjects.xtype = 'U') and (sysindexes.indid BETWEEN 2 and 254)
and (indexproperty(sysindexes.id,sysindexes.name,'IsClustered') = 0)
and sysindexes.groupid = 1

By using a subquery all the indexes that are being used as a constraint are excluded. sysconstraints contains a row for each constraint in the current database. Where constid is the object id of the constraint and the name is the index name. A colid of 0 indicates it is a table level constraint as opposed to column level. sysconstraints.id is equal to the sysobject id for the table.


and not exists (select 1 from sysconstraints
join dbo.sysobjects so2
on so2.id = sysconstraints.constid
and so2.name = sysindexes.name
where colid = 0 and sysconstraints.id = dbo.sysobjects.id)

The order by clause sorts the output by table name and index id.

This data is stored in a cursor that is then processed and the appropriate create and drop statements are generated.

Once the cursor is opened and a record fetched the columns that make up the index are needed in the correct order. A select statement with much of the same logic as above is used. Lets take a look at how this is done


SELECT @list=@list +'['+dbo.syscolumns.name+']' +
(
case indexkey_property(@id,@indid,sysindexkeys.colid,'IsDescending')
when 1 then ' DESC ,'
else ' ASC , '
end )

The select appends to variable @list all the columns that make up the index as well as the sort order of the column. Indexkey_property is used to find this value and then either DESC or ASC is added to the column definition.

复制代码

FROM dbo.sysindexes
INNER JOIN dbo.sysobjects on dbo.sysindexes.id = dbo.sysobjects.id
INNER JOIN dbo.sysindexkeys on dbo.sysindexes.id = dbo.sysindexkeys.id
and dbo.sysindexes.indid = dbo.sysindexkeys.indid
INNER JOIN dbo.syscolumns on dbo.sysindexes.id = dbo.syscolumns.id
AND dbo.sysindexkeys.colid = dbo.syscolumns.colid
WHERE (dbo.sysobjects.xtype = 'U') and (dbo.sysindexes.indid = @indid)
and (dbo.sysobjects.id = @id)
复制代码

The select if very similar to the one above with the some of the filters removed since we are working with only the subset of indexes we need to script.


ORDER BY dbo.sysindexes.indid, dbo.sysindexkeys.keyno

The Order BY clause is used to keep the columns in the original order.

Once @list is populated it is time to script the drop and create statements.


set @strsql = 'drop index ['+@tbname+'].['+@indname+']'
print @strsql

The drop index statements only needs the table name and index name. the print statement sends the statement to the results pane of Query Analyzer.

复制代码

IF @fill = 0
SET @fill = 90
--@fill will be returned as zero for system created statistics and possibly for some indexes, but a value of 0 is invalid in the create statement.

IF (indexproperty(@id,@indname,'IsUnique') = 0)
set @strsql = 'create unique index '
else
set @strsql = 'create index '
--Use correct create index statement by including the unique keyword if original index was unique.

set @strsql = @strsql + '['+@indname+'] on [dbo].['+@tbname+']('+@list+') with fillfactor = '+cast(@fill as nvarchar(3)) + ' on [' + @newgroup +']'
print @strsql
复制代码

Prepend the create statement to the remaining command needed for the create. The tablename, indexname, list of columns, and fill factor are all needed along with the new filegroup name. @newgroup is set at the top of the script.

Once the script has completed executing the output should look similar to what it below:

复制代码

drop index [Order Details].[OrdersOrder_Details]
create index [OrdersOrder_Details] on [dbo].[Order Details]([OrderID] ASC ) with fillfactor = 90 on [INDEX]

drop index [Order Details].[ProductID]
create index [ProductID] on [dbo].[Order Details]([ProductID] ASC ) with fillfactor = 90 on [INDEX]

drop index [Order Details].[ProductsOrder_Details]
create index [ProductsOrder_Details] on [dbo].[Order Details]([ProductID] ASC ) with fillfactor = 90 on [INDEX]

drop index [Orders].[CustomerID]
create index [CustomerID] on [dbo].[Orders]([CustomerID] ASC ) with fillfactor = 90 on [INDEX]
复制代码

In order to move the indexes associated with unique constraints the code was modified as detailed below.

复制代码

from sysindexes
inner join dbo.sysobjects
on dbo.sysindexes.id = dbo.sysobjects.id
inner join dbo.sysconstraints
on dbo.sysconstraints.id = dbo.sysobjects.id
and dbo.sysconstraints.colid = 0
inner join dbo.sysobjects so2
on so2.id = dbo.sysconstraints.constid
and so2.name = sysindexes.name
where (dbo.sysobjects.xtype = 'U') and (sysindexes.indid BETWEEN 2 and 254)
and (indexproperty(sysindexes.id,sysindexes.name,'IsClustered') = 0)
and (indexproperty(sysindexes.id,sysindexes.name,'IsUnique') = 1)
and sysindexes.groupid = 1
复制代码

Join the sysindexes table to the sysconstraints table where sysconstraints.colid = 0, signifying this is a table level constrainst, and the constraint is an index as represented by the join to sysobjects so2. This will include all unique index constraints on the table.


and not exists (select 1 from sysreferences fk
where fk.rkeyid = sysindexes.id
and fk.rkeyindid = sysindexes.indid)

Exclude any constraints that are associated with foreign keys. These objects reside in the sysreferences table using the table id and index id.

Cut and paste this output to a new query analyzer window and execute it to move the indexes.

When generating the output for these type of indexes the drop constraint and add constraint clause of the alter table is required as shown below.

复制代码

set @strsql = 'alter table ['+@tbname+'] drop constraint ['+@indname+']'

--Drop the constraint by using the tablename and indexname from the cursor fetch.

set @strsql = 'alter table [dbo].['+@tbname+'] add constraint ['+@indname+'] '
set @strsql = @strsql + ' Unique NonClustered ('+@list+') '
set @strsql = @strsql + ' with (fillfactor = '+cast(@fill as nvarchar(3)) + ') on [' +@newgroup +']'
复制代码

Issue the alter table with add constraint to create the new constraint. @list, @fill and @newgroup are populated the same here as in the normal index move logic.

Using the system tables to create transact sql statements as shown above an entire databases worth of indexes can be moved very small amount of time. SQLServer uses similar methods to return the alter statements that are generated out of Enterprise Manager when a script is saved after altering an object.

The output scripts can be used in a variety of methods to provide a higher quality of life. A couple of examples are that the scripts can be generated during the day and then scheduled for execution during off hours, saved for reuse in different environments involving the same schema.

Improvements could be made to the scripts to include the dropping and recreation of associated foreign keys, using management views for execution on SQLServer 2005 instance.

I hope that by sharing the method used above it will open a door to scripting that may not have been used before.

Resources:

Move_Unique_Constraints.sql | Move_Index.sql



    本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2011/04/01/2002078.html,如需转载请自行联系原作者





版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章