SQL Server : Drop vs Disable Index

If you are dropping the index; the database will not have any information for dropped index and once the index is dropped, the metadata, statistics and index pages are removed.

But if you disable the existing index of table; database have the metadata of index but it will deallocate index page and the space is freed in database.

 

Here is a script to drop the SQL index from table

 

DROP INDEX OrderNumber_IX ON dbo.Order;

 

To drop all indexes of table

 

DROP INDEX ALL ON dbo.Order;

 

Here is a script to disable an index on table

 

ALTER INDEX OrderNumber_IX ON dbo.Order

Disable;

 

To disable all indexes of table

 

ALTER INDEX ALL ON dbo.Order

Disable;

 

Note : There is one additional data impact of  disabling the clustered index of table;  table will not be available for any DML operation except rebuild and drop command; so before disable clustered index; be very caution about its impact.

 

If you want to enable the disabled index on table; simply run the rebuild command

 

To enable a disabled index of table

 

ALTER INDEX OrderNumber_IX ON dbo.Order

rebuild;

 

To enable the all disabled index of table

 

ALTER INDEX ALL ON dbo.Order

rebuild;

INCLUDE Columns in Non-Clustered Index

This article explains how to use INLCUDE clause while creating the non-clustered index and how it helps to improve the query performance.

Non-clustered index is used to improve the performance of queries which are not covered by clustered index and while creating of non-clustered index, we consider only those columns which are being part of WHERE/ORDER BY/GROUP BY/JOIN statement.

CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]

(

   [DepartmentCode] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [Primary]

In below SQL query, which fetches department information like name and location on based on input department code.

Select DepartmentName, DepartmentLocation From dbo.Department where DepartmentCode = @Code

In this query, the department record is quickly found with help of nonclustred index ‘DepartmentCode_IDX’ but to get department name and location information they have to fetch from table.

Built Non-Clustered Index-tree (B-Tree):

To improve the further performance of above select query, we can use INCLUSE clause in non-clustered index and specify the list of non-key columns which are part of Select statement like ‘ DepartmentName’, ‘DepartmentLocation’

CREATE NONCLUSTERED INDEX [DepartmentCode_IDX] ON [dbo].[Department]

(

   [DepartmentCode] ASC

) INCLUDE( DepartmentName, DepartmentLocation)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [Primary]

Built Non-Clustered Index-tree (B-Tree):

You can see the Include Columns are not part of all Index nodes expects leaf nodes so overall it is not increasing index tree’s size and benefit to have include columns, it adds the extra data at leaf node and it helps to get select columns value from index leaf nodes instead of fetching data from table so in this way it improves the performance of query.

Other related Post:

SQL: Clustered, Non-Clustered and Index on Multiple Columns