|
|
Archives of the TeradataForum
Message Posted: Mon, 17 Oct 2005 @ 12:13:29 GMT
Subj: | | Re: Bugs with identity column |
|
From: | | Victor Sokovin |
| The only thing, which is a bit tricky is, that, once you have to make a copy of a table with an identity column, you may not be able to
supply uniqueness anymore. The new ( copied ) table will get a new pool of IDs for the identity column. The new pool is then not aware of the
already used IDs in the copied table. | |
| If you want to use the identity column as a surrogate key, this is crucial. | |
At first sight, system generated surrogate keys seem to be easy to use but they are tricky in daily maintenance of the DWH. The above is just
one of the concerns.
I like the solution you have shared with us but I must admit that if I had to follow it I would be tempted to extend it and eliminate the
identity columns at all. Really, they do not add a lot of value in this context, do they? All you need is a simple sequence for each load. Such a
sequence could be generated in the ETL process, for example. This way we still have sequences but do not have to face restrictions associated with
identity columns.
This is not to discourage the OP. Identity columns do not need to be surrogate keys per se and could be safely used for other purposes.
Regards,
Victor
| |