Misunderstanding of SQL Server: Misunderstanding of heap fragments on the last 30 days

Source: Internet
Author: User

Misunderstanding #29: You can build clustered indexes for the heap and DROP the indexes before performing fragment on the heap.
Nooooooooooooo !!!

Building a clustered index on the heap and dropping it again seems to me to be the second thing except shrinking the database.
If you see fragments on the stack through sys. dm_db_index_physical_stats (or earlier versions of dbcc showcontig), never create a clustered index and delete the clustered index to sort out the fragments. A good practice is not to delete clustered indexes after they are created. There is already a lot of information about how to choose an ideal clustered index key-narrow, rarely changed, unique, and auto-incrementing. Kimberly has an article to summarize this: Ever-increasing clustering key-the Clustered Index Debate ...... again! (Note that it is based on SQL Server 2005). I also have An example: An example of a nasty cluster key.
You can also clear heap fragments by using alter table... REBUILD in SQL Server 2008, but this practice is as evil as deleting a clustered index.
If you want to ask why I have a great idea about this? Well, let me explain: Every row in a non-Clustered Index will point to a link with the RID or Clustered Index key (for details, see: What Happens if I Drop a Clustered Index ?), This link will appear in either of the following two ways:

  • If the table where the non-clustered index is located is a heap, the link is an RID.
  • If the table where the non-clustered index is located is a clustered index, the link is the clustered index key.
    For more information, see the link at the bottom of the article.
    Therefore, it is easy to see that if you want to change the heap into a clustered index, all the RID of non-clustered indexes will be invalid, so all non-clustered indexes need to be rebuilt. Similarly, if the clustered index key is deleted, the clustered index keys stored on all non-clustered indexes will become invalid. Therefore, all non-clustered indexes need to be rebuilt.
    To put it simply, if you create and delete a clustered index, all non-clustered indexes will be rebuilt twice.
    If you use alter table... REBUILD of SQL Server 2008 to sort out heap fragments, you also need to re-create all non-clustered indexes because all the RID will change.
    So what if the clustered index is "rebuilt? It depends on the SQL Server version, whether you perform rebuild indexes, or change indexes. A common misunderstanding is that partitioning a table will change the clustered index key, but in fact it will not. For operations that will cause re-indexing of non-clustered Indexes, see the following list: indexes From Every Angle: What happens to non-clustered Indexes when the table structure is changed ?.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.