{"id":1821,"date":"2019-04-21T21:40:03","date_gmt":"2019-04-21T14:40:03","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1821"},"modified":"2020-04-11T20:12:22","modified_gmt":"2020-04-11T13:12:22","slug":"sql-server-enable-indexes","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-indexes\/sql-server-enable-indexes\/","title":{"rendered":"SQL Server Enable Indexes"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use various statements to enable one or all disabled indexes on a table.<\/p>\n\n\n\n

Sometimes, you need to disable an index<\/a> before doing a large update<\/a> on a table. By disabling the index, you can speed up the update process by avoiding the index writing overhead.<\/p>\n\n\n\n

After completing the update to the table, you need to enable the index. Since the index was disabled, you can rebuild the index but cannot just simply enable it. Because after the update operation, the index needs to be rebuilt to reflect the new data in the table.<\/p>\n\n\n\n

In SQL Server, you can rebuild an index by using the ALTER INDEX<\/code> statement or DBCC DBREINDEX<\/code> command.<\/p>\n\n\n\n

Enable index using ALTER INDEX and CREATE INDEX statements #<\/a><\/h2>\n\n\n\n

This statement uses the ALTER INDEX<\/code> statement to “enable” or rebuild an index on a table:<\/p>\n\n\n

ALTER<\/span> INDEX<\/span> index_name \nON<\/span> table_name  \nREBUILD<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement uses the CREATE INDEX<\/code> statement to enable the disabled index and recreate it:<\/p>\n\n\n
CREATE<\/span> INDEX<\/span> index_name \nON<\/span> table_name(column_list)\nWITH<\/span>(DROP_EXISTING=ON<\/span>)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses the ALTER INDEX<\/code> statement to enable all disabled indexes on a table:<\/p>\n\n\n
ALTER<\/span> INDEX<\/span> ALL<\/span> ON<\/span> table_name\nREBUILD<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Enable indexes using DBCC DBREINDEX statement #<\/a><\/h2>\n\n\n\n
This statement uses the DBCC DBREINDEX<\/code> to enable an index on a table:<\/p>\n\n\n
DBCC DBREINDEX (table_name, index_name);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement uses the DBCC DBREINDEX<\/code> to enable all indexes on a table:<\/p>\n\n\n
DBCC DBREINDEX (table_name, \" \");  \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Enable indexes example #<\/a><\/h2>\n\n\n\n
The following example uses the ALTER INDEX<\/code> statement to enable all indexes on the sales.customers<\/code> table from the sample database<\/a>:<\/p>\n\n\n
ALTER<\/span> INDEX<\/span> ALL<\/span> ON<\/span> sales.customers\nREBUILD<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned various statements including ALTER INDEX<\/code>, CREATE INDEX<\/code>, and DBCC DBREINDEX<\/code> to enable one or all indexes on 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