Suiggestiosn For Choosing The Partition Key

Question

Suiggestiosn For Choosing The Partition Key

dedicatedtosql

Ten Centuries

Points: 1147
More actions
April 2, 2014 at 5:43 pm

#396529

Hello All,
I need some guide lines to choose a proper partitioning key for tha tables. LIke below:
What Data type should be choosen?
Can Clurstering Key be diffrent from Partitioning Key?
How should the Primary Key Be defined?
What is the affect of partitioning to Non clustred indxes?
Can any one direct me to a good link or something.
Please help.
Regards,
Nawaz.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

dedicatedtosql (4/2/2014)
Hello All,
I need some guide lines to choose a proper partitioning key for tha tables. LIke below:
What Data type should be choosen?
Can Clurstering Key be diffrent from Partitioning Key?
How should the Primary Key Be defined?
What is the affect of partitioning to Non clustred indxes?
Can any one direct me to a good link or something.
Please help.
Regards,
Nawaz.

Before you ask those questions, ask yourself these...

1. Why am I looking to partition this table? What are the benefits that I'm looking for? If it's for performance reasons, I recommend NOT partitioning because it seldom results in performance improvements unless every query brings the partitioning column into play.

2. If you have foreign keys that will point at this table and you want to maintain the benefits of using SWITCH, then forget about it unless the partitioning column IS the PK because all unique indexes must have the partitioning column added to them if you want to use SWITCH.

3. What kind of table is it? Is it a type of audit table where the rows are inserted but never updated? If that's the case, consider using the "DateCreated" column as the partitioning column/first column of the clustered index and an IDENTITY column as a temporal tiebreaker and the second column of the clustered index.

4. How big is the table? If it's less than 10 or 20 million rows and you have the time to back the whole table up every night, consider not partitioning.

There are a ton of other considerations, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)