Table Partition Switch out problem

Please help! I am having error switching out from the partition i set up. I have the scripts and error information below :

I created separate file groups for each range

--Create Partition Function

```
USE [ApplicationLogs]
GO
CREATE PARTITION FUNCTION FN_SchedulerLog AS RANGE LEFT FOR VALUES (N'2016-06-30T23:59:59.998', N'2016-07-31T23:59:59.998', N'2016-08-31T23:59:59.998', N'2016-09-30T23:59:59.998', N'2016-10-31T23:59:59.998', N'2016-11-30T23:59:59.998', N'2016-12-31T23:59:59.998')

--Create Parttion SCHEME
CREATE PARTITION SCHEME [sch_SchedulerLog] AS PARTITION [FN_SchedulerLog] TO ([FG_SchedulerLog_06_16], [FG_SchedulerLog_07_16], [FG_SchedulerLog_08_16], [FG_SchedulerLog_09_16], [FG_SchedulerLog_10_16], [FG_SchedulerLog_11_16], [FG_SchedulerLog_12_16], [PRIMARY])

DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog]
(
[ID] 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) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog]
(
[Date]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON sch_SchedulerLog

DROP INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

--- switching out data from any FGs to a switchout table for archiving purposes
--****
--Create a switch out table:
USE [ApplicationLogs]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].staging_SchedulerLog_20160923-120700 COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HostName] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HostIP] varchar COLLA

Because switching partition change address of all pages to target table, it also moves indexes (created on the source table) to target table. If available index on the table is not partitioned index will not move to target.

When we SWITCH partition, make sure both (source and target) tables has same schema (structure), indexes (should be partitioned) and CHECK constraint.

According to the Error


Error: ALTER TABLE SWITCH' statement failed. The table
'ApplicationLogs.dbo.SchedulerLog' is partitioned while index
'staging_SchedulerLog_20160923-120700_pkSchedulerLogId' is not
partitioned.

The index [pkSchedulerLogId] is not partitioned.

After you partition the index

DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] 
(
    [ID] ASC
    ,[Date]
)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)
ON [sch_SchedulerLog]([Date])

AND

either add check constraint to both tables

ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1] CHECK  ([Date]<=N'2016-06-30T23:59:59')
ALTER TABLE [dbo].[SchedulerLog]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_2] CHECK  ([Date]<=N'2016-06-30T23:59:59')

OR

remove constraint from staging table.

ALTER TABLE [staging_SchedulerLog_20160923-120700] DROP CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1];

Now you will be able to SWITCH partition

ALTER TABLE [dbo].[SchedulerLog] SWITCH PARTITION 1 TO [dbo].[staging_SchedulerLog_20160923-120700];

Thanks

StackExchange Database Administrators Q#150550, answer score: 5