sql-server - "No identical index"切换数据分区时出错

Question

我正在尝试设置一个数据仓库应用程序，以便将我的公司与分区数据导入表一起使用。我正在尝试删除旧数据并为新数据腾出空间。那就是我收到此错误消息的地方:

Msg 4947, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'AssetServer.dbo.IISLog061122' for the index 'IDX_IISLogPartitioned_IP' in target table 'AssetServer.dbo.IISLogPartitioned' . 

这是它说需要复制的索引的定义

/****** 对象:索引 [IDX_IISLogPartitioned_IP] 脚本日期:07/01/2009 10:44:45 ******/

CREATE NONCLUSTERED INDEX [IDX_IISLogPartitioned_IP] ON [dbo].[IISLogPartitioned] ( 
[c-ip] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 

在下面您将看到我在存储过程中创建了一个相同的索引。我已验证它已成功创建，但 alter table 语句失败并显示上述消息。存储过程的主循环归结为:

ALTER PARTITION FUNCTION fnIISLogRequestTime() SPLIT RANGE ('20090612 01:59:59:000');

CREATE TABLE [dbo].[IISLog061201]     
    ([RequestTime] [datetime] NULL,     
     [weekday] [int] NOT NULL,     
     [cs-method] [varchar](50) NOT NULL,     
     [cs-uri-stem] [varchar](255) NOT NULL,     
     [cs-uri-query] [varchar](2048) NULL,     
     [c-ip] [varchar](50) NOT NULL,     
     [cs(Referer)] [varchar](2048) NULL,     
     [cs-host] [varchar](255) NULL,     
     [sc-status] [int] NOT NULL,     
     [sc-substatus] [int] NULL,     
     [sc-bytes] [int] NULL,     
     [cs-bytes] [int] NULL,     
     [time-taken] [int] NULL,     
     [insertiontime] [datetime] NOT NULL,     
     [TimeSinceLast] [int] NULL,     
     [VIP] [varchar](50) NULL) ON [PRIMARY];      



CREATE NONCLUSTERED INDEX [IDX_IISLogPartitioned_IP] ON [dbo].[IISLog061201](
    [c-ip] ASC
) WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON);


ALTER TABLE [dbo].[IISLog061201] SWITCH TO IISLogPartitioned PARTITION 7

我怎样才能摆脱这个错误信息？

Answer 1

你得到错误的原因是因为这两个索引实际上不相同。分区表上的索引在其定义中包含分区列，即使您的代码没有明确说明它。 (这是幕后的事情。)

如果你想在有分区表时进行分区切换，你的索引需要显式引用分区列。更改您提供的索引以包含 RequestTime(假设这是分区列)。您可以将其作为索引列或简单地作为包含列。下面我将包括在内。

CREATE NONCLUSTERED INDEX [IDX_IISLogPartitioned_IP] ON [dbo].[IISLog061201](
[c-ip] ASC) INCLUDE ( RequestTime ) WITH (
PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON);

现在它应该可以工作了。