{"id":1815,"date":"2019-04-21T21:12:44","date_gmt":"2019-04-21T14:12:44","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1815"},"modified":"2020-07-11T03:57:26","modified_gmt":"2020-07-10T20:57:26","slug":"sql-server-disable-indexes","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-indexes\/sql-server-disable-indexes\/","title":{"rendered":"SQL Server Disable Indexes"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the ALTER TABLE<\/code> statement to disable the indexes of a table.<\/p>\n\n\n\n

SQL Server Disable Index statements #<\/a><\/h2>\n\n\n\n

To disable an index, you use the ALTER INDEX<\/code> statement as follows:<\/p>\n\n\n

ALTER<\/span> INDEX<\/span> index_name\nON<\/span> table_name\nDISABLE<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To disable all indexes of a table, you use the following form of the ALTER INDEX<\/code> statement:<\/p>\n\n\n
ALTER<\/span> INDEX<\/span> ALL<\/span> ON<\/span> table_name\nDISABLE<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you disable an index, the query optimizer will not consider that disabled index for creating query execution plans.<\/p>\n\n\n\n
When you disable an index on a table, SQL Server keeps the index definition in the metadata and the index statistics in nonclustered indexes. However, if you disable a nonclustered or clustered index on a view, SQL Server will physically delete all the index data.<\/p>\n\n\n\n
SQL Server disable index examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of disabling indexes to have a better understanding.<\/p>\n\n\n\n
A) Disabling an index example #<\/a><\/h3>\n\n\n\n
This example uses the ALTER INDEX<\/code> to disable the ix_cust_city<\/code> index on the sales.customers<\/code> table:<\/p>\n\n\n
ALTER<\/span> INDEX<\/span> ix_cust_city \nON<\/span> sales.customers \nDISABLE<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
As a result, the following query, which finds customers who locate in San Jose<\/code> , cannot leverage the disabled index:<\/p>\n\n\n
SELECT<\/span>    \n    first_name, \n    last_name, \n    city\nFROM<\/span>    \n    sales.customers\nWHERE<\/span> \n    city = 'San Jose'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the estimated query execution plan:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
B) Disabling all indexes of a table example #<\/a><\/h3>\n\n\n\n
This statement disables all indexes of the sales.customers<\/code> table:<\/p>\n\n\n
ALTER<\/span> INDEX<\/span> ALL<\/span> ON<\/span> sales.customers\nDISABLE<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Hence, you cannot access data in the table anymore.<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> sales.customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the error message:<\/p>\n\n\n
The query processor is unable to produce a plan because the index 'PK__customer__CD65CB855363011F'<\/span> on table or view 'customers'<\/span> is disabled.<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that you will learn how to enable the index in the next tutorial.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the ALTER INDEX<\/code> statement to disable indexes of a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
If you disable a clustered index of a table, you cannot access the table data using data manipulation language such as SELECT<\/a><\/code>, INSERT<\/a><\/code>, UPDATE<\/a><\/code>, and DELETE<\/a><\/code> until you rebuild or drop the index<\/a>.<\/p>\n\n\n\n