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)
10 Comments. Leave new
Gr8 piece! Keep it up.
Hi Pinal,
To enable all the indexes together, run the Alter Index All command. eg
“ALTER INDEX ALL ON Production.Product REBUILD”
The above command will rebuild and enable all indexes that has been disabled.
The leaf level of each non-clustered index holds the clustering key (if you have a clustered index). So if you disable the clustered index, the non-clustered indexes wouldn’t be pointing to data (as they would be if you had a heap instead), they’d be pointing to the disabled index. It wouldn’t make sense to leave any non-clustered indexes enabled if you disable a clustered index. The behaviour is completely sensible.
HTH,
Regards,
Greg
Hi Greg Low,
That is great information.
Regards,
Pinal Dave ( https://blog.sqlauthority.com/ )
I recently came across this need to disable all indexes for a massive data load. We do it nightly, so I’m interested in scripting this. When I try:
ALTER INDEX ALL ON Production.Product DISABLE
that works great, except that it also disables my PK index, which I don’t want to do, and which I don’t want to rebuild.
I’m wondering if there is an equivalent:
ALTER INDEX ALL_EXCEPT_PK ON Production.Product DISABLE
and, likewise:
ALTER INDEX ALL_EXCEPT_PK ON Production.Product REBUILD
Maybe I’ll have to start writing SP’s to do this from the meta data, but I’d hoped someone else might have thought of this already?
thanks!
Hi, could you Please tell me , actually mai i have done MCA in 2008 and presently i am working in sqlserver 2005 as a developer in a small comany and i want adwise from your side about my carear and i want to get a specifice certifecation in database so plz tell me it is posible to get certifection and my maine role in my company as trainee ..
all Indexes also can be rebuild in this way
USE AdventureWorks;
GO
DBCC DBREINDEX (‘HumanResources.Employee’, ”, 70);
GO
u r best sir
sir will u please help me to find the size of each nonclustered index in a table i got one query from u it showing the size of nonclustered index with the dataavailable in those columns but i need only size without data inthose indexes
Great Post ! Thanks