none
Partitioning related tables using Year RRS feed

  • Question

  • We have 4 levels of related tables(which are below) and want to partition by year. Each level of table has primary key. Should i use primary key of each table as a partition key(by getting the range for each year) or should I use Foreign key as a partition key to partition the tables. 

    Which approach is the best one to do it.

    -------- Parent-----------------------------------------------
    CREATE TABLE [dbo].[Parent](
    [FileId] [int] NOT NULL,
    [UploadDate] [datetime] NOT NULL,
    [Name] [varchar](50) NULL,
    PRIMARY KEY CLUSTERED 
    (
    [FileId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ---------Child-------------------------------------------------
    CREATE TABLE [dbo].[Child](
    [FileERId] [int] NOT NULL,
    [FileId] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    [FileERId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Child]  WITH CHECK ADD  CONSTRAINT [FK_Child_Parent] FOREIGN KEY([FileId])
    REFERENCES [dbo].[Parent] ([FileId])
    GO

    --ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK_Child_Parent]
    --GO

    -----------------GrandChild---------------------------------------

    CREATE TABLE [dbo].[GrandChild](
    [FileRowId] [int] NOT NULL,
    [FileERId] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    [FileRowId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[GrandChild]  WITH CHECK ADD  CONSTRAINT [FK_GrandChild_Child] FOREIGN KEY([FileERId])
    REFERENCES [dbo].[Child] ([FileERId])
    GO

    --ALTER TABLE [dbo].[GrandChild] CHECK CONSTRAINT [FK_GrandChild_Child]
    --GO

    -----------------GrandGrandChild---------------------------------------

    CREATE TABLE [dbo].[GrandGrandChild](
    [FileRowErrId] [int] NOT NULL,
    [FileRowId] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    [FileRowErrId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[GrandGrandChild]  WITH CHECK ADD  CONSTRAINT [FK_GrandGrandChild_GrandChild] FOREIGN KEY([FileRowId])
    REFERENCES [dbo].[GrandChild] ([FileRowId])
    GO

    --ALTER TABLE [dbo].[GrandGrandChild] CHECK CONSTRAINT [FK_GrandGrandChild_GrandChild]
    --GO

    your help would be appreciated.

    Saturday, February 15, 2020 12:18 AM

All replies

  • If it was up to me, I would firstly resist the temptation to partition as long as possible, because partitioning adds complexity and can lead to poor performance. (But it can also help to relieve some paint points.)

    When you partition, you should partition by columns you actually use in your queries. If you run a query over the table where the patitioning column is absent, SQL Server needs to look in every partition. I should immediately qualify this: you can have non-clustered indexes which you do not partition, in which case this is not an issue. However, if you have these so-called non-aligned indexes you lose most of the benefits with partitioning.

    For the parent table, how you do you look up things? By Name? By Date? By Id? By all of them depending on the user?

    And the child tables? If you always access them from the parent by joins, it makes sense to partition on the foreign key, assuming that you can be sure that keys are growing monotonically (which is not unreasonable). To partition by year, you would have to add that column to each table, which is not very appealing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, February 15, 2020 9:55 AM