While playing with Indexes I have found following interesting fact. I did some necessary tests to verify that it is true.
When a clustered index is disabled, all the nonclustered indexes on the same tables are auto disabled as well. User do not need to disable non-clustered index separately. However, when clustered index is enabled, it does not automatically enable nonclustered index. All the nonclustered indexes needs to be enabled individually. I wondered if there is any short cut to enable all the indexes together. Index rebuilding came to my mind instantly. I ran T-SQL command of rebuilding all the indexes and it enabled all the indexes on table.
This was very intriguing to me, as I never faced this kind of situation before. Everyday there is something new in SQL Server and that what makes life of DBA refreshing.
T-SQL to rebuild all the indexes on table.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
Rebuild All Indexes using SQL Server Management Studio.
Expand AdventureWorks Database, Expand Production.Product Database, Expand Index Group
Right click on Indexes and select Rebuild All
Reference : Pinal Dave (https://blog.sqlauthority.com)
