Special guidelines for SQL Server partitioned Indexes (2)-Unique index partitions

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

"Partitioning a unique index"

When you partition a unique index (clustered or nonclustered), you must select the partition by column from the partition in which the unique index key is used. This restriction causes SQL Server to investigate only a single partition to ensure that duplicate new key values do not exist in the table. If the partitioning column cannot be contained in a unique key by columns, you must use a DML trigger instead of enforcing uniqueness. ”

Do you have an understanding of this description? This passage you can read this way:

1 "to a unique index (clustered or nonclustered)": When you create a SQL Server table structure, you are accustomed to the ID value as the primary key, which means that the ID is created as a unique index, and is a clustered index, in fact, in SQL Server, the two can be separated;

2) "When you partition a unique index (clustered or nonclustered), you must select the partition by column from the partition by which the unique index key is used," the translation is very clumsy, and we look at its original English text: When partitioning a unique index (clustered or nonclustered), the partitioning column must is chosen from among those the unique index key. If you want to create a unique index used X ([SiteId], [url]) and want to partition the unique index, the partitioning column in the CREATE index on option must be a subset of the [SiteId] and [url] 2 fields in Uniqueindex ([SiteId], [url]) ([url] or [SiteId] or [Url]+[siteid]). Why is there such a requirement? See below for an explanation.

3 "This restriction will cause SQL Server to investigate only a single partition to ensure that there are no duplicate new key values in the table, which means that the Uniqueindex ([SiteId], [URL]) index is based on SITEID as the partitioning column, Then a SiteId value will only belong to a single partition, so that ([SiteId], [URL]) These two fields are unique in a single partition and are unique in the entire table; If the above unique index uniqueindex ([SiteId], [ URL] is based on an ID as the partitioning column, so how do you make sure that the data in each individual partition is unique across the table? There is no way to database. SQL Server is lazy, it wants to judge a partition's unique index in this is the only one can save a lot of, do not need to go to the whole table to judge, this is not a lot of convenience?

4) "If the partitioning column cannot be contained in a unique key by columns, you must use a DML trigger instead of enforcing uniqueness." "All of the above descriptions have a premise that you want the index to align with the base table, and if you don't have to align, you can use a DML trigger, or a separate filegroup to hold the unique index, and if so, you can't use partition switching."

Author: Listen to the wind blowing rain

Source: http://gaizai.cnblogs.com/

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