Generating a primary key

I'm having problems generating the primary key for a sql server table. I use a slowly changing dimension to discriminate modified and new records. The primary key in the SQL Server table is a combo number/letter incremental (ex. 0000A, 0001A...9999A, 0000B...). I tried creating Instead of insert and For insert trigger for a table but this doesn't seem to do the work.

What are my other options? How can I generate a primary key for every new row?

Any advice is appreciated.

Regards

Sara

I personally prefer generating my key values inside the pipeline. For your scheme you would need to write some bespoke code to generate the incrementing value. Use a Script Component to host this code, and output a new column with the value.

Here is an example of this pattern in use - http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/, just tweak teh code to give you your values.

|||

Wow, thank you very much, I followed your example (ignoring the letters, trying with incremental numbers only), it works and it works well!

Just have to play around to make it work with letters too.

Thanx again!

|||

Sara,

Primary keys should never contain logic within them. Why do you need the letters? Using integers, you can achieve the correct order of SCD changes for a given business key, so I don't see the need for letters at all.

|||What do you exactly mean by 'the correct order of SCD changes for a given business key'?|||

Sara4 wrote:

What do you exactly mean by 'the correct order of SCD changes for a given business key'?

I was just trying to make an assumption as to why you'd want letters in your key. With integers, you could select a business key and order by the primary key to see the order of the changes as they occurred. Granted, you could do that with your approach as well, but you're actually going to use more storage using your method (based on your example above) versus using a 4-byte integer field.