partitioned tables - primary key

Hallo all,

Lets say i have a table called Persons with columns ID,PersonType, first
name and surname.
-ID is the real key (the one with the non duplicate values)
-PersonType is the column i would like to partition my table on it.
Given that it is required to have a clusterd index with the partition column
Should i create a unique clustered primary key with both columns
(ID,PersonType)
or
should i create a clustered index on PersonType and a non clustered unique
index on ID?
If its the first option, in which order should i place the coluns when i
design the table and the primary key?
ID, PersonType
or
PersonType, ID?
Thank you very much!

Fotis

Hi all,Help anyone?

Hi all,

Help anyone?

http://blogs.msdn.

http://blogs.msdn.com/craigfr/archive/2006/11/27/introduction-to-partitioned-tables.aspx
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/

While I really like the simple-talk link, I will add the msdn/BOL page.

While I really like the simple-talk link, I will add the msdn/BOL page.

http://msdn.microsoft.com/en-us/library/dd578580.aspx

Still, Uri's links beat what I was going to say up one side and down the
other.

Hallo all,i have already read the links you sent me and its not easy to

Hallo all,
i have already read the links you sent me and its not easy to extract the
answers i want among the lines.
my 2 questions are practical:
what is the best practice on how can i combine the concept of the primary
key clustered table with the concept of the partition table on another
column but the primary key?
-Should i include the partition column in my primary key and denormalize
all foreign key tables and include the column there as well?
-or should i drop the primary key clustered index, create a clustered
index on the partition column instead and then create a non clustered index
with a unique constraint on the actual primary key column?
also, although i did all the reading i couldnt find anywhere a clear
reference on wether the column order matters or not, in the scenario of
including the partition column in the same clustered index with the
primarykey. Should the partition column be first, second, or it doesnt
mattter at all?
the simple talk link mentions something about adding it as second column,
but couldnt confirm it in official microsoft links and on the other hand,
other examples on the web include the partition column as the first one in
the pair. Any clear info on this?

thanks a lot again!

Excellent questions and I am not sure I know the answers your looking for,but

Excellent questions and I am not sure I know the answers your looking for,
but I will try - and if I am wrong, may the MVP's strike me from atop Mount
Olympus.

To start with,there is not a requirment that a partitioned table have a
clustered PK, or that the PK uniquely define rows in each partition (by the
column you are partitioning on). In fact, I would expect the opposite to be
true most of the time. While the simple-talk example does not specify it
explicitly, it is clear that the PK is not used in the partition scheme and
does not have anything to do with where the data is being physically stored.

So I am trying to grasp why you are having trouble and it looks like you think
that the PK of the table is related to how the table is partitioned (where
the data is physically stored). While I prefer to marry these two things and
partition a table on an integer PK across multiple volumes using modulo
division, it is only one way and has its limitations as well as its
benifits.

- The clustered index is controlling the order the data is being stored in
the table, or in this case, each of the partitions.

- The partition scheme is controlling which partition the data is being
stored in, not how the data is physically ordered in the partition.

So, unless the partition column is a natural part of the PK, I would not
include it. I am not even sure it is required to have any index on the
partition column, though it certinally seems desireable.

Perhaps if you thought of each partition as a seperate table, it might be
easier.

Hi Jay,thanks a lot for your answer.

Hi Jay,

thanks a lot for your answer.
Considering i have primary - foreign key relation in my database, lets say
persons and adresses, what i
would do in life before partitioning, would be to put a primary key on my
primary table "persons" and relate it to table "addresses".
the create statment would be like this:

CREATE TABLE [dbo].[Persons](
[ID] [int] not NULL,
[PersonType] [int] not NULL,
[Name] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL
primary key (id)
) ON [primary]

GO


now if i would like to use partitioning ideally i would rewrite it as:

CREATE TABLE [dbo].[Persons](
[ID] [int] not NULL,
[PersonType] [int] not NULL,
[Name] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL
primary key (id)
) ON myPartitionScheme(Persontype)

GO

the only problem is that if i do this, im getting the error

Msg 1908, Level 16, State 1, Line 1
Column 'PersonType' is partitioning column of the index
'PK__Persons__239E4DCF'. Partition columns for a unique index must be a
subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

However, if i rewrite it as

CREATE TABLE [dbo].[Persons](
[ID] [int] not NULL,
[PersonType] [int] not NULL,
[Name] [nvarchar](50) NULL,
[Surname] [nvarchar](50) NULL
primary key (id,persontype)
) ON myPartitionScheme(Persontype)

GO

including the partition column in the primary key, then its perfectly ok.
however, this leads to altering all foregin key tables for to include there
the partition column as well and somehow it violates the uniqness of the
primary key i used to have, since now uniqness is dependant on two columns
instead of the one i always used.

so here is where i would like to ask how can i marry the primary key best
practice i used to know so far, with the concept of partitioning on another
column other than the primary key i used to have.

thanks again,

Fotis

What version of SQL Server are you on?

What version of SQL Server are you on?

OK, I am not going to say I understand this, because I do not.

OK, I am not going to say I understand this, because I do not. However,
according to MS, the partition column must be part of the PK.

http://connect.microsoft.com/SQLServer/feedback/details/380993/partitioning-error-1908-enhance-partitioning

The link talks about the index, not the table, which is confusing. However,
it does seem to indicate your course of action.

Sorry I could not be more help.

OK, this still bothers me. Go back to the simple-talk link:http://www.

OK, this still bothers me. Go back to the simple-talk link:

http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

and search the page for 1908.

He explains this error and what to do.

To search, type Ctrl-F in the browser and enter 1908.

Hi Jay,Yes, i should include the partition column in the primary key.

Hi Jay,
Yes, i should include the partition column in the primary key. this is why
im asking.
if i do so, i cannot anymore guaranty uniquness in my primary key and this
also raises the issue of what happens to all my foreign key ralations. If i
have a two column primary key, this probably means, i should include the
partition column to all detail - foreign key tables as well.
And one other question as well: Suppose i do include the partition column in
the primary key, should this column be first, second or it doesnt matter? If
we talk about the primary key, we also talk about a clustered index and the
way the records are ordered in the db. Is sql server smart enough to ignore
the order i define the columns in the primary key, when i partition the
table?

thanks a lot,
Fotis.

As long as any component of your PK is unique, the entire PK will be unique.

As long as any component of your PK is unique, the entire PK will be unique.


If you change the PK, then yes, you would have to change it everywhere, or
change the FK's that refer to it as non-identifying FK's (with a NOT NULL).
While relationally ugly, that should work, though I do not think it is
acceptable. However, I cannot help but wonder if creating a unique index on
the PK + partition column will deal with the error, without altering your
keys. The Simple-Talk paper was using indexes. I do know that SQL Server
2000 allowed you to build a FK on a table that did not have a PK, but did
have a unique index, so this may be more of the same. I suggest you try it.


I think SQL Server is smart enough to NOT ignore the order. However, having
said that, I see the Simple-Talk unique index examples have one each. Now I
would add the partition key to the end of the index, but thinking about it,
it may not matter as only a subset will be put in each partition anyway.

Think about it, if you are partitioning on a column to begin with, then that
column is going to be in the where clause of any query that is not a table
scan, or badly written.

I think it should be clear to you at this point that while I know stuff
around your issue, I do not know the answer to your specific question.
Perhaps I will have time to try some tests today.