Partitions and Common Practice

Hello All,
I posted a simillar question sometime back but I did not see any response. I
have modified my question quite a bit with more info and posting it hoping to
see some response this time :)
I have a Policy Master data file whose uniqueness is identified by the
following
columns (Business Key).
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction was entered into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
We loaded this file into the Staging table of SQLServer 2005 database --
STGPOLICY. The data in this staging table, STGPOLICY, will be processed and
populated into FACPOLICY table.
STGPOLICY Table has these 5 important columns apart from the other columns
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date]
Surrogate Key, UniqID, is an identity column that gets incremented as
records are inserted into the table.
For better performance and Maintainability, we decided to use Partitions on
FACPOLICY table with [Transaction Date] being the Partition Key.
FACPOLICY Table has these 6 important comlumns apart from the other columns
UniqID --> Surrogate Key
Policy Number --> Natural Key1
Effective Date --> Natural Key2
Endorse Number --> Natural Key3
[Endorsement Date] --> Natural Key4
[Transaction Date] --> Partition Key
For faster loading and processing, I have to Partition the STGPOLICY table
on [Transaction Date] column as this column is used for extensive
search/processing. So, I created the partition with [TransactionDate] as the
Partition column. However, when I try to create a unique index on the natural
keys the system gives an error as Transactiondate is not part of the Unique
Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
Date].
I went through the BOL and found that this is the ugly side of PArtitions.
Does that mean that I cannot define a primary key on the natural key when the
natural key is not a Partition Key? I'm left with either creating a nonunique
key or including the Partition Key along with the Natural Key. Is this a
common practice?
Now, with the FACPOLICY table, the UniqID adds another level of intrigue
since the Primary Key would be the UniqID (assuming that there is no
Partitions). With partitions, would it mean that I have to include
TransactionDate to UniqID to create a Primary Key ?
I'm looking for some common practices. Please help.
Thanks,
rgn
Hello All,
Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working on
Partitioning some of the tables for a datawarehousing project and need some
guidance/help.
I have a Policy Master table whose uniqueness is identified by the following
columns via a Primary Key Clustered constraint.
[A] Policy Number
[B] Effective Date
[C] Endorse Number
[D] Endorsement Date
It also has an other column called [Transaction Date] which indicates the
datetime when the transaction came into the system and since it is a
DataWarehousing system lot of queries will be based on the transaction date.
I created a Partition Function, Partition Scheme and the Created the table
on the Partition Scheme with the [Transaction Date] as the Partitioning
Column. Things looked good till this point. However, when I tried to create
the Primary Key on the above 4 columns, I got the following error:
Msg 1908, Level 16, State 1, Line 1
Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
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
I understand what this error means. But I cannot modify the Primary Key by
adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
help?
Thanks,
rgn> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
Another other alternative is to not partition the natural key. The downside
is that you can't use SWITCH to quickly load data with the non-partitioned
index in place. If SWITCH is an important part of you partitioning
strategy, you can drop the non-partitioned unique constraint/index, SWITCH
in the new data and then recreate the constraint/index afterward.
I wouldn't go as far as to say that it is a common practice to add the
partitioning column to a unique constraint/index solely to facilitate
partitioning, but I suspect it is not all that unusual. Bending the
database design rules in favor of performance and manageability is an option
as long as you have mechanisms in place to ensure data integrity.
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
All partitioned indexes include the partitioning column and unique
partitioned indexes must explicitly include the partitioning column in the
key. The partitioning column is implicitly included in non-clustered
indexes (but not part of the key) if not already part of the clustered or
non-clustered key. So, like the natural key, you'll need to add the
partitioning column to the surrogate key to form a composite surrogate key.
It's a little more inconvenient to deal with the composite key but more
palatable that adding the partitioning key to a natural key.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:7B2D7BEE-F322-46B0-8CB4-4493C7C7E86B@.microsoft.com...
> Hello All,
> I posted a simillar question sometime back but I did not see any response.
> I
> have modified my question quite a bit with more info and posting it hoping
> to
> see some response this time :)
> I have a Policy Master data file whose uniqueness is identified by the
> following
> columns (Business Key).
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction was entered into the system and since it is
> a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> We loaded this file into the Staging table of SQLServer 2005 database --
> STGPOLICY. The data in this staging table, STGPOLICY, will be processed
> and
> populated into FACPOLICY table.
> STGPOLICY Table has these 5 important columns apart from the other columns
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date]
> Surrogate Key, UniqID, is an identity column that gets incremented as
> records are inserted into the table.
> For better performance and Maintainability, we decided to use Partitions
> on
> FACPOLICY table with [Transaction Date] being the Partition Key.
> FACPOLICY Table has these 6 important comlumns apart from the other
> columns
> UniqID --> Surrogate Key
> Policy Number --> Natural Key1
> Effective Date --> Natural Key2
> Endorse Number --> Natural Key3
> [Endorsement Date] --> Natural Key4
> [Transaction Date] --> Partition Key
>
> For faster loading and processing, I have to Partition the STGPOLICY table
> on [Transaction Date] column as this column is used for extensive
> search/processing. So, I created the partition with [TransactionDate] as
> the
> Partition column. However, when I try to create a unique index on the
> natural
> keys the system gives an error as Transactiondate is not part of the
> Unique
> Key comprising of Policy Number,Effective Date,Endorse Number,[Endorsement
> Date].
> I went through the BOL and found that this is the ugly side of PArtitions.
> Does that mean that I cannot define a primary key on the natural key when
> the
> natural key is not a Partition Key? I'm left with either creating a
> nonunique
> key or including the Partition Key along with the Natural Key. Is this a
> common practice?
> Now, with the FACPOLICY table, the UniqID adds another level of intrigue
> since the Primary Key would be the UniqID (assuming that there is no
> Partitions). With partitions, would it mean that I have to include
> TransactionDate to UniqID to create a Primary Key ?
> I'm looking for some common practices. Please help.
>
> Thanks,
> rgn
>
>
> Hello All,
> Iâ'm new to SQLServer 2005 though I know SQLServer 2000 well. Iâ'm working
> on
> Partitioning some of the tables for a datawarehousing project and need
> some
> guidance/help.
> I have a Policy Master table whose uniqueness is identified by the
> following
> columns via a Primary Key Clustered constraint.
> [A] Policy Number
> [B] Effective Date
> [C] Endorse Number
> [D] Endorsement Date
> It also has an other column called [Transaction Date] which indicates the
> datetime when the transaction came into the system and since it is a
> DataWarehousing system lot of queries will be based on the transaction
> date.
> I created a Partition Function, Partition Scheme and the Created the table
> on the Partition Scheme with the [Transaction Date] as the Partitioning
> Column. Things looked good till this point. However, when I tried to
> create
> the Primary Key on the above 4 columns, I got the following error:
> Msg 1908, Level 16, State 1, Line 1
> Column 'TRANDATE' is partitioning column of the index 'PK_POLICYHDR'.
> 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
> I understand what this error means. But I cannot modify the Primary Key by
> adding TRANDATE to it. Iâ'm not sure if Iâ'm missing something. Can someone
> help?
> Thanks,
> rgn
>