March 16, 2011 at 4:58 pm
A table has both the clustered index and non-clustered index.
what happens if we only defragment clustered Index but not non-clustered index?
Thanks
March 16, 2011 at 5:57 pm
depending on how fragmented your tables the pointers on the non clustered index pages that point to where the data is on the clustered index page will be invalid and SQL Server will not be able to optimize your queries.
Joe K
March 16, 2011 at 6:06 pm
You simply reorganize the clustered index, nothing happens to the nonclustered index. If you rebuild the clustered index, the nonclustered index is automatically rebuilt as well.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 16, 2011 at 6:07 pm
Joe Korn (3/16/2011)
depending on how fragmented your tables the pointers on the non clustered index pages that point to where the data is on the clustered index page will be invalid and SQL Server will not be able to optimize your queries.Joe K
I've never seen it lose the pointers. Remember, the pointer in a non-clustered index is the key in the clustered index. Fragmentation wouldn't affect this to my knowledge.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply