x

Data Partitioning

how to Partition Master Detail tables ?

my database have a Detail table and two master table.

How should the partition key be determined?

more ▼

asked Feb 13 at 05:28 AM in Default

avatar image

Mehdi593
11 1 5

Kendra Little has a good video about partitioning. https://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/

Usually the key is some type of datetime value and/or numeric value.

Feb 13 at 07:53 PM JohnM

thank ! but : This site can’t be reached www.brentozar.com took too long to respond.

Feb 14 at 04:42 AM Mehdi593
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Master-detail and partitioning takes som considerations. Normally you would partition on a date-dimension. At least if partitioning is used for being able to quickly archive old stuff. But partitioning could be for totally different reasons. For example to spread out data in different filegroups without any actual business meaning in the key. Say you have four disks and put filegroups on each of them, then you could partition by a modulus-operation on an ever-increasing integer-column to spread the data on four disks (eg to avoid hotspots on disk if the table is for a frequently written to log-table).

But let's assume you will partition on a business key like a date column. Then normally you have that column on the master table. If that's the case, and you want to details table to align with the master table, you would have to duplicate the date key down to the details tables.

Quite often, partitioning requires to also alter existing table schema, not just the clustering key of the table.

In order to make suggestions, you'll have to reveal some more information about your tables and your requirements than "I have a details table and two master tables". What purpose do you have with partitioning the tables? What business keys do you already have in the tables?

more ▼

answered Feb 14 at 08:56 AM

avatar image

Magnus Ahlkvist
22.3k 20 43 43

this scenario is simulation, but exactly same with real scenario.

CREATE TABLE TblStudent (StdID INT NOT NULL PRIMARY KEY, FName VARCHAR(25) NOT NULL, PhoneNum INT NOT NULL) GO

CREATE TABLE TblBooks (BookID INT NOT NULL PRIMARY KEY, BookName VARCHAR(25) NOT NULL, Unit INT NOT NULL) GO

CREATE TABLE TblCourse ( StdID INT NOT NULL CONSTRAINT FK_StdID FOREIGN KEY REFERENCES TblStudent(StdID), BookID INT NOT NULL CONSTRAINT FK_BookID FOREIGN KEY REFERENCES TblBooks(BookID), Grad FLOAT NULL, RegisterDate DATE DEFAULT CONVERT(DATE, SYSDATETIME()), RegisterTime TIME DEFAULT CONVERT(TIME, SYSDATETIME()), CONSTRAINT PK_TblCourse PRIMARY KEY (StdID, BookID) ) GO

i want Partition TblCourse Table. how to do this? How should the partition key be determined?

6 days ago Mehdi593

The first candidate for a partitioning key in the above table TblCourse that I would think of would be the RegisterDate column. If you use that as the partitioning key, you can switch out older dates from the table once you no longer need to keep them.

But the key depends on why you want to partition the table. Unless you know why you want to partition the table, you can't possibly pick a good partitioning key either :)

Anyway, if the purpose is the most common purpose - to be able to quickly archive old data - I would choose RegisterDate. And then you'll have to include RegisterDate in the clustered index of the table. Now... Your clustered index is the primary key, and you probably don't want to include RegisterDate in the primary key. Therefore, you need to make the Primary Key nonclustered, and create a new clustered index. The clustered index could include any columns you want, but the partitioning key must be included in the clustered index.

6 days ago Magnus Ahlkvist

does partitioning table need clustered index ? are you suggest : Denormalization

6 days ago Mehdi593

If TblCourse is what you want to partition, then you don't need to denormalize anything. You already have a date column in TblCourse which can be used as a partitioning key. I can't see how you could possibly align the other tables with the partitioning, so I'd leave them out of the partitioning discussion and only partition the table TblCourse. But still: Why do you consider partitioning? If there's not a clear purpose, then it shouldn't be done.

Regarding clustering key: Yes, the partitioning key must be a part of the clustered index on the partitioned table. Otherwise it can't be used as partitioning key.

6 days ago Magnus Ahlkvist

Thanks for your answer, can you Presentation link or example for Data partitioning Master/Detail Tables ?

6 days ago Mehdi593
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x66

asked: Feb 13 at 05:28 AM

Seen: 52 times

Last Updated: 6 days ago

Copyright 2018 Redgate Software. Privacy Policy