Share via

Index Build strategy in SQL Server - Introduction (II)

  • Article

- Building Partitioned Index vs. Building non-Partitioned Index:

The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on the data. All partitions of a single index or table must reside in the same database.

Building Aligned Partitioned Index Build:

Although partitioned indexes can be implemented independently from their base tables, it generally makes sense to design a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index by using the same partition scheme and partitioning column as the table. As a result, the index is partitioned in essentially the same manner as the table. This makes the index aligned with the table.

An index does not have to participate in the same named partition function to be aligned with its base table. However, the partition function of the index and the base table must be essentially the same, in that

1) the arguments of the partition functions have the same data type,

2) they define the same number of partitions, and

3) they define the same boundary values for partitions.

 

Also If you build a non-clustered index on a partitioned base (partitioned heap or clustered index) without specifying partitioning function, then the non-clustered index will be aligned partitioned as well (see example below).

Example:

Create Partition Function pf (int)

as range right for values (NULL, 1, 100)

                 

Create Partition Scheme ps

as Partition pf

TO ([PRIMARY], [FileGroup1], [FileGroup1], [FileGroup1])

                 

Create table t (c1 int, c2 int)

on ps(c1)

                 

Create Index idx_t on t(c1)

 Building non-Aligned Partitioned Index Build:

SQL Server does not align the index with the table if you specify a different partition scheme or a separate filegroup on which to put the index at creation time.

You can turn a non-partitioned table into partitioned by building a partitioned clustered index – it will be non-aligned index build as well (see example below).

Example:

Create Partition Function pf (int)

as range right for values (NULL, 1, 100)

                 

Create Partition Scheme ps

as Partition pf

TO ([PRIMARY], [FileGroup1], [FileGroup1], [FileGroup1])

                 

Create table t (c1 int, c2 int)

                 

Create clustered Index idx_t on t(c1)

on ps(c1)

Note: If you have a partitioned clustered index (as in the example above) and drop the clustered index, then the new heap will stay partitioned and will be located in the same partition scheme or filegroup as was defined for the clustered index unless you specify MOVE TO option when dropping the clustered index.

Example:

Drop Index idx_t on t

WITH(MOVE TO new_ps(c1))

In this example the base table is moved to a different partition scheme and the nonclustered indexes are not moved to coincide with the new location of the base table (heap). Therefore, even if the nonclustered indexes were previously aligned with the clustered index, they may no longer be aligned with the heap.

 

 Read in next post: Index Build Scenario 1: Offline, Serial, No Partitioning

 

Posted by: Lyudmila Fokina

Comments

  • Anonymous
    January 16, 2007
    There are two main categories of partitioned index build: Aligned (when base object and in-build index