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;
1 comment:
I think SQL and REST both are actually very useful tools for finding out solutions of some very useful and complex database operations. Although both can’t be used interchangeably.
SQL Server Load Rest Api
Post a Comment