0

There is two main tables in database (SQL Server 2014)

table1:
----------------------------------------------
id         name         datetime        image

table2:
--------------------------------
id         Address      phone     person_id 

As you can guess there is an "one to many" relation between two tables, and the foreign key of [table2] is [person_id].

I'm trying to use partition table and sliding window features on table1. Everything perfectly done (I tested sliding window and partitioning on single table successfully) except one thing.

For sliding window , the oldest partition will be switch to an Auxiliary table (with same structure of table1) and the newest partition will split for new datas.

Partition function and scheme is created on datetime column.

As Microsoft mentioned,:

For switching task, the all indexes must be aligned.

In our situation the table1 has a primary key that is unique non-clustered index too (the database made it automatically) and the foreign key of table2 connected to that.

When stored procedure tried to switching out the oldest partition, it rises an error:

the table1 is partitioned but the [primary key index] column is not partitioned

So now the all indexes is not aligned because of this primary key !

I tried to include datetime to primary key, but database created an index for new primary key automatically on filegroup instead of partition scheme. Also tried other situations but no difference.

My final purpose is to switch last partition to auxiliary table and truncate that(about one billion records per partition). So the related records in table2 must delete too.

I'm looking for a solution to solve this situation and handle partitioning, switching and truncating on related tables with related columns (like this one).

Please don't attend so much to the structure or image storing stuffs! , I just tried to propose a concept from my circumstance.

1

You mentioned you added datetime to the PK, but it was not partitioned. You also need to specify the partition scheme when you recreate the PK index so that it is aligned.

Also, note there are restrictions on tables with foreign key relationships. See Transferring Data Efficiently by Using Partition Switching.

Unfortunately, SQL Server didn't ask anything about index creation on partition or filegroup. I performed that with visual features.

The GUI is nice for some simple quick tasks but not for partitioning a table with billions of rows. Use T-SQL so that you know exactly what is going on. Large tables are unforgiving.

| improve this answer | |
0

table2, probably, will have more rows than table1, so partition for table2 is even more required than for table1. on the other side table2 does not have partition-key (datetime), so this "construction" is looks useless.

You need same partition key for both tables.

You may add datetime column to table2 and add it to PK - as result you will have duplicate data (same datetime stored in in both tables), extra column, and somehow sync updates (if datetime updateable).

Another way (I prefer this and did in my case) - review partitioning logic and set partition key as id for table1 and person_id for table2. PK for table2 will be person_id, id. Any other index also should include id or person_id correspondently. If you do so, then you will need manually or dynamicaly set ranges in partition function which match to your dates:

id     date      range
...
10001  1/1/2010  10001-12345
...    
12346  1/1/2011  12346-45678
...
45679  1/1/2012  45679-...

Assume, that id and dates increments at the same time.

| improve this answer | |
  • No the size of table2 is more less than table1 and there will be exist many records in table1 without any related record in table2 (with image), so I didn't planning for partition table2. But as I realized the table2 must be partitioned to. Is it true? – Hamed Hamedi Apr 24 '15 at 10:08
  • Absolutely no, you do not have to. In this case to make partition by date on table1 date should be added to PK (otherwise it will be useless). For table2, if you strongly want FK, then you should add date column or just remove FK. Forign Key is good tool, but as any tool it should not be used everywhere. – parfilko Apr 24 '15 at 18:42
0

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)
    ) 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.