{"id":86,"date":"2014-10-13T09:52:01","date_gmt":"2014-10-13T09:52:01","guid":{"rendered":"http:\/\/www.codereview.co\/?p=86"},"modified":"2014-10-13T10:56:00","modified_gmt":"2014-10-13T10:56:00","slug":"how-to-disable-indexes-in-sql-server-2005-and-2008","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/how-to-disable-indexes-in-sql-server-2005-and-2008\/","title":{"rendered":"How to disable indexes in SQL Server 2005 and 2008?"},"content":{"rendered":"

Scenario proposal<\/strong><\/span><\/p>\n

While looking through the new features in SQL Server 2005 and SQL Server 2008 we found a potentially interesting one called Disabling Indexes,<\/strong> which can be used to disable indexes on a table or a view. Can you give us a detailed explanation of how we go about using this new feature along with examples?<\/span><\/p>\n

Solution<\/strong><\/span>
\n
\nDatabase Administrators can use the Disabling Indexes feature which is available in SQL Server 2005 and later versions to prevent the index usage by user queries. This feature is very useful for DBA’s when they need to figure out whether the indexes which are available on a table are really useful or not. When you are disabling an index the index definition remains in metadata and index statistics are also kept on non-clustered indexes. However, disabling a clustered index or a non-clustered index on a view physically deletes the index data. If you are disabling a clustered index on a table then the table won’t be available for user access, however the data will still remain in the table, but it will be unavailable for any DML operation until the index is rebuilt or dropped. You can use ALTER INDEX REBUILD <\/strong>to rebuild an index and CREATE INDEX WITH DROP_EXISTING<\/strong> statement to enable a disabled index. In this tip I will be using the Person.Address <\/strong>table which is available in the AdventureWorks<\/strong> database.<\/span><\/p>\n

Disabling Indexes <\/strong><\/span><\/p>\n

There are different ways by which you can disable an index. Lets us go by each option one by one.<\/span><\/p>\n

\n
Disabling Index Using T-SQL<\/strong> Execute the below T-SQL to disable IX_Address_StateProvinceID index which is available on Person.Address table of AdventureWorks database.<\/span><\/li>\n<\/ol>\n\n\n\n
USE\u00a0AdventureWorks GO ALTER\u00a0INDEX\u00a0IX_Address_StateProvinceID\u00a0ON\u00a0Person.Address\u00a0DISABLE GO —\u00a0Query\u00a0to\u00a0check\u00a0Index\u00a0Usage SELECT\u00a0NAME\u00a0AS\u00a0[IndexName],\u00a0TYPE_DESC\u00a0AS\u00a0[IndexType], CASE\u00a0IS_DISABLED\u00a0 WHEN\u00a00\u00a0THEN\u00a0‘Enabled’ ELSE\u00a0‘Disabled’\u00a0 END\u00a0AS\u00a0[IndexUsage],\u00a0 FILL_FACTOR\u00a0AS\u00a0[FillFactor]\u00a0FROM\u00a0SYS.INDEXES WHERE\u00a0OBJECT_ID\u00a0=\u00a0OBJECT_ID(‘Person.Address’)\u00a0ORDER\u00a0BY\u00a0IndexName,\u00a0IndexUsage GO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
$\"1\"$ <\/strong><\/span><\/p>\n
<\/p>\n
\n
Disabling Index Using SQL Server Management Studio (SSMS)<\/strong> Expand Object Explorer for the AdventureWorks Tables until you get to Person.Address and then expand Indexes and right click IX_Address_StateProvinceID (Non-Unique, Non-Clustered) <\/strong>and select Disable<\/strong> from the drop down list. This will open up Disable Indexes<\/strong> dialog box, click OK to disable the index.<\/span><\/li>\n<\/ol>\n
$\"2\"$ <\/span><\/p>\n
You can also disable<\/strong> an index by unchecking Use Index <\/strong>option as shown in the below snippet which is available in the Options<\/strong> page for the Index Properties<\/strong> dialog box.<\/span><\/p>\n
$\"3\"$ <\/span><\/p>\n
Enabling Indexes <\/strong> <\/span><\/p>\n
There are different ways by which you can enable indexes. Let’s go through each option. <\/span><\/p>\n
1. Enable Index Using ALTER INDEX REBUILD Statement<\/strong><\/span><\/p>\n\n\n\n
USE\u00a0AdventureWorks GO ALTER\u00a0INDEX\u00a0IX_Address_StateProvinceID\u00a0ON\u00a0Person.Address\u00a0REBUILD GO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
2. Enable Index Using CREATE INDEX WITH DROP_EXISTING Statement<\/strong><\/span><\/p>\n\n\n\n
USE\u00a0AdventureWorks GO CREATE\u00a0NONCLUSTERED\u00a0INDEX\u00a0[IX_Address_StateProvinceID]\u00a0ON\u00a0[Person].[Address]\u00a0 ( [StateProvinceID]\u00a0ASC )WITH\u00a0(DROP_EXISTING\u00a0=\u00a0ON,\u00a0FILLFACTOR\u00a0=\u00a080)\u00a0ON\u00a0[PRIMARY] GO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
3. Enable Index Using SQL Server Management Studio (SSMS)<\/strong> Expand Object Explorer for the AdventureWorks Tables until you get to Person.Address and then expand Indexes and right click IX_Address_StateProvinceID (Non-Unique, Non-Clustered) <\/strong>and select Rebuild<\/strong> from the drop down list. This will open up Rebuild Indexes<\/strong> dialog box, click OK to enable the index.<\/span><\/p>\n
$\"4\"$ <\/strong><\/span><\/p>\n
You can also enable an index by selecting Use Index <\/strong>option as shown in the below snippet which is available in the Options<\/strong> page for the Index Properties<\/strong> dialog box.<\/span><\/p>\n
\u00a0 $\"5\"$ <\/span><\/p>\n
Thanks for reading this article,<\/strong><\/span><\/p>\n
Next steps :<\/strong><\/span><\/p>\n
\n
Add this article to your database toolkit<\/strong><\/span><\/li>\n
Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/li>\n
Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n
\u00a0<\/span><\/p>\n
\u00a0<\/span><\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Scenario proposal While looking through the new features in SQL Server 2005 and SQL Server 2008 we found a potentially interesting one called Disabling Indexes, which can be used to disable indexes on a table or a view. Can you give us a detailed explanation of how we go about using this new feature along […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[173,65,4,3],"tags":[18,17,19,16],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/86"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":7,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"predecessor-version":[{"id":330,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/86\/revisions\/330"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}