SK generation in SQL Server 2005/2008

View previous topic View next topic Go down

SK generation in SQL Server 2005/2008

Post  VHF on Wed May 18, 2011 10:09 am

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: SK generation in SQL Server 2005/2008

Post  Mike Honey on Wed May 18, 2011 8:29 pm

IDENTITY, using SSIS
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: SK generation in SQL Server 2005/2008

Post  BoxesAndLines on Wed May 18, 2011 9:15 pm

I manage my own.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: SK generation in SQL Server 2005/2008

Post  apermag on Wed Jun 29, 2011 4:45 pm

BoxesAndLines wrote:I manage my own.

Hi BoxesAndLines,

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

Thanks

apermag

Posts : 17
Join date : 2011-06-28

View user profile

Back to top Go down

Re: SK generation in SQL Server 2005/2008

Post  BoxesAndLines on Wed Jun 29, 2011 6:15 pm

No, I use Informatica Sequence transformation.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: SK generation in SQL Server 2005/2008

Post  VHF on Thu Jun 30, 2011 12:05 pm

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)?



Last edited by VHF on Thu Jun 30, 2011 12:08 pm; edited 1 time in total (Reason for editing : typo)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: SK generation in SQL Server 2005/2008

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum