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;

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

This blog will demonstrates how to improve the SQL query performance by adding index on SQL tables and will be discuss the scenarios where we should go with clustered , non-clustered or single index on multiple columns.

Clustered Index should be created on Column which uniquely identify to each row of tables and it defines the physical order of table records and then generally table’s primary key should be have clustered index. Now when you are going to create other index (Non-clustered) which generally help SQL engine to create execution plan and quickly filter the records.

If you creates non-clustered index in very smartly way, then it will be more efficient and more reusable.

Here is an example - an order table which contain company huge order records

Table: Order

Table Columns:

OrderID	PK	Primary Key
OrderDate
OrderNumber
State
City
Zip

On Order table, mostly we make search by OrderNumber and also search by location like State, City and Zip.

In consideration of uniqueness of record, you create clustered index on OrderID which is primary key of table and one more index (non-clustered) you can create on Order Number.

If you are looking search by location query, we are considering three columns (State, City and Zip)

Then mostly time we are searching order by State, City and zip combination

And sometime by state and city and sometime by zip code only.

If you are going to have single index which covers all columns (state, city and zip) then it will helps in all scenarios except search by zip; so in this case we need to have one more index on zip.

So after considering all above scenarios, table should have below indexes

Index Name	Type of Index	Columns
Inx_OrderOrderID	Clustered Index	OrderID
Inx_OrderOrderNumber	Non-Clustred Index	OrderNumber
Inx_OrderStateCityZip	Non-Clustred Index	State, City, ZIP
Inx_OrderZip	Non-Clustred Index	ZIP

Thanks for visiting!!