Special guidelines for SQL Server partitioned Indexes (3)

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.

SQL Server interprets "Special guidelines for Partitioned Indexes" (1)-Index alignment

SQL Server interprets "Special guidelines for Partitioned Indexes" (2)-Unique index partitions

Second, interpretation

"Partitioning Nonclustered Indexes"

"When partitioning a unique nonclustered index, the index key must contain the partitioning column. When partitioning a nonclustered index that is not unique, SQL Server adds the partitioning column as an indexed non-key (inclusive) column by default to ensure that the index is aligned with the base table. If the partitioning column is already present in the index, SQL Server will not add the partitioning column to the index. “

(i) When partitioning a unique nonclustered index, the index key must contain the partitioning column. "Partitioning a unique nonclustered index, first of all it has a unique constraint, you can refer to: SQL Server interprets" Special guidelines for Partitioned Indexes "(2)-Unique index partitions

(b) In fact, the above description, I am most concerned about whether the default is to create an inclusive column? Here we test:

1 Create a partitioned table named [Classifyresult], which takes [ClassId] as the partitioning column, [id]+ [ClassId] as the clustered index, and is the primary key (the unique constraint).

--Creating a test table create
table [dbo].[ Classifyresult] (
    [Id] [bigint] IDENTITY (1,1) not NULL,
    [ClassId] [int] NOT NULL CONSTRAINT [Df_classifyresult_ ClassID]  Default ((0)),
    [Archiveid] [int] not NULL CONSTRAINT [Df_classifyresult_archiveid]  default ((0)) ,
    [URL] [nvarchar] () not NULL CONSTRAINT [Df_classifyresult_url]  DEFAULT ("),
 CONSTRAINT [pk_ Classifyresult] PRIMARY KEY CLUSTERED 
(
    [Id] ASC,
    [CLASSID] ASC
) on [Sch_classifyresult_classid] ([ClassId]),
 CONSTRAINT [Ix_classifyresult_temp_classidurl] UNIQUE nonclustered 
([
    ClassId] ASC,
    [URL] ASC
) With (Ignore_dup_key = in) on [Sch_classifyresult_classid] ([ClassId])
) on [Sch_classifyresult_classid] ([ClassId ])

2 Create a non-unique nonclustered index for [classifyresult]: [Ix_classifyresult_arichiveid], which has only one key value: [Archiveid], and uses the same partitioning scheme as the table.

--Create a nonclustered index that is not unique create
nonclustered index [Ix_classifyresult_arichiveid] on [dbo].[ Classifyresult] 
(
    [Archiveid] ASC
) on [Sch_classifyresult_classid] ([ClassId])

3 when you partition a non-unique nonclustered index, SQL Server By default adds the partitioning column as the Non-key (inclusive) column of the index to ensure that the index is aligned with the base table. , the SQL statement that creates the index above is equivalent to the following SQL statement:

--Create a non-unique nonclustered index (include) create nonclustered the index
[Ix_classifyresult_arichiveid] on [dbo].[ Classifyresult] 
([
    Archiveid] ASC
) INCLUDE ([ClassId]) on [Sch_classifyresult_classid] ([ClassId])

4 The following is to verify that the above statement is correct, how should we verify it? First you need to understand what the include does: the charm of include in the SQL Server index (indexed with the included column), so we test the execution plan when we return different column values at select time. The execution plan is shown as Figure1:

--sql_1 query returns [Id] and [Archiveid]
SELECT top [Id],[archiveid] from
[Classifyresult]
where Archiveid = 107347

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

(Figure1: Implementation plan)