Enable Indexes and Constraints in SQL Server

We know that Indexes are the booster to improve the query performance in SQL Server in case of fetching the data from database only otherwise they will decrease the performance for other operations such as insert, update or delete. In our last article disable indexes and constraints in sql server, we have observed to handle this situations. 

First of all, we know that the index definition remains in metadata when we are disabling an index and index statistics are kept on non-cluster indexes and it remains in a disabled state until it is rebuilt or dropped.

In this article, we are going through the different scenarios to enable any disabled index in SQL Server. Before enabling an index in SQL Server, we have to know all the limitations and restriction of it. SQL server supports the followings –

1. If we enable or rebuild an index then all the associated constraints (disabled constraints) should be enabled manually except Primary Key and Unique Key constraints.  
2. SQL Server does not allow us to enable Foreign Key constraints without enable associated index due to having referenced Primary Key or Unique Key constraints.
3. In case of ONLINE option is set to ON then SQL Server does not allow rebuilding or enabling a disabled clustered index.

Before enabling indexes, we should have ALTER permission on the table or view. In case of using DBCC DBREINDEX, eser must either own the table or be a member of the sysadmin fixed server role or the db_ddladmin and db_ownerfixed database roles.

There are many ways to enable our disable indexes in SQL Server which are given below-

A.   Using SQL Server Management Studio – With the help of SSMS OR SSDT, we can easily enable a disabled index. We need to expend our index folders of the data table in the database as given below-

After expending the index folder, right click on the index and choose Rebuild. It will be launched Rebuild Indexes window to verify that the correct index is in the Indexes to rebuild grid and just click on OK button as given below-

B. If you want to enable all the indexes on a table then you just need to right click on the Indexes folder and choose rebuild as given below –

In the Rebuild Indexes dialog box, verify that the correct indexes are in the Indexes to rebuild grid and click OK. To remove an index from the Indexes to rebuild grid, select the index and then press the Delete key.

C.    Transact-SQL to enable a disabled index – It is very easy way to enable a disabled index where we need to use ALTER INDEX clause as given below-

USE DEMO;  GO  -- Enable IndCustItems index  -- on the dbo.TB_SalesOrders table  ALTER INDEX IndCustItems ON dbo.TB_SalesOrders REBUILD;

D.   There is another way to create index by checking if index exists then drop and create the same index again as given below-

USE DEMO;  GO  -- Re-creates IndCustItems index  -- on the dbo.TB_SalesOrders table  CREATE INDEX IndCustItems ON dbo.TB_SalesOrders (OrdNo) --- and then deletes the existing WITH (DROP_EXISTING = ON);  GO

E.   If you want to enable all the indexes on a single table then we can easily do this in T-SQL as given below-

USE DEMO;  GO  -- Enables all indexes on dbo.TB_SalesOrders table ALTER INDEX ALL ON dbo.TB_SalesOrders REBUILD;  GO