(iii) Detailed knowledge of index zoning

Align with table partitions

Before you know the index partition, you need to understand the concept of partition index and table alignment, if the partition and table use the same partition schema and the same partitioning column, then the partition is the same as the table partition, we call alignment, and vice versa is not aligned.

For partitioned table-aligned partitioned indexes, sort tables are generated at once in memory or tempdb at the time of sorting, and if not aligned with partitioned tables, the sort table is generated at the same time for each partition, so more memory space is required. Each sort table occupies a minimum of 40KB space, and the aligned index partition will only occupy 40kb, non-aligned if 100 partitions will occupy 40*100kb space. Therefore, alignment will directly affect the sorting performance.

At the same time, for an unaligned index, the partition switch cannot be completed and the partition's subset can be managed quickly.

New PARTIONTEST01 The following partition table for index testing:

--New test TableCREATE TABLEPARTIONTEST01 (Logidint Identity(1,1), OrderIDint, SalesDatedatetime) onPC_PARTIONTEST01 ([salesDate])--Specify partition Scheme--Query partition StatusSELECT object_name(p.object_id) as [object_name], Id.name asindex_name,ps.name partition_scheme,ds.name filegroup,pf.name partition_function,pf.type_desc+':'+ Case  whenPf.boundary_value_on_right=0  Then ' Left' Else 'rigth' EndFunction_type,p.partition_number,Isnull(PRV. VALUE,"') asboundy_value,p.rows fromsys.indexes IDJOINSys.partition_schemes PS onps.data_space_id=id.data_space_idJOINSys.destination_data_spaces DDS onps.data_space_id=dds.partition_scheme_idJOINSys.data_spaces DS onds.data_space_id=dds.data_space_idJOINSys.partitions P onP.object_id =Id.object_id  andp.index_id=id.index_id anddds.destination_id=P.partition_numberJOINSys.partition_functions PF onps.function_id=pf.function_id Left JOINSys.partition_range_values PRV onprv.function_id=pf.function_id andprv.boundary_id=P.partition_number-Pf.boundary_value_on_rightWHERE object_name(ID.object_id)= 'PartionTest01'
The query results are as follows:

Second, clustered index partition

Test One: If a partition column is not explicitly specified in the clustered key for a non-unique clustered index partition, the partition column is added by default in the clustered index key list

Test Two: for a unique clustered index partition, you must add a partition by column, as shown in the following error. New successful after adding the partitioning column, only the partitioning column is added to guarantee uniqueness on a single partition.

Three, nonclustered index partitions

Test One: non-unique nonclustered index, which automatically contains the partition by column.

Test Two: unique nonclustered, you still need to specify the partitioning column.

Test Three: specifies that the partitioning column is included to successfully

Conclusion

1. Creating a new unique index requires explicitly specifying the partitioning column to ensure that duplicate key values do not exist in the table.
2. New non-unique index if the partition by column is not explicitly specified, the partitioning column is automatically specified as the index key column
3. New contains non-unique nonclustered indexes and will no longer be added to the partition by column index key columns.

(iii) Detailed knowledge of index zoning