Rebuilding a HEAP with nonaligned index

blue digital binary data on computer screen. Close-up shallow DOF

This post is designed to help you rebuild a HEAP with nonaligned indexes and without any issues. Starting in SQL Server 2008, you can rebuild an entire HEAP table using the command:

ALTER TABLE tbl_Demo REBUILD

Moreover, if the HEAP table is partitioned, you may specify a particular partition number to be rebuild as follows:

ALTER TABLE tbl_Demo REBUILD PARTITION = n

While n is the partition number being rebuild in order (read more about this command here).

While performing a daily regular index maintenance operation to rebuild all indexes and heap tables, I have found an interesting case while rebuilding a partitioned heap using the above command and specifying a particular partition number.

Additional non-clustered indexes to it, can be aligned or nonaligned with its corresponding table’s partition schema (clustered or heap), that is, stored on the same or different partition schema. This depends on performance and design considerations (read more about this topic here).

When there is a non-partitioned non-clustered index on that heap (i.e. this index is not aligned with the table partition schema), we get error message 7733 when we try to rebuild the HEAP table, as follows:

Msg 7733, Level 16, State 2, Line 70

‘ALTER TABLE’ statement failed. The table ‘tbl_Demo’ is partitioned while index ‘ix’ is not partitioned.

In order to demonstrate the problem, let’s create a partition function for datetime datatype and a partition schema and then create a table on it (based on datetime partition column) and then insert 3 records to the table:

CREATE PARTITION FUNCTION pf_Month_Demo (DATETIME)

AS RANGE RIGHT FOR VALUES
(
‘20050101’,’20050201′,’20050301′
)
GO

CREATE PARTITION scheme ps_Month_Demo AS PARTITION pf_Month_Demo ALL TO ([PRIMARY])
GO

CREATE TABLE tbl_Demo
(

rid INT IDENTITY(1,1),
dt DATETIME

)
ON ps_Month_Demo (dt)
GO

INSERT tbl_Demo (dt) values (‘2005-01-01′), (‘2005-02-01′), (‘2005-03-01′)
GO

Now, let’s create a non-clustered index on the heap table, i.e., directly on the PRIMARY filegroup and not on the partition scheme ps_Month_Demo:

CREATE NONCLUSTERED INDEX ix ON tbl_Demo (rid) ON [PRIMARY]

GO

The following query shows how the heap is portioned and the non-clustered is not (index_id = 0 is the HEAP, index_id = 2 is the non-clustered index):

SELECT * FROM sys.partitions

WHERE object_id = object_id(‘tbl_Demo’)

Rebuilding a HEAP

If we rebuild the non-clustered index while specifying the partition number (which is not mandatory, but possible), we get the error message 7733:

ALTER TABLE tbl_Demo REBUILD PARTITION = 1

 

Msg 7733, Level 16, State 2, Line 70

‘ALTER TABLE’ statement failed. The table ‘tbl_Demo’ is partitioned while index ‘ix’ is not partitioned.

Obviously, the message explains the correct situation, but this is undesired exception response. Having nonaligned indexes is absolutely allowed and specifying a partition number 1 is also not a critic violation of the product design. After all, every table or index, partitioned or not, has partition number 1. So, a failure during the rebuild of this case is not an option.

Moreover, this error appears only for heap, and not for a clustered index with nonaligned non-clustered index (see demo the attached script)

So, what can we do if we want to keep the table as a heap and rebuild a nonaligned non-clustered index?

2 solution are possible:

  1. We can align the non-clustered index and partition it the same as the heap. This option is not always what we want from design and performance perspective.
  2. We can specify:

ALTER TABLE tbl_Demo REBUILD PARTITION = ALL

Author
Aviel300
Aviel Iluz
SQL Server Expert

Aviel is an expert Microsoft SQL Server consultant. His vast SQL Server experience of almost 20 years includes SQL Server development, administration, and implementations. Aviel has developed several iterations of SQL Server training material, and has delivered training at technology colleges and multiple clients.

  • Categories