| mkobaly 2006-01-05, 8:23 pm |
| 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
|