none
foreign key partitioning

    Question

  • Hello, I would like create a partition function to partition volumeRow table using [VolmeID]. Table [volume] has been partitioned by [ProductionMonth].

    Is it possible to do partition by foreign key?

    Is there alternative way to do it?

    Thanks,

    Shenyi


    SELECT [RowID]
          ,[VolmeID]
       ...
      FROM [volumeRow]
    GO
    --117,984,134 records

    SELECT [VolmeID],[ProductionMonth]
        ...
    FROM [volume]


    Shenyi

    Friday, August 23, 2013 8:28 PM

Answers

  • Is it possible to do partition by foreign key?

    The partitioning column must be persisted in the table being partitioned.  So you can partition on the volumeRow table VolmelID column.  However, you can't partition the volumeRow table by ProductionMonth because that column is only present in the volume table.  I assume you often join the 2 related tables so query performance is a consideration if the tables are partitioned differently but much depends on you queries. You would need to partition both tables on VolmedID in order to partition both the same way, which may also affect performance depending on you queries.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, August 24, 2013 2:00 PM
  • One more question. If both tables are updated frequently. should I change the partition value range (1 10 1000, 10001 to 2000 etc) once per month or once per year? How do I now when it is good time to do it?

    I assume this range is based on VolmeID?  Is there a one-to-one relationship between ProductionMonth and VolmeID, and are the values incremental?  In that case, it probably makes sense to perform partition maintenance monthly to align with the business cycle and plan such that only empty partitions are SPLIT/MERGEd.  No harm in doing it annually either.  You'll jut a several empty partitions that will get used as the year progresses.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Michael_Bao Monday, August 26, 2013 12:25 PM
    Saturday, August 24, 2013 4:40 PM

All replies

  • You can follow the BOL example. Note that the partitioned column is part of the PRIMARY KEY.

    I don't think though it is a good idea.

    USE AdventureWorks2012;
    GO
    -- Adds four new filegroups to the AdventureWorks2012 database
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test1fg;
    GO
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test2fg;
    GO
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test3fg;
    GO
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test4fg; 
    
    -- Adds one file for each filegroup.
    ALTER DATABASE AdventureWorks2012 
    ADD FILE 
    (
        NAME = test1dat1,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf',
        SIZE = 5MB,
        MAXSIZE = 100MB,
        FILEGROWTH = 5MB
    )
    TO FILEGROUP test1fg;
    ALTER DATABASE AdventureWorks2012 
    ADD FILE 
    (
        NAME = test2dat2,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf',
        SIZE = 5MB,
        MAXSIZE = 100MB,
        FILEGROWTH = 5MB
    )
    TO FILEGROUP test2fg;
    GO
    ALTER DATABASE AdventureWorks2012 
    ADD FILE 
    (
        NAME = test3dat3,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf',
        SIZE = 5MB,
        MAXSIZE = 100MB,
        FILEGROWTH = 5MB
    )
    TO FILEGROUP test3fg;
    GO
    ALTER DATABASE AdventureWorks2012 
    ADD FILE 
    (
        NAME = test4dat4,
        FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf',
        SIZE = 5MB,
        MAXSIZE = 100MB,
        FILEGROWTH = 5MB
    )
    TO FILEGROUP test4fg;
    GO
    -- Creates a partition function called myRangePF1 that will partition a table into four partitions
    CREATE PARTITION FUNCTION myRangePF1 (int)
        AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
    GO
    -- Creates a partition scheme called myRangePS1 that applies myRangePF1 to the four filegroups created above
    CREATE PARTITION SCHEME myRangePS1
        AS PARTITION myRangePF1
        TO (test1fg, test2fg, test3fg, test4fg) ;
    GO
    CREATE TABLE Table1 (col1 int PRIMARY KEY, col2 varchar(32));
    GO
    
    -- Creates a partitioned table called PartitionTable that uses myRangePS1 to partition col1
    CREATE TABLE PartitionTable (ID INT, col1 int REFERENCES Table1, col2 char(10),
                                 PRIMARY KEY (ID, col1))
        ON myRangePS1 (col1) ;
    GO

    Link: http://technet.microsoft.com/en-us/library/ms188730.aspx


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012




    Saturday, August 24, 2013 1:21 PM
  • Is it possible to do partition by foreign key?

    The partitioning column must be persisted in the table being partitioned.  So you can partition on the volumeRow table VolmelID column.  However, you can't partition the volumeRow table by ProductionMonth because that column is only present in the volume table.  I assume you often join the 2 related tables so query performance is a consideration if the tables are partitioned differently but much depends on you queries. You would need to partition both tables on VolmedID in order to partition both the same way, which may also affect performance depending on you queries.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, August 24, 2013 2:00 PM
  • Hi Dan,

    Thank you for your reply. You answered my question. 

    One more question. If both tables are updated frequently. should I change the partition value range (1 10 1000, 10001 to 2000 etc) once per month or once per year? How do I now when it is good time to do it?

    Thanks, Shenyi 


    Shenyi

    Saturday, August 24, 2013 4:27 PM
  • One more question. If both tables are updated frequently. should I change the partition value range (1 10 1000, 10001 to 2000 etc) once per month or once per year? How do I now when it is good time to do it?

    I assume this range is based on VolmeID?  Is there a one-to-one relationship between ProductionMonth and VolmeID, and are the values incremental?  In that case, it probably makes sense to perform partition maintenance monthly to align with the business cycle and plan such that only empty partitions are SPLIT/MERGEd.  No harm in doing it annually either.  You'll jut a several empty partitions that will get used as the year progresses.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Michael_Bao Monday, August 26, 2013 12:25 PM
    Saturday, August 24, 2013 4:40 PM
  • ProductionMonth and VolmeID is one to many relationship. Yes, we have business cycle. I will check how to do "SPLIT/MERGEd". Thank you so much


    Shenyi

    Monday, August 26, 2013 12:25 PM