Preserving surrogate keys

Something I've seen in Oracle before is a way of generating surrogate keys for the Primary Keys of a table using dense_rank, and then storing the surrogates in a seperate key table.

Why you may ask? Of course in SQL server you can use identity columns but if you need to rebuild the table from scratch then you have no guarantee that the same surrogate keys will be created for a given 'natural' key.

An alternative is to create a 'key' table that contains only a limited number of field, namely the primary (surrogate) key alongside the natural key of the table. This way, you can always create the table when necessary and are guaranteed to get the same surrogate keys.

In sql server you may need to disable any identity columns before the rebuild but can swicth it back on afterwards.