Surrogate keys

Question

When loading a dimension in a Data Warehouse, what values do you guys typically use for surrogate keys? I know they are supposed to be integers and should be incremental, but what would be the recommended “seed” value to begin with? Should we start with 1001, 1002 etc? Also, should we have the same values in other dimensions? Or should we start with a different seed for each dimension?
Thanks for your help.

Answer 1

If possible use composite natural keys, e.g. date, client and PO#, if not then UNIQUEIDENTIFIER or GUID wirth NEWID()

---

Arthur My Blog

Answer 2

I usually just use IDENTITY(1,1) for the surrogate keys in my dimensions.

Answer 3

I usually use an integer with an Identity. (int join faster than string/guids). The starting number doesn't mind... it's a meaningless number

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 4

I would say sedd of 1 and strating from 1 makes sense. ultimatley it boils down to how much size u take physically to store, we already know int takes 4 bytes in your case you have to move soon into bigint or maybe larger size, in some time. and that would mean 8 bytes instead of 4 bit.

So i would have thought identity(1,1) makes more sense rather then identity(1000,1000)

---

Abhinav
http://bishtabhinav.wordpress.com/

Answer 5

Same here, just (1,1). in a global dimension I will also add a record that is Id:-1 CodeField:n/a.  This helps resolve things like unknown dates from source system.