-3

Somehow, we have a SQL Server Table with both a HEAP and a CLUSTERED index on a table. Is there a way to fix this? e.g. delete the HEAP index by its object ID?

If we drop the Clustered Index, it will create a second HEAP which will be removed when the Clustered Index is recreated. This zombie HEAP index will remain.

enter image description here

  • You probably received a lot of down votes (even thought it is a simple question) because you failed to meet the community's quality standards, which are listed in the help section under How do I ask a good question. The down votes signal: "...while voting down a post signals the opposite: that the post contains wrong information, is poorly researched, or fails to communicate information." – hot2use Jul 19 '17 at 7:30
  • @hot2use I guess it's hard to convey I did extensive searching around on the issue (and looking at heaps/clustered keys) - obviously there was no info on it as it was a red herring and that's why I asked the question. – PeterX Jul 20 '17 at 6:27
7

Sorry, but the last entry in the table is for a different object/table. 452964740 != 562309263. So you don't have a heap and a clustered index in the same table.

Explanation

A heap is a table without a clustered index.

Reference: Heaps (Tables without Clustered Indexes)

So you can't have both a heap and a clustered index in a table.

sys.indexes

The definition of sys.indexes is as follows:

Column name         Data type           Description
------------------- ------------------- ---------------------------------------------------------------------------
object_id           int                 ID of the object to which this index belongs.  
name                sysname             Name of the index. name is unique only within the object.  NULL = Heap  
index_id            int                 ID of the index. index_id is unique only within the object. 
                                        0 = Heap; 
                                        1 = Clustered index; 
                                        > 1 = Nonclustered index   
type                tinyint             Type of index:
                                        0 = Heap
                                        1 = Clustered
                                        2 = Nonclustered
                                        3 = XML
                                        4 = Spatial
                                        5 = Clustered xVelocity memory optimized columnstore index (Reserved for future use.)  
                                        6 = Nonclustered columnstore index  
type_desc            nvarchar(60)       Description of index type:
                                        HEAP
                                        CLUSTERED
                                        NONCLUSTERED
                                        XML
                                        SPATIAL
                                        CLUSTERED COLUMNSTORE (Reserved for future use.)
                                        NONCLUSTERED COLUMNSTORE  
is_unique            bit                1 = Index is unique.
                                        0 = Index is not unique.  
data_space_id        int                ID of the data space for this index. Data space is either a filegroup or partition scheme.
                                        0 = object_id is a table-valued function.  
ignore_dup_key       bit                1 = IGNORE_DUP_KEY is ON.
                                        0 = IGNORE_DUP_KEY is OFF.  
is_primary_key        bit               1 = Index is part of a PRIMARY KEY constraint.   
is_unique_constraint  bit               1 = Index is part of a UNIQUE constraint.  
fill_factor           tinyint           > 0 = FILLFACTOR percentage used when the index was created or rebuilt.  
                                        0 = Default value
is_padded             bit               1 = PADINDEX is ON.
                                        0 = PADINDEX is OFF.
is_disabled           bit               1 = Index is disabled.
                                        0 = Index is not disabled.
is_hypothetical       bit               1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.  
                                        0 = Index is not hypothetical.
allow_row_locks       bit               1 = Index allows row locks.
                                        0 = Index does not allow row locks.
allow_page_locks      bit               1 = Index allows page locks.
                                        0 = Index does not allow page locks.
has_filter            bit               1 = Index has a filter and only contains rows that satisfy the filter definition.
                                        0 = Index does not have a filter.
filter_definition     nvarchar(max)     Expression for the subset of rows included in the filtered index.
                                        NULL for heap or non-filtered index.

You will get better results if you join sys.indexes with sys.objects like this:

select so.name, si.* from sys.indexes as si 
    join sys.objects as so 
        on si.object_id = so.object_id

You can build on this statement and join with sys.schemas to retrieve the (schema-)owner of the table and so forth.

Reference Material:

  • Thanks - the danger of looking at just a table name and not the full schema name. Interestingly our backend system was also fooled by this second table - deleting this "backup" table fixed the issue. – PeterX Jul 19 '17 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.