I have included a code sample for you to look at (and only now notice how old this question is. I hope it finds you well). There are two things that you need to keep in mind if you are switching related tables using partitioning.
1 - you need to have the same partition key (the date is fine) in both and it needs to be the same value.
2 - When switching, DISABLE any foreign key constraints on the child switch target, switch children first, then parents, then enable any foreign key constraints.
Please see the example below on one way of setting up the parents, children and the switch targets.
/** CREATE PARTITION FUNCTION / SCHEME
You didn't provide any details so I just stubbed in some, principal on the tables
is the same though, so it doesn't matter here...
**/
CREATE PARTITION FUNCTION PF_TestMe (DATETIME2(7)) AS RANGE LEFT FOR VALUES ('1/1/2020', '2/1/2020');
CREATE PARTITION SCHEME PS_TestMe AS PARTITION PF_TestMe ALL TO ([PRIMARY]);
/** dbo.PersonTest - This is the table that holds the actual person, the date and the image.
NOTE - I have a non-clustered primary key defined here, so that the foreign key references will work.
I have also created a clustered index as well, and that is defined as being on the partition.
**/
CREATE TABLE dbo.PersonTest
(
PersonTestID INT NOT NULL IDENTITY(1,1)
, PersonName VARCHAR(100) NULL
, Persondate DATETIME2(7) NOT NULL
, PersonImage VARBINARY(MAX) NULL
, CONSTRAINT PK_PersonTest PRIMARY KEY NONCLUSTERED (PersonTestID)
, INDEX IDXC_PersonTest CLUSTERED (PersonTestID, Persondate) ON PS_TestMe (Persondate)
)
CREATE TABLE dbo.PersonTest_SwitchTarget
(
PersonTestID INT NOT NULL IDENTITY(1,1)
, PersonName VARCHAR(100) NULL
, Persondate DATETIME2(7) NOT NULL
, PersonImage VARBINARY(MAX) NULL
, CONSTRAINT PK_PersonTest_SwitchTarget PRIMARY KEY NONCLUSTERED (PersonTestID)
, INDEX IDXC_PersonTest_SwitchTarget CLUSTERED (PersonTestID, Persondate) ON PS_TestMe (Persondate)
)
GO
/** dbo.fnGetPersonDate - This is a function to support the PersonContact table. It returns the
Persondate field from the PersonTest table.
**/
CREATE FUNCTION dbo.fnGetPersonDate
(@PersonTestID INT)
RETURNS DATETIME2(7)
AS
BEGIN
DECLARE @RValue DATETIME2(7)
SET @RValue = (SELECT TOP (1) Persondate FROM dbo.PersonTest WHERE PersonTestID = @PersonTestID)
RETURN @RValue
END
GO
/** dbo.PersonContact - Note that the Persondate field is included here as a computed column using the function
specified above. You can skip that if you can rely on your UI to always use the same date as in the PersonTest
table, but the computed column will work otherwise. It's also set to be persisted to avoid computation costs
**/
CREATE TABLE dbo.PersonContact
(
PersonContactID INT NOT NULL IDENTITY(1,1)
, PersonTestID INT NOT NULL CONSTRAINT FK_PersonContact_PersonTestID REFERENCES dbo.PersonTest (PersonTestID)
, Persondate AS dbo.fnGetPersonDate(PersonTestID) PERSISTED NOT NULL
, [Address] VARCHAR(200) NULL
, phone VARCHAR(20) NULL
, CONSTRAINT PK_PersonContact PRIMARY KEY NONCLUSTERED (PersonContactID)
, INDEX IDXC_PersonContact CLUSTERED (PersonContactID, Persondate) ON PS_TestMe (Persondate)
)
--NOTE the nonclustered index here has Persondate as a keycolumn. That is required for partitioned non-clustered indexes.
CREATE NONCLUSTERED INDEX IDX_PersonContact_PersonTestID
ON dbo.PersonContact (PersonTestID, Persondate)
WITH (FILLFACTOR=90, SORT_IN_TEMPDB=ON)
ON PS_TestMe (Persondate)
/** NOTE: the switchtarget version DOES NOT have the FK Check constraint enabled. If you need/want it to be enabled then
you will need to disable the check constraint and then re-enable it when switching partitions.
**/
CREATE TABLE dbo.PersonContact_SwitchTarget
(
PersonContactID INT NOT NULL IDENTITY(1,1)
, PersonTestID INT NOT NULL --CONSTRAINT FK_PersonContact_SwitchTarget_PersonTestID REFERENCES dbo.PersonTest (PersonTestID)
, Persondate AS dbo.fnGetPersonDate(PersonTestID) PERSISTED NOT NULL
, [Address] VARCHAR(200) NULL
, phone VARCHAR(20) NULL
, CONSTRAINT PK_PersonContact_SwitchTarget PRIMARY KEY NONCLUSTERED (PersonContactID)
, INDEX IDXC_PersonContact_SwitchTarget CLUSTERED (PersonContactID, Persondate) ON PS_TestMe (Persondate)
)
