SQL serve special guidelines for partitioned Indexes (1)-Index alignment

First, the preface

See a document about SQL Server table partitioning on MSDN: Special guidelines for partitioned indexes, it's harder to understand the meaning of a document if you don't have actual experience with the table partition. Here I will explain some of the concepts to facilitate the exchange of people.

Second, interpretation

The index is aligned to its base table and does not need to participate in the same named partition function as the base table. However, the partitioning function of an index and a base table must be identical in essence, that is:

1 The parameters of the partition function have the same data type;

2 The partitioning function defines the same number of partitions;

3 The partition function defines the same boundary value for the partition. ”

Here we test:

--1. Create filegroup ALTER DATABASE [TEST] add FILEGROUP [fg_testunique_id_01] ALTER DATABASE [test] add FILEGROUP [Fg_testuniq UE_ID_02] ALTER DATABASE [test] add FILEGROUP [fg_testunique_id_03]--2. Create a file ALTER DATABASE [test] Add File (NAM E = N ' fg_testunique_id_01_data ', FILENAME = N ' E:\DataBase\FG_TestUnique_Id_01_data.ndf ', SIZE = 1MB, filegrowth = 1MB) to F
    
Ilegroup [fg_testunique_id_01]; ALTER DATABASE [Test] ADD FILE (NAME = n ' fg_testunique_id_02_data ', FILENAME = N ' E:\DataBase\FG_TestUnique_Id_02_
    
Data.ndf ', SIZE = 1MB, filegrowth = 1MB) to FILEGROUP [fg_testunique_id_02]; ALTER DATABASE [Test] ADD FILE (NAME = n ' fg_testunique_id_03_data ', FILENAME = N ' E:\DataBase\FG_TestUnique_Id_03_
    
Data.ndf ', SIZE = 1MB, filegrowth = 1MB) to FILEGROUP [fg_testunique_id_03]; --3. Creating partition Functions Create PARTITION function fun_testunique_id (INT) as RANGE right for VALUES (10000000,20000000)--4. Create a partition scheme C reate PARTITION SCHEME sch_testunique_id as PARTITION fun_testunique_id to ([Fg_testuniqUE_ID_01],[FG_TESTUNIQUE_ID_02],[FG_TESTUNIQUE_ID_03]) 

The above SQL script creates partition functions: fun_testunique_id (INT) and partition scheme: [sch_testunique_id]. Here we create similar partitioning functions: Fun_testunique_siteid (INT) and partitioning scheme: [Sch_testunique_siteid]. These two functions fully conform to the 3 conditions mentioned above:

1 The parameters of the partitioning function have the same data type; (all int types)

2 partition function defines the same number of partitions (all 3 partitions)

3 The partition function defines the same boundary value for the partition. "(Boundary values are 10000000,20000000)

--1. Create filegroup ALTER DATABASE [TEST] add FILEGROUP [fg_testunique_siteid_01] ALTER DATABASE [test] add FILEGROUP [fg_test UNIQUE_SITEID_02] ALTER DATABASE [test] ADD FILEGROUP [fg_testunique_siteid_03]--2. Create A file ALTER DATABASE [test] A DD FILE (NAME = n ' fg_testunique_siteid_01_data ', FILENAME = N ' E:\DataBase\FG_TestUnique_SiteId_01_data.ndf ', SIZE = 1MB,
    
FileGrowth = 1MB) to FILEGROUP [fg_testunique_siteid_01]; ALTER DATABASE [Test] ADD FILE (NAME = n ' fg_testunique_siteid_02_data ', FILENAME = N ' E:\DataBase\FG_TestUnique_SiteId_02
    
_data.ndf ', SIZE = 1MB, filegrowth = 1MB) to FILEGROUP [fg_testunique_siteid_02]; ALTER DATABASE [Test] ADD FILE (NAME = n ' fg_testunique_siteid_03_data ', FILENAME = N ' E:\DataBase\FG_TestUnique_SiteId_03
    
_data.ndf ', SIZE = 1MB, filegrowth = 1MB) to FILEGROUP [fg_testunique_siteid_03]; --3. Creating partition Functions Create PARTITION function Fun_testunique_siteid (INT) as RANGE right for VALUES (10000000,20000000)--4. Create partitions Scenario CREATE PARTITION Scheme Sch_testuniqUe_siteid as PARTITION Fun_testunique_siteid to ([Fg_testunique_siteid_01],[fg_testunique_siteid_02],[fg_testunique _SITEID_03])

Next, create a table [Testunique] that is partitioned with this partition scheme; The clustered index of this table is created on the partition scheme: [sch_testunique_id]. Then create a unique index: [Ix_testunique_siteidurl] is created on the partition scheme [Sch_testunique_siteid]. So is this unique index aligned with the base table?

--5. Creating a partitioned Table create
table [dbo].[ Testunique] (
    [Id] [int] IDENTITY (600000000,1) not to REPLICATION not null,
    [SiteId] [int] NULL,
    [URL] [ NVARCHAR] (420) NULL,
    [Publishon] [datetime] NULL,
    [AddOn] [datetime] NULL,
 CONSTRAINT [pk_archive] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) with (pad_index  = on, Statistics_norecompute  = off, ignore_ Dup_key = off, allow_row_locks  = on, allow_page_locks  = on, FILLFACTOR = m on [sch_testunique_id] ([Id])