Partition switching fails with error 4947, level 16, State 1
Loading large volumes of data into a staging table and performing index build operation on the staging table can be much quicker than trying to load data directly into your main table.
In order to partition switch the schema for you staging table should match exactly the schema of your main table.
If there are differences in the schemas you will probably come across this error.
Msg 4947, Level 16, State 1, Line 33
ALTER TABLE SWITCH statement failed. There is no identical index in source table
The error is caused by the partitioning column being automatically added onto the index on the main table when you create it on the partition schema and you not including the column on the index on the staging table.
So here's a quick example. The steps to setup the demo are:-
- Create a database
- Create the partition function and partition scheme (putting all filegroups to primary for ease of demo)
- Create two schemas (the staging schema for the new data and the Sales schema which we load into)
- Create the staging table on the primary filegroup
- Create the sales table on the partition scheme, partitioning by DatePlaced.
- Create a non-unique clustered index on each table with ID as the key
- Insert a row of data into the staging table
{code lang:sql lines:true hidden:false showtitle:false}use master;
if exists(Select * from sys.databases Where name = 'SQLEganPartitioning')
Begin
Alter Database [SQLEganPartitioning] Set Restricted_User With Rollback Immediate;
Drop Database [SQLEganPartitioning];
End
Create Database SQLEganPartitioning;
go
use [SQLEganPartitioning];
CREATE PARTITION FUNCTION pf_Orders(date)
AS
RANGE Right FOR VALUES ('2013-01-01', '2014-01-01', '2015-01-01');
GO
CREATE PARTITION SCHEME ps_Orders
AS
PARTITION pf_Orders All TO ([PRIMARY])
GO
create Schema [Sales] Authorization dbo;
go
Create schema [Staging] Authorization dbo;
go
Create table [Staging].[Orders]
( ID int not null,
DatePlaced date not null default getdate(),
Quantity int not null default 1
) On [Primary];
Create table [Sales].[Orders]
( ID int not null,
DatePlaced date not null default getdate(),
Quantity int not null default 1
) On [ps_Orders](DatePlaced);
Create Clustered Index [pk_SalesOrders] On [Staging].[Orders] (ID) With(IGNORE_DUP_KEY=Off)
Create Clustered Index [pk_SalesOrders] On [Sales].[Orders] (ID) With(IGNORE_DUP_KEY=Off)
Insert Into Staging.Orders (ID,[DatePlaced] ) Values (1, '2012-06-09');
{/code}
Before we can switch from the staging to the main table we have to add a check constraint onto the table. This check constraint should validate the data in the staging table will fit into the destination tables partition. So looking at partition 1 in the partition functions definition we can see the data should be between '2012-01-01' And '2012-12-31'.
{code lang:sql lines:true hidden:false showtitle:false}Alter Table [Staging].[Orders] Add Constraint [ck_OrderID] Check ([DatePlaced] between '2012-01-01' And '2012-12-31');{/code}
Now we can try and switch
{code lang:sql lines:true hidden:false showtitle:false}Alter Table [Staging].[Orders] Switch To [Sales].[Orders] Partition 1;{/code}
When we run the above we get the error 4947.
If we look at the indexes on the tables using the code
{code lang:sql lines:true hidden:false showtitle:false}Select *
From sys.indexes
Where object_id in (object_id('Sales.Orders'), object_id('Staging.Orders'))
Select schema_name(t.schema_id) + '.' + t.name, i.index_id, i.name,ic.index_column_id, c.name
from sys.index_columns as ic
Inner Join sys.columns as c on ic.column_id = c.column_id and ic.object_id = c.object_id
Inner Join sys.indexes as i on ic.object_id = i.object_id And ic.index_id = i.index_id
Inner Join sys.tables as t on i.object_id = t.object_id
Where i.object_id in (object_id('Sales.Orders'), object_id('Staging.Orders')){/code}
We can see both tables have the one index, but the columns are different. In the partitioned table the partitioning key has automatically been included. This is not visibile in the index properties in SSMS
So to fix the staging table we update the index to include the partitioning column and then we can switch in.
{code lang:sql lines:true hidden:false showtitle:false}CREATE CLUSTERED INDEX [pk_SalesOrders] ON [Staging].[Orders]
(
[ID] ASC,
[DatePlaced] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Go
Alter Table [Staging].[Orders] Switch To [Sales].[Orders] Partition 1
{/code}
{jcomments lock}
