Help with switching partition - getting error x contains primary key for constraint...

Question

Help with switching partition - getting error x contains primary key for constraint...

sherrerk

Ten Centuries

Points: 1165
More actions
January 28, 2014 at 8:30 am

#281650

We have a situation that data in a partition occasionally gets put into a wrong partition because the partition function was not changed properly. I am trying to develop a script that pulls the data out of the partition, splits the data, change the partition function, then switch back in the split data. I am trying to do this in a way as to only effect the data within the partitions without locking other partitions. Any words or advice would be appreciated.
Executing... alter table RecurringBillingRuns switch partition 2 to RecurringBillingRuns1 partition 2, give the following error.
Msg 4967, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'Test.dbo.RecurringBillingRuns' contains primary key for constraint 'FK__BillingRuns__RecurringBillingRuns__PartitionKey__RecurringBillingRun_ID'.
Here is the structure of both tables involved:
CREATE TABLE [dbo].[RecurringBillingRuns](
[Row] [bigint] IDENTITY(1,1) NOT NULL,
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[PartitionKey] [bigint] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cycle_ID] [uniqueidentifier] NOT NULL,
[RunDate] [int] NOT NULL,
[PerformInitialBilling] [bit] NOT NULL,
[InitialBillingRangeBegin] [int] NULL,
[InitialBillingRangeEnd] [int] NULL,
[GenerateFirstReminders] [bit] NOT NULL,
[FirstReminderRangeBegin] [int] NULL,
[FirstReminderRangeEnd] [int] NULL,
[GenerateSecondReminders] [bit] NOT NULL,
[SecondReminderRangeBegin] [int] NULL,
[SecondReminderRangeEnd] [int] NULL,
[GenerateThirdReminders] [bit] NOT NULL,
[ThirdReminderRangeBegin] [int] NULL,
[ThirdReminderRangeEnd] [int] NULL,
[PerformDrops] [bit] NOT NULL,
[Mode] [tinyint] NOT NULL,
[DropDate] [int] NULL,
[TerminationReason_ID] [uniqueidentifier] NULL,
[NewStatusForDroppedMembers_ID] [uniqueidentifier] NULL,
[Recurrence] [xml] NOT NULL,
[ExecutionSchedule] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExecutionEnds] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDate] [datetime] NOT NULL,
[NextScheduledRun] [datetime] NULL,
[DateLastRun] [datetime] NULL,
[Suspended] [bit] NOT NULL,
[NumberOfTimesRun] [int] NOT NULL,
[ErrorMessage] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmailAddresses] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Keywords] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SecurityLock] [xml] NULL,
[LockedForDeletion] [bit] NOT NULL,
[IsConfiguration] [bit] NOT NULL,
[IsSealed] [bit] NOT NULL,
[LastModifiedBy_ID] [uniqueidentifier] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[CreatedBy_ID] [uniqueidentifier] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[SystemTimestamp] [timestamp] NOT NULL,
CONSTRAINT [PK__RecurringBillingRuns__PartitionKey__ID] PRIMARY KEY NONCLUSTERED
(
[PartitionKey] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE UNIQUE CLUSTERED INDEX [UC__RecurringBillingRuns__PartitionKey__Row] ON [dbo].[RecurringBillingRuns]
(
[PartitionKey] ASC,
[Row] 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 [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__RecurringBillingRuns__PartitionKey__Cycle_ID__ForeignKey] ON [dbo].[RecurringBillingRuns]
(
[PartitionKey] ASC,
[Cycle_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__RecurringBillingRuns__PartitionKey__NewStatusForDroppedMembers_ID__ForeignKey] ON [dbo].[RecurringBillingRuns]
(
[PartitionKey] ASC,
[NewStatusForDroppedMembers_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__RecurringBillingRuns__PartitionKey__TerminationReason_ID__ForeignKey] ON [dbo].[RecurringBillingRuns]
(
[PartitionKey] ASC,
[TerminationReason_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
ALTER TABLE [dbo].[RecurringBillingRuns] ADD CONSTRAINT [DF_RecurringBillingRuns_Mode] DEFAULT ((0)) FOR [Mode]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [FK__RecurringBillingRuns__BillingCycles__PartitionKey__Cycle_ID] FOREIGN KEY([PartitionKey], [Cycle_ID])
REFERENCES [dbo].[BillingCycles] ([PartitionKey], [ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [FK__RecurringBillingRuns__BillingCycles__PartitionKey__Cycle_ID]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [FK__RecurringBillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID] FOREIGN KEY([PartitionKey], [TerminationReason_ID])
REFERENCES [dbo].[ConfigurableTypes] ([PartitionKey], [ID])
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [FK__RecurringBillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [FK__RecurringBillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID] FOREIGN KEY([PartitionKey], [NewStatusForDroppedMembers_ID])
REFERENCES [dbo].[MembershipStatuses] ([PartitionKey], [ID])
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [FK__RecurringBillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_Drops] CHECK (([PerformDrops]=(0) OR [DropDate] IS NOT NULL))
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_Drops]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_FirstReminder] CHECK (([GenerateFirstReminders]=(0) OR [FirstReminderRangeBegin] IS NOT NULL AND [FirstReminderRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_FirstReminder]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_InitialBilling] CHECK (([PerformInitialBilling]=(0) OR [InitialBillingRangeBegin] IS NOT NULL AND [InitialBillingRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_InitialBilling]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_SecondReminder] CHECK (([GenerateSecondReminders]=(0) OR [SecondReminderRangeBegin] IS NOT NULL AND [SecondReminderRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_SecondReminder]
GO
ALTER TABLE [dbo].[RecurringBillingRuns] WITH CHECK ADD CONSTRAINT [CK_RecurringBillingRuns_ThirdReminder] CHECK (([GenerateThirdReminders]=(0) OR [ThirdReminderRangeBegin] IS NOT NULL AND [ThirdReminderRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[RecurringBillingRuns] CHECK CONSTRAINT [CK_RecurringBillingRuns_ThirdReminder]
GO
CREATE TABLE [dbo].[BillingRuns](
[Row] [bigint] IDENTITY(1,1) NOT NULL,
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[PartitionKey] [bigint] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cycle_ID] [uniqueidentifier] NOT NULL,
[Batch_ID] [uniqueidentifier] NULL,
[RunDate] [datetime] NOT NULL,
[Status] [tinyint] NOT NULL,
[RecurringBillingRun_ID] [uniqueidentifier] NULL,
[PerformInitialBilling] [bit] NOT NULL,
[InitialBillingRangeBegin] [date] NULL,
[InitialBillingRangeEnd] [date] NULL,
[GenerateFirstReminders] [bit] NOT NULL,
[FirstReminderRangeBegin] [date] NULL,
[FirstReminderRangeEnd] [date] NULL,
[GenerateSecondReminders] [bit] NOT NULL,
[SecondReminderRangeBegin] [date] NULL,
[SecondReminderRangeEnd] [date] NULL,
[GenerateThirdReminders] [bit] NOT NULL,
[ThirdReminderRangeBegin] [date] NULL,
[ThirdReminderRangeEnd] [date] NULL,
[PerformDrops] [bit] NOT NULL,
[ErrorMessage] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Mode] [tinyint] NOT NULL,
[DropDate] [date] NULL,
[TerminationReason_ID] [uniqueidentifier] NULL,
[NewStatusForDroppedMembers_ID] [uniqueidentifier] NULL,
[DateScheduled] [datetime] NULL,
[DateStarted] [datetime] NULL,
[DateCompleted] [datetime] NULL,
[Keywords] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SecurityLock] [xml] NULL,
[LockedForDeletion] [bit] NOT NULL,
[IsConfiguration] [bit] NOT NULL,
[IsSealed] [bit] NOT NULL,
[LastModifiedBy_ID] [uniqueidentifier] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[CreatedBy_ID] [uniqueidentifier] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[SystemTimestamp] [timestamp] NOT NULL,
CONSTRAINT [PK__BillingRuns__PartitionKey__ID] PRIMARY KEY NONCLUSTERED
(
[PartitionKey] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE UNIQUE CLUSTERED INDEX [UC__BillingRuns__PartitionKey__Row] ON [dbo].[BillingRuns]
(
[PartitionKey] ASC,
[Row] 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 [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__Batch_ID__ForeignKey] ON [dbo].[BillingRuns]
(
[PartitionKey] ASC,
[Batch_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__Cycle_ID__ForeignKey] ON [dbo].[BillingRuns]
(
[PartitionKey] ASC,
[Cycle_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__NewStatusForDroppedMembers_ID__ForeignKey] ON [dbo].[BillingRuns]
(
[PartitionKey] ASC,
[NewStatusForDroppedMembers_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__RecurringBillingRun_ID__ForeignKey] ON [dbo].[BillingRuns]
(
[PartitionKey] ASC,
[RecurringBillingRun_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
CREATE NONCLUSTERED INDEX [IX__BillingRuns__PartitionKey__TerminationReason_ID__ForeignKey] ON [dbo].[BillingRuns]
(
[PartitionKey] ASC,
[TerminationReason_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [msMultiTenantSchema]([PartitionKey])
GO
ALTER TABLE [dbo].[BillingRuns] ADD CONSTRAINT [DF_BillingRuns_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[BillingRuns] ADD CONSTRAINT [DF_BillingRuns_Mode] DEFAULT ((0)) FOR [Mode]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__Batches__PartitionKey__Batch_ID] FOREIGN KEY([PartitionKey], [Batch_ID])
REFERENCES [dbo].[Batches] ([PartitionKey], [ID])
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__Batches__PartitionKey__Batch_ID]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__BillingCycles__PartitionKey__Cycle_ID] FOREIGN KEY([PartitionKey], [Cycle_ID])
REFERENCES [dbo].[BillingCycles] ([PartitionKey], [ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__BillingCycles__PartitionKey__Cycle_ID]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID] FOREIGN KEY([PartitionKey], [TerminationReason_ID])
REFERENCES [dbo].[ConfigurableTypes] ([PartitionKey], [ID])
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__ConfigurableTypes__PartitionKey__TerminationReason_ID]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID] FOREIGN KEY([PartitionKey], [NewStatusForDroppedMembers_ID])
REFERENCES [dbo].[MembershipStatuses] ([PartitionKey], [ID])
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__MembershipStatuses__PartitionKey__NewStatusForDroppedMembers_ID]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [FK__BillingRuns__RecurringBillingRuns__PartitionKey__RecurringBillingRun_ID] FOREIGN KEY([PartitionKey], [RecurringBillingRun_ID])
REFERENCES [dbo].[RecurringBillingRuns] ([PartitionKey], [ID])
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [FK__BillingRuns__RecurringBillingRuns__PartitionKey__RecurringBillingRun_ID]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_Drops] CHECK (([PerformDrops]=(0) OR [DropDate] IS NOT NULL))
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_Drops]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_FirstReminder] CHECK (([GenerateFirstReminders]=(0) OR [FirstReminderRangeBegin] IS NOT NULL AND [FirstReminderRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_FirstReminder]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_InitialBilling] CHECK (([PerformInitialBilling]=(0) OR [InitialBillingRangeBegin] IS NOT NULL AND [InitialBillingRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_InitialBilling]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_SecondReminder] CHECK (([GenerateSecondReminders]=(0) OR [SecondReminderRangeBegin] IS NOT NULL AND [SecondReminderRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_SecondReminder]
GO
ALTER TABLE [dbo].[BillingRuns] WITH CHECK ADD CONSTRAINT [CK_BillingRuns_ThirdReminder] CHECK (([GenerateThirdReminders]=(0) OR [ThirdReminderRangeBegin] IS NOT NULL AND [ThirdReminderRangeEnd] IS NOT NULL))
GO
ALTER TABLE [dbo].[BillingRuns] CHECK CONSTRAINT [CK_BillingRuns_ThirdReminder]
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

sherrerk (1/28/2014)
We have a situation that data in a partition occasionally gets put into a wrong partition because the partition function was not changed properly.

My suggestion would be to fix the root problem that you've identified above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sherrerk Ten Centuries Points: 1165 More actions · Answer 2

I figured the problem out. I had to nocheck the fk constraints referencing the table that was being switched.

Thanks!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

sherrerk (1/28/2014)
I figured the problem out. I had to nocheck the fk constraints referencing the table that was being switched.
Thanks!

That's cool. Still, the best thing to do would be to prevent the occurance of the problem to begin with,.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sherrerk Ten Centuries Points: 1165 More actions · Answer 4

Honestly I don't understand what you mean be fixing the root... these are fk constraints that need to be there; so enlighten me!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

This is the "root" problem that needs to be prevented from happening...

We have a situation that data in a partition occasionally gets put into a wrong partition because the partition function was not changed properly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sherrerk Ten Centuries Points: 1165 More actions · Answer 6

Ah yes, you are completely right about that, and that I have fixed. Unfortunately, what is done is done, and I don't want to shut down the entire database to split the partitions.