| mikenz 2006-03-15, 8:23 pm |
| See my thread "Composite index design question" which is sort of related.
I suspect that if your systemID only has several unique values then it would
not make a good choice for the first column of a composite key.
I think probably the best solution is create a new identity column for the
PK, then have a unique index on ( uniqueID, systemID) if you feel it
necessary.
"mkobaly" wrote:
> Currently we have 5 systems that I am copying data from into my
> datawarehouse. The table structures are all identical in that each system has
> a primary key on uniqueID (int, identity) When replicating the data into my
> datawarehouse I am attaching a systemID (int) that uniquely identifies where
> the record came from.
>
> My question is, in my datawarehouse should I make a composite primary key on
> SystemID and uniqueID or should I just use an identity column and/or how
> about creating a bigInt column called BLAH that takes SystemID(S) +
> UniqueID(U). I know UniqueID from the source systems will never go past and
> int so I could create bigint in the form of SSSSUUUUUUUUUU. (Max 4 digits for
> SystemID and 10 for uniqueID)
>
> That way I have one unique column called BLAH vs UniqueID and SystemID and
> I can still tell what system the record came from. Also there are going to be
> multiple tables that have this same structure that will be joined together.
>
> My thought is use an Identity column for PK (clustered index) and create
> unique constraint on BLAH (SSSSUUUUUUUU). My thoughts/concernsa are..
> - Joins should be faster on one column BLAH vs the two
> - There will not be page splits for inserts when I have clustered index on
> identity column
>
> Is there anything I am missing or should be worried about. I would love to
> hear any thoughts and suggestions either way.
>
> Thanks
> -Michael
>
|