SK generation in SQL Server 2005/2008

Informal survey:

For those running on Microsoft SQL Server 2005/2008/R2, how do you generate surrogate key (SK) values for new dimension records? IDENTITY field or another approach?

Please specify if you are using SSIS, hand-coded T-SQL, or other for ETL.

IDENTITY, using SSIS

I manage my own.

BoxesAndLines wrote:I manage my own.

Hi BoxesAndLines,

Can I ask how you manage the SK's? Selecting a max?

Thanks

No, I use Informatica Sequence transformation.

The reason I originally started is this thread is up to now I have been using an IDENTITY column in the dimension table as the SK. I typically use the SQL 2008 MERGE command to update dimension tables from a staging table, which works very well.

However, on the rare occasion I need to completely rebuild a dimension table, I need to be careful not to loose my SK assignments. Yes, I can copy the records to another table, make the structural changes, then bring them back with IDENTIY INSERT turned ON. However, I was thinking it would be nice if the SK assignments were stored separately.

I did come across a mention of of having a key assignment table in the staging database in one of the Kimball books. If I do this, have the SK (as an IDENTITY field) and the business key. When loading the dimension I would merge into key assignment table first to assign keys for new records, then merge into the dimension table (which would no longer have an identity column.) Having the key assignments safely stored in the staging database would allow completely rebuilding the dimension tables as needed without fear of losing the SKs.

My question now is if I adopt this approach should I have a separate key assignment table for each dimension or should I lump all the dimensions (or at least all the dimensions that use a 32-bit key) together into a single key assignment table (and thus in effect a single key generator shared by all dimensions)?