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


0
iso
1/26/2010 2:06:13 PM
sqlserver.server 1327 articles. 0 followers. Follow

11 Replies
1093 Views

Similar Articles

[PageSpeed] 52

Hi all,

Help anyone?

"����� �������" <fm@blogica.gr> wrote in message 
news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
> 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
>
> 

0
iso
1/27/2010 2:47:29 PM
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/



"����� �������" <fm@blogica.gr> wrote in message 
news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
> 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
>
> 


0
Uri
1/27/2010 3:07:44 PM
While I really like the simple-talk link, I'll 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.

"Uri Dimant" <urid@iscar.co.il> wrote in message 
news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
> 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/
>
>
>
> "����� �������" <fm@blogica.gr> wrote in message 
> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>>
>
> 


0
Jay
1/27/2010 3:28:48 PM
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!

"Jay" <spam@nospam.org> wrote in message 
news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
> While I really like the simple-talk link, I'll 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.
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message 
> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>> 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/
>>
>>
>>
>> "����� �������" <fm@blogica.gr> wrote in message 
>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>> 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
>>>
>>>
>>
>>
>
> 

0
iso
2/2/2010 12:59:14 PM
Excellent questions and I'm not sure I know the answers your looking for, 
but I will try - and if I'm wrong, may the MVP's strike me from atop Mount 
Olympus.

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

So I'm trying to grasp why you're 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'm not even sure it's 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.

"����� �������" <fm@blogica.gr> wrote in message 
news:Odj$eeApKHA.1548@TK2MSFTNGP02.phx.gbl...
> 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!
>
> "Jay" <spam@nospam.org> wrote in message 
> news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
>> While I really like the simple-talk link, I'll 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.
>>
>> "Uri Dimant" <urid@iscar.co.il> wrote in message 
>> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>>> 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/
>>>
>>>
>>>
>>> "����� �������" <fm@blogica.gr> wrote in message 
>>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
> 


0
Jay
2/2/2010 2:44:25 PM
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


"Jay" <spam@nospam.org> wrote in message
news:Oq$H0YBpKHA.3748@TK2MSFTNGP02.phx.gbl...
> Excellent questions and I'm not sure I know the answers your looking for,
> but I will try - and if I'm wrong, may the MVP's strike me from atop Mount
> Olympus.
>
> To start with,there isn't a requirment that a partitioned table have a
> clustered PK, or that the PK uniquely define rows in each partition (by
> the column you're partitioning on). In fact, I would expect the opposite
> to be true most of the time. While the simple-talk example doesn't specify
> it explicitly, it is clear that the PK is not used in the partition scheme
> and doesn't have anything to do with where the data is being physically
> stored.
>
> So I'm trying to grasp why you're 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'm not even sure it's 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.
>
> "����� �������" <fm@blogica.gr> wrote in message
> news:Odj$eeApKHA.1548@TK2MSFTNGP02.phx.gbl...
>> 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!
>>
>> "Jay" <spam@nospam.org> wrote in message
>> news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
>>> While I really like the simple-talk link, I'll 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.
>>>
>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>>>> 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/
>>>>
>>>>
>>>>
>>>> "����� �������" <fm@blogica.gr> wrote in message
>>>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>>>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>
>

0
iso
2/2/2010 4:13:53 PM
What version of SQL Server are you on?

"����� �������" <fm@blogica.gr> wrote in message 
news:%234ZfQLCpKHA.3748@TK2MSFTNGP02.phx.gbl...
> 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
>
>
> "Jay" <spam@nospam.org> wrote in message
> news:Oq$H0YBpKHA.3748@TK2MSFTNGP02.phx.gbl...
>> Excellent questions and I'm not sure I know the answers your looking for,
>> but I will try - and if I'm wrong, may the MVP's strike me from atop 
>> Mount
>> Olympus.
>>
>> To start with,there isn't a requirment that a partitioned table have a
>> clustered PK, or that the PK uniquely define rows in each partition (by
>> the column you're partitioning on). In fact, I would expect the opposite
>> to be true most of the time. While the simple-talk example doesn't 
>> specify
>> it explicitly, it is clear that the PK is not used in the partition 
>> scheme
>> and doesn't have anything to do with where the data is being physically
>> stored.
>>
>> So I'm trying to grasp why you're 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'm not even sure it's 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.
>>
>> "����� �������" <fm@blogica.gr> wrote in message
>> news:Odj$eeApKHA.1548@TK2MSFTNGP02.phx.gbl...
>>> 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!
>>>
>>> "Jay" <spam@nospam.org> wrote in message
>>> news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
>>>> While I really like the simple-talk link, I'll 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.
>>>>
>>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>>> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>>>>> 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/
>>>>>
>>>>>
>>>>>
>>>>> "����� �������" <fm@blogica.gr> wrote in message
>>>>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 


0
Jay
2/2/2010 4:47:01 PM
OK, I'm not going to say I understand this, because I don't. 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 couldn't be more help.

"����� �������" <fm@blogica.gr> wrote in message 
news:%234ZfQLCpKHA.3748@TK2MSFTNGP02.phx.gbl...
> 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
>
>
> "Jay" <spam@nospam.org> wrote in message
> news:Oq$H0YBpKHA.3748@TK2MSFTNGP02.phx.gbl...
>> Excellent questions and I'm not sure I know the answers your looking for,
>> but I will try - and if I'm wrong, may the MVP's strike me from atop 
>> Mount
>> Olympus.
>>
>> To start with,there isn't a requirment that a partitioned table have a
>> clustered PK, or that the PK uniquely define rows in each partition (by
>> the column you're partitioning on). In fact, I would expect the opposite
>> to be true most of the time. While the simple-talk example doesn't 
>> specify
>> it explicitly, it is clear that the PK is not used in the partition 
>> scheme
>> and doesn't have anything to do with where the data is being physically
>> stored.
>>
>> So I'm trying to grasp why you're 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'm not even sure it's 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.
>>
>> "����� �������" <fm@blogica.gr> wrote in message
>> news:Odj$eeApKHA.1548@TK2MSFTNGP02.phx.gbl...
>>> 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!
>>>
>>> "Jay" <spam@nospam.org> wrote in message
>>> news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
>>>> While I really like the simple-talk link, I'll 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.
>>>>
>>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>>> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>>>>> 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/
>>>>>
>>>>>
>>>>>
>>>>> "����� �������" <fm@blogica.gr> wrote in message
>>>>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 


0
Jay
2/2/2010 4:55:23 PM
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.

"����� �������" <fm@blogica.gr> wrote in message 
news:%234ZfQLCpKHA.3748@TK2MSFTNGP02.phx.gbl...
> 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
>
>
> "Jay" <spam@nospam.org> wrote in message
> news:Oq$H0YBpKHA.3748@TK2MSFTNGP02.phx.gbl...
>> Excellent questions and I'm not sure I know the answers your looking for,
>> but I will try - and if I'm wrong, may the MVP's strike me from atop 
>> Mount
>> Olympus.
>>
>> To start with,there isn't a requirment that a partitioned table have a
>> clustered PK, or that the PK uniquely define rows in each partition (by
>> the column you're partitioning on). In fact, I would expect the opposite
>> to be true most of the time. While the simple-talk example doesn't 
>> specify
>> it explicitly, it is clear that the PK is not used in the partition 
>> scheme
>> and doesn't have anything to do with where the data is being physically
>> stored.
>>
>> So I'm trying to grasp why you're 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'm not even sure it's 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.
>>
>> "����� �������" <fm@blogica.gr> wrote in message
>> news:Odj$eeApKHA.1548@TK2MSFTNGP02.phx.gbl...
>>> 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!
>>>
>>> "Jay" <spam@nospam.org> wrote in message
>>> news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
>>>> While I really like the simple-talk link, I'll 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.
>>>>
>>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>>> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>>>>> 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/
>>>>>
>>>>>
>>>>>
>>>>> "����� �������" <fm@blogica.gr> wrote in message
>>>>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 


0
Jay
2/2/2010 5:08:26 PM
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.

"Jay" <spam@nospam.org> wrote in message 
news:udPOSpCpKHA.4836@TK2MSFTNGP05.phx.gbl...
> 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.
>
> "����� �������" <fm@blogica.gr> wrote in message 
> news:%234ZfQLCpKHA.3748@TK2MSFTNGP02.phx.gbl...
>> 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
>>
>>
>> "Jay" <spam@nospam.org> wrote in message
>> news:Oq$H0YBpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>> Excellent questions and I'm not sure I know the answers your looking 
>>> for,
>>> but I will try - and if I'm wrong, may the MVP's strike me from atop 
>>> Mount
>>> Olympus.
>>>
>>> To start with,there isn't a requirment that a partitioned table have a
>>> clustered PK, or that the PK uniquely define rows in each partition (by
>>> the column you're partitioning on). In fact, I would expect the opposite
>>> to be true most of the time. While the simple-talk example doesn't 
>>> specify
>>> it explicitly, it is clear that the PK is not used in the partition 
>>> scheme
>>> and doesn't have anything to do with where the data is being physically
>>> stored.
>>>
>>> So I'm trying to grasp why you're 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'm not even sure it's 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.
>>>
>>> "����� �������" <fm@blogica.gr> wrote in message
>>> news:Odj$eeApKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>> 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!
>>>>
>>>> "Jay" <spam@nospam.org> wrote in message
>>>> news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
>>>>> While I really like the simple-talk link, I'll 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.
>>>>>
>>>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>>>> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>>>>>> 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/
>>>>>>
>>>>>>
>>>>>>
>>>>>> "����� �������" <fm@blogica.gr> wrote in message
>>>>>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
> 

0
iso
2/3/2010 9:33:46 AM
> 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

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

> 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.

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 don't think it's 
acceptable. However, I can't 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 didn't have a PK, but did 
have a unique index, so this may be more of the same. I suggest you try it.

> 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?

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're partitioning on a column to begin with, then that 
column is going to be in the where clause of any query that isn't 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 don't know the answer to your specific question. 
Perhaps I'll have time to try some tests today.

>
> thanks a lot,
> Fotis.
>
> "Jay" <spam@nospam.org> wrote in message 
> news:udPOSpCpKHA.4836@TK2MSFTNGP05.phx.gbl...
>> 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.
>>
>> "����� �������" <fm@blogica.gr> wrote in message 
>> news:%234ZfQLCpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>> 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
>>>
>>>
>>> "Jay" <spam@nospam.org> wrote in message
>>> news:Oq$H0YBpKHA.3748@TK2MSFTNGP02.phx.gbl...
>>>> Excellent questions and I'm not sure I know the answers your looking 
>>>> for,
>>>> but I will try - and if I'm wrong, may the MVP's strike me from atop 
>>>> Mount
>>>> Olympus.
>>>>
>>>> To start with,there isn't a requirment that a partitioned table have a
>>>> clustered PK, or that the PK uniquely define rows in each partition (by
>>>> the column you're partitioning on). In fact, I would expect the 
>>>> opposite
>>>> to be true most of the time. While the simple-talk example doesn't 
>>>> specify
>>>> it explicitly, it is clear that the PK is not used in the partition 
>>>> scheme
>>>> and doesn't have anything to do with where the data is being physically
>>>> stored.
>>>>
>>>> So I'm trying to grasp why you're 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'm not even sure it's 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.
>>>>
>>>> "����� �������" <fm@blogica.gr> wrote in message
>>>> news:Odj$eeApKHA.1548@TK2MSFTNGP02.phx.gbl...
>>>>> 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!
>>>>>
>>>>> "Jay" <spam@nospam.org> wrote in message
>>>>> news:uzLEtV2nKHA.1556@TK2MSFTNGP05.phx.gbl...
>>>>>> While I really like the simple-talk link, I'll 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.
>>>>>>
>>>>>> "Uri Dimant" <urid@iscar.co.il> wrote in message
>>>>>> news:%23$$jwJ2nKHA.4648@TK2MSFTNGP06.phx.gbl...
>>>>>>> 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/
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "����� �������" <fm@blogica.gr> wrote in message
>>>>>>> news:OhjjVDpnKHA.1544@TK2MSFTNGP02.phx.gbl...
>>>>>>>> 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
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
> 


0
Jay
2/3/2010 2:55:50 PM
Reply:

Similar Artilces:

setting up tax tables in RMS
I have a client who has special tax needs. $0-$1600 is taxed at 9.25 $1601-$3200 is taxed at 9.75 $3201- and above is taxed at 7% so for example- for a $3,300.00 item- the tax would be $311.00. How would I set this up in RMS? Help! Thanks Andrea Andrea, Create 3 Sales Taxes. Sales Tax 1 Minimum Taxable Amount = None Maximum Taxable Amount = $1,600.99 Tax Rate = %9.25 Sales Tax 2 Minimum Taxable Amount = $1,601.00 Maximum Taxable Amount = $3,200.99 Tax Rate = %9.75 Sales Tax 3 Minimum Taxable Amount = $3,201.00 Maximum Taxable Amount = None Tax Rate = %7 Assign all of these taxes to one...

Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot table? I have a pivot table that is returning totals at the bottom, as it should, but I *also* need it to return that total as a percentage of grand total, directly beneath the total. I've always done this free-form in the cells below a pivot table before, but the size of this pivot is dynamic so that's not an option. Also--I'm using the pivot in Access, not Excel directly. Anybody have any tips? Thanks! ...

html table import
Hi Our sales organization came up with the idea of taking a web based financial tool, write a huge Excel sheet around it, and update the data every week. From it, they get all kinds of stats and results for the management. So far so good. I'm in charge of the web application. Now since a few months, when they import, after exactly 139 lines, the formatting changes in excel. Instead of being right aligned, the table cells are suddenly left aligned (starting on column i), the 0.00 in the source is suddenly shown as such where it was 0 in the 138 lines before. Then after a few hundred...

Pivot Table Splits Columns for same entry #2
Thanks for the response. However, I seem to have misrepresented m problem because the solution doesn't help. In the Layout for the Pivo Table I have the following: In the "ROW": Item#, In the "COLUMN": Tota Lost $, Frequency of Reports generated by item #, In the "DATA": sum th counts of each item #. My chart displays the Item# on the x-axis, th sum the counts of each item # on the y-axis, the Total Lost $ and th Frequency of Reports generated by item # in the legend. There should only be one column per item # whether the chart type i stacked or clustered. I...

To type I need to depress keys for twice as long as usual. Why?
Having inadvertently depressed a "shift" key for longer than 6 seconds, I have created a problem with typing. To type I need to depress the keys for about twice as long as usual, with an associated sound as well. I can temporarily correct this by depressing the shift key again for longer than 6 seconds but I need to make the correction more permanent. How do I do this? This is a Windows thing, not specifically Word. Go to Start menu > Control Panel > Accessibility Options (or in Vista or Windows 7, Ease of Access Center). Turn off Filter Keys. To prevent it from ...

Form creates unwanted rows in table
I've created an asset database which keeps track of computers, laptops etc. There is one company which has many departments and every department has many rooms. (surprise, surprise) There are four tables: tables Department and Asset and two "middle" tables DepartmentRoom and AssetRoom. The table structures are as follows: DEPARTMENT id name dep_type dep_num etc... ASSET id name a_type ip_num vendorID serial_num etc... DEPARTMENTROOM id name depID (foreign key to department) ASSETROOM id assetID (foreign key to asset) roomID (foreign key to depa...

Need to remove data in table due to input mask; cannot close.
ACCESS Table open, unable to close or modify telephone field. Had tried i/p mask of 0 due to "Unlisted" numbers appearing right-justified when doing a report. I sure thought I changed it back to a NO MASK prior saving the table. Now, table is open and I am unable to do anything with that field, even deleting the content, to satisfy the mask problem. Hands seems to be tied in 'catch 22'; I would have thought (!!??) that the table would not have saved properly if the input mask did not match the field contents. I'm stuck! Thank you so much. =?Utf-8?B?b2ZmZXJvY2t...

Pivot table calculated field: multiply Sum of FieldA with Max of FieldB
Hello usenetters, My question concerns Excel 2003. A user asked me about a calculated field in a pivot table. Some of the fields are DT_NOW, GW_WCR, GW_LOB. The formula of the calculated field should depend on the date (DT_NOW): Up to 30/06/2009: Rotation = GW_WCR / GW_LOB * 91 Starting 01/07/2009: Rotation = GW_WCR / GW_LOB * 28 She added a field to the underlying data called ROT_FACTOR, filled with 91 until 30th june and filled with 28 starting 1st july. The current formula is =IF(ROT_FACTOR>300000;GW_WCR/GW_LOB*91;GW_WCR/GW_LOB*28) The cutoff value 300000 is an arbitrary value base...

Pivot Table Defaults Turn Off Subtotal
Is there a way to change the default behavior of Pivot Tables? Specifically, can I change the default behavior for a Field to NOT show the subtotals? I know how to turn it off easy enough - it's just frustrating doing it over and over. Thoughts? I have not answer, just want to agree that this is a frustrating issue. Ie, everytime I build a Pivot Table, I have to go through the process of manually turning off the subtotals. Another issue I run into is that generally my pivot tables need to have the 'Field Settings' / 'Layout & Print' changed via a manual process ...

DAO and Table Locking
Greetings, I've written some code to create a relationship between table A and table B. The code looks something like this: Set rel = db.CreateRelation("A_B") rel.Table = "A" rel.ForeignTable = "B" Set fld = rel.CreateField("AID") fld.ForeignName = "BID" rel.Fields.Append fld db.Relations.Append rel This code works just fine except that a few lines later, I use DLookup() to find a row in table B, but I then get an error that table B is exclusively locked and cannot be accessed. Can anyone clarify why table B has been...

Table Indexes after upgrade to Dynamics GP v9
After upgrading to Dynamics GP we received an error that was due to "Index 'AK6GL00100' on table 'GMI.dbo.GL00100' does not exist". While troubleshooting this problem I found that there were not any clustered indexes on my GL00100 or GL00105 tables. So, I found the Indexes.cmp in the SQL\Install\0\Company on the server and tried to load the indexes for those tables from that file. That's when I discovered that the indexes in this file were not consistent with our account framework and assumed only 5 segments were in use. Thus, the reason why they failed to loa...

table of numbers 1 thru 52 pre sql2k5
Hi all, What's the easiest way to generate a single column of numbers in a table prior to sql 2k5? thanks, rodchar Check out: http://msdn.microsoft.com/en-us/library/aa175802(SQL.80).aspx -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" <rodchar@discussions.microsoft.com> wrote in message news:C3DD455A-8DC5-42CD-884D-EB67EF6A4B5A@microsoft.com... Hi all, What's the easie...

Function Keys in Excel
Can someone please tell me what the F2 key does in Excel 2000? I have a column of data which has been copied from Access 2000 into an Excel 2000 sheet. It is numeric data and when I use the "Countif" function on this data, it doesn't calculate. However, if I press the "F2" function key in any of the cells, something happens to the data so that the "countif" function recoginises it. Can anyone explain what is happening here? Thank you. -- LPS F2 allows you to edit directly in the cell, do you have calculations set to automatic under tools>calcul...

copy record between tables
Hi, I have two tables with same structure. I need to copy a record from oneto other using vba codes. I'd like avoid to copy field to field.Thank you. Without knowing anything about your table structures, something similar to the following:Dim strSQL As StringstrSQL = "INSERT INTO tblCopy SELECT tblOriginal.* " _ & "FROM tblOriginal WHERE tblOriginal.KeyValue = 4"CurrentProject.Connection.Execute strSQL"Alberto" <saveas[at]iol.it> wrote in message news:uk2oUMBYHHA.3656@TK2MSFTNGP05.phx.gbl...> Hi,> I have two tables with same structure....

Exporting Ppivot table from Access to Excel
Hi there, (1) I am working with a huge Access database with 346,722 records (2) I created a pivot table in Access and that works (3) Now, I want to export this table to Excel (4) When I try to do this, it says "No Data" and returns a blank Excel page I understand that there is a limitation on the number of lines in Excel (~64K). But, since I have a massive database file, how can I possibly export the Pivot table to Excel? Any comments will be greatly appreciated! Thanks!!! rgds Kaushik You can build a series of queries based of the pivot that break the result down to manageab...

Help with pivot table
Hi, I am looking for a fix in below code, probably addition to it. I'm trying to export a query from ACCESS 2003 to EXCEL 2003. I do that each time with the export to excel button available in access which generates a temp file which I have to save all the time by naming it into appropriate excel file format. So far I have managed to export only the data sheet. I'm stuck with creating the pivot table portion in same code. These few lines I found while browsing and changed them w.r.t. my requirements. I would like to have a pivot table in sheet2 based on data ran...

Enter key will no longer return to begining of next row
From one worksheet to the next, the enter key will only activate the cell directly below the current cell. What Have I done to change the direction? Have checked the direction selection in Options and as suggested by MS Help made sure the Down was selected. Doesn't change a thing. Help! Sandy look in Tools | Options | and on the Edit tab check what's in "Move selection after Enter" Regards Trevor "Sandy" <anonymous@discussions.microsoft.com> wrote in message news:212b01c3e06b$ac7afe50$a401280a@phx.gbl... > From one worksheet to the next, the ent...

Can Pivot Table layout be copied???
A few weeks ago, I ended up with the perfect Pivot Table layout by dragging fields around -- I just don't remember how I got there! I have attempted to create a similar table (with new data) but, even though the field layout buttons are in the exact same locations, the table looks different. I'm trying to get "Sum of 2004", "Sum of 2005," etc. as column headings across the top instead of having all years one below the other. Is there any way to copy the layout from the "perfect" table to the new one? I answered my own question, with help from De...

How turn on Classic Pivot Table Settings
How does one turn on Classic Piviot Table Setting from Excel 2007. ...

sum items in a table based on description
I am interested in summing items is a table based on their description. Is there a formula that will add items together from column based on the contents in another cell reference in the same row? I know a pivot table will do this with some restrictions in the table design. You can use sumif if there is onbly one condition Assume you want to sum values in column C where column B is "x" =SUMIF(B2:B500,"x",C2:C500) Regards, Peo Sjoblom "Hrider" <hrider@yahoo.com> wrote in message news:ejggYE$MFHA.3328@TK2MSFTNGP14.phx.gbl... >I am interested ...

update data in table for new Format
I have a table with several thousand records. currently i have serial numbers stored as number but i now need to change the format. currently it is 4-8 digits. the new format should be xx-xxxxxx (2 digits a "-" and 6 more digits). I need this new value to store the "-" in the field. I know i can switch the field to a text field and this works but i need a way to convert existing data to the new format. I tried a mask format but it starts at the beinging. so if i put in a ##-###### and my data is 4021 it gives me 40-21 and i need 00-004021. Any ideas on this...

Median in pivot tables
The Excel pivot tables have a variety of functions. The one function that appears to be lacking is median. I'd sure like to be able to determine the median value grouped by some other variable. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

when replicating, I get error message, but there is no table
when replicating with my assistant, I get that there are replication errors, but when I try to open the conflicts table, I get that there are "no conflict tables" =?Utf-8?B?YmttNGVhZ2xl?= <bkm4eagle@discussions.microsoft.com> wrote in news:5246CFE9-3BC1-4411-AB08-3E7B8D67D433@microsoft.com: > when replicating with my assistant, I get that there are > replication errors, but when I try to open the conflicts table, I > get that there are "no conflict tables" You'll have to look at the tables directly. The conflict tables are called BaseTabl...

SQL statement to find a particular column within all tables
I am looking for a query that will allow me to find all instances of a particular column within all tables so I know where they are all located. Does anyone have such a query they would be willing to share? Thank you. Pam, I posted this query a few months aback on my blog (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt: select distinct rtrim(objs.name) from syscolumns cols inner join sysobjects objs on (cols.id = objs.id) inner join sysindexes indx on (cols.id = indx.id) where (cols.name = 'ACTINDX') and (ob...

Fast alternative to table based state transition matrix
I got this answer from comp.theory. It was completely obvious once it was explained. It is trivially simple to create a DFA based recognizer without a state transition matrix data table. Simply encode case statements corresponding to inputs within the case elements of a case statement corresponding to states. In at least some cases the (case within case) method might be faster depending upon whether or not memory is reduced enough to more than offset the higher case statement overhead to increase cache locality of reference. "Peter Olcott" <NoSpam@OCR4Screen.com...