Methods to Enable and Disable Indexes in SQL Server

来源:转载

Introduction

Disable indexes feature is available in SQL Server 2005 and all the later versions to prevent user access to the index. In Microsoft SQL Server 2000 and earlier versions of the SQL Server only have a DROP index and RE-CREATE index option. Disable indexes are very useful feature when we need to recognize whether the indexes are useful or not. The index definition remains in metadata when we are disabling an index and index statistics are kept on non-cluster indexes.

However, if you are disabling a clustered index on the table, even then the data still remains on the table, but the table won’t be available for users access. The data of the cluster index will be unavailable for any DML (data manipulation language) operations until the index is dropped or rebuild.

In this blog post I will show you how to disable and enable the index by using SQL Server Management Studio or Transact-SQL

Disable an Index using Transact-SQL

In Object Explorer, click New Query and run the following query on AdventureWorks database to disable IX_Address_StateProvinceID index on Person.Address table

Disable an Index using SQL Server Management Studio (SSMS)

In Object Explorer expands the database by clicking on the plus sign until you get the IX_Address_StateProvinceID index. Then right click on the IX_Address_StateProvinceID and from the drop down list select Disable . Verify the Disable Indexes and click OK.

You can also disable an index form Index Properties dialog box

Click Options under Select a page, column and uncheck Use index option as shown below

Enable an Index using Transact-SQL

There are two different T-SQL statements to enable indexes

ALTER INDEX REBUILD Statement CREATE INDEX WITH DROP_EXISTING Statement Enable Index using ALTER INDEX REBUILD Statement

Enable Index using CREATE INDEX WITH DROP_EXISTING Statement

Enable Index using SQL Server Management Studio (SSMS)

In Object Explorer expands the database by clicking on the plus sign until you get the IX_Address_StateProvinceID index. Then right click on the IX_Address_StateProvinceID and from the drop down list select Rebuild . Click OK on Rebuild Indexes dialog box, to enable the index

You can also enable an index form Index Properties dialog box

Click Options under Select a page, column and check Use index option as shown below



分享给朋友:
您可能感兴趣的文章:
随机阅读: