Sql-server – Partitioning a Table Referenced by a Foreign Key

foreign keypartitioningsql serversql-server-2012

I have one table:

CREATE TABLE [dbo].[entry]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EntryDate] [datetime] NOT NULL,
    [createddate] [datetime] NOT NULL,

    CONSTRAINT [PK_Entry_ID] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
    WITH
    (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80
    ) ON [PRIMARY]
) ON [PRIMARY];

…where the primary key is referenced by another table.
To partition this table I have already done following steps :

 CREATE PARTITION FUNCTION EntryFunc (DATE) 
   AS RANGE LEFT
   FOR VALUES ('2011-01-01')


  CREATE PARTITION SCHEME EntryScheme
    AS PARTITION EntryFunc
    TO ([FileGroup2], [PRIMARY])

The above 2 steps successfully completed,but when I am partitioning table I am not able to drop primary key clustered index because it is referenced by other table. My motive is to partition the table Entry according to created date.

Best Answer

From BOL (http://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx)

Source and target tables must have the same FOREIGN KEY constraints. If the target table has any FOREIGN KEY constraints, the source table must have the same foreign keys defined on the corresponding columns, and these foreign keys must reference the same primary key as those of the target table. The foreign keys of the source table cannot be marked is_not_trusted (viewable in the sys.foreign_keys catalog view), unless the corresponding foreign key of the target table is also marked is_not_trusted.

So if you want to partition a table referenced by foreign key, then both tables need to have same FK.

Also for fast partitioning, below rules apply:

you cannot use fk's

No primary key/foreign keys defined if source table has the primary key. There can be no active primary key/foreign key relationship between the source table and the target table in which the source table holds the primary key.

No primary key/foreign keys defined if target table has the foreign key. There can be no active primary key/foreign key relationship between the source table and the target table in which the target table holds the foreign key.

No foreign key from another table can reference the source table. The source table cannot be referenced by a foreign key in another table.

So, you have to drop the constraint, partition the table and the recreate the costraint. Better to do as a transaction, so on success it can be committed or on failure it can br rolled back.

Step by step tutorial can be found at : http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/ And

http://www.confio.com/logicalread/sql-server-primary-key-vs-clustered-index,-part-3/#.Ui1lNGIpDFo

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

MySQL Partitioning

Personally I'd use the date as your partition function, and partition by a hash of the year and month. Maybe splitting the data into 48 or more partitions. I've done this on some large volume databases and had good results.

ALTER TABLE `your_table` 
PARTITION BY HASH(YEAR(`date_field`)*12 + MONTH(`date_field`)) 
  PARTITIONS 48;

This should create a nice distributed set of data across 48 partitions (you may need to fiddle with the calculation on the date to get it quite right for your needs).

I build a model in Excel, with all the dates down one column, put the partition function on the second showing which partition that data would appear in. You can then chart the second column frequency to see how the data distribution is placed across the partitions - a really useful way of tinkering with your function before you alter your table!

Hope that helps...

Best Answer

Related Solutions

Sql-server – Indexing – Uniqueidentifier Foreign Key or Intermediary mapping table

MySQL Partitioning

Related Question