SQL Server 当表分区遇上唯一约束

2012-02-23 18:58 听风吹雨阅读(6084) 评论(12) 编辑收藏举报

一、前言

我已经在高兴对服务器创建了表分区并且获得良好性能和自动化管理分区切换的时候，某一天，开发人员告诉我，某表的两个字段的数据不唯一，需要为这两个字段创建唯一索引的时候，这一切就变得不完美了。

列的唯一，这个实际上是一个唯一索引。使用关键字unique建立。

二、背景

我有一个表TestUnique，这个表使用分区方案[Sch_TestUnique_Id]，它是以Id做为分区依据列的，这个Id也是一个聚集索引，表中其它索引是跟分区对齐的（创建其它非聚集索引的时候使用了分区方案或者不指定-默认就是分区方案），而且我我这个表很大，我需要定时的进行交换分区（SWITCH PARTITION、滑动窗口、切换分区），表分区的相关信息可参考：SQL Server 表分区实战系列（文章索引）

--创建测试表
CREATE TABLE [dbo].[TestUnique](
    [Id] [int] IDENTITY(600000000,1) NOT FOR 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 = 100) ON [Sch_TestUnique_Id]([Id])
) ON [Sch_TestUnique_Id]([Id])
GO

现在需要创建SiteId+Url做为一个唯一索引，本来以为这个唯一索引是可以进行分区对齐的，但是却在创建索引的时候遇到错误了。

三、分析

1. 对分区表创建索引时，SQL Server 将使用与该表相同的分区方案和分区依据列自动对索引进行分区。因此，索引的分区方式实质上与表的分区方式相同。这将使索引与表“对齐”。创建唯一索引有下面三种方式：

--方式1
CREATE UNIQUE NONCLUSTERED INDEX [IX_TestUnique_SiteIdUrl]
ON [TestUnique] (SiteId,Url)

消息1908，级别16，状态1，第1 行

列'Id' 是索引'IX_TestUnique_SiteIdUrl' 的分区依据列。唯一索引的分区依据列必须是索引键的子集。

--方式2
ALTER TABLE [dbo].[TestUnique] ADD  CONSTRAINT [IX_TestUnique_SiteIdUrl] UNIQUE NONCLUSTERED 
(
    [SiteId] ASC,
    [Url] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Sch_TestUnique_Id]([Id])

消息1908，级别16，状态1，第1 行

列'Id' 是索引'IX_TestUnique_SiteIdUrl' 的分区依据列。唯一索引的分区依据列必须是索引键的子集。

消息1750，级别16，状态0，第1 行

无法创建约束。请参阅前面的错误消息。

--方式3
CREATE UNIQUE NONCLUSTERED INDEX [IX_TestUnique_SiteIdUrl] ON [dbo].[TestUnique]
(
    [SiteId] ASC,
    [Url] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) ON [Sch_TestUnique_Id]([Id])
GO

--测试没有指定分区方案时是否默认使用分区方案
CREATE NONCLUSTERED INDEX [IX_TestUnique_SiteIdUrl] ON [dbo].[TestUnique]
(
    [SiteId] ASC,
    [Url] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
GO

2. 如果分区依据列不可能包含在唯一键中，则必须使用 DML 触发器，而不是强制实现唯一性。（在需要分区的表中，估计插入的数据量还是比较大的，在这个表使用触发器应该会有性能上的问题吧？）

--测试索引键的子集
CREATE UNIQUE NONCLUSTERED INDEX [IX_TestUnique_SiteIdUrl] ON [dbo].[TestUnique]
(
    [Id] ASC,
    [SiteId] ASC,
    [Url] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF) ON [Sch_TestUnique_Id]([Id])

上面这条SQL是能成功执行的，不过从业务逻辑上来看，加了唯一的Id值对唯一就没有任何意义了，但是这条SQL告诉我们：当你使用了SiteId做为分区依据列，那么你就可以创建以SiteId+Url的唯一索引。

3. IGNORE_DUP_KEY = ON与IGNORE_DUP_KEY = OFF的区别：

忽略重复键，在创建或修改唯一索引时，可以可设置一个忽略重复键的选项。如果此选项已设置为“是”（ON），当您试图通过添加影响多行的数据来创建重复键（使用 INSERT 语句）时，则不会添加包含重复项的行,不重复的记录会给插入到表中的；如果此选项设置为“否”（OFF），则整个插入操作将失败，并且将回滚所有数据。

4. 如果您预计将通过使用更多分区来扩展索引，或者将会涉及到频繁的分区切换，那么将索引与已分区表对齐将非常重要。有关详细信息，请参阅设计分区以管理数据子集。如果表与其索引对齐，SQL Server 则可以快速高效地切换分区，同时又能维护表及其索引的分区结构。

5. 在下列情况下，独立于基表而单独设计已分区索引（不对齐）很有用：