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
- 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');
-
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'.
Now we can try and switch
When we run the above we get the error 4947.
If we look at the indexes on the tables using the code
- 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'))
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.
- 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
-
Comments
Thank you so much for this - I finally figured out why my indexes where not identical.
Your rock.
Regards