Friday, 13 May 2016

Is Surrogate Key is Required

Here in this article I am just explaining my view related to the Surrogated Key. All depends on situation based scenario. Hope you like it.

What is Surrogate Key
As I am not going to any hardcore definition of Surrogate key over here, I am just going to explain it in my ways.
Surrogate key is just like a Primary key with an incremental number. It is basically an Identity column with data type Big Int. They Columns also contain a Clustered Index.

Use of Surrogate Key in Case of Dimension
In case of dimension we can better use of Surrogate key for SCD Type II case where we maintain the Historical records.
IDNO (PK)    ( Surrogate Key)
EMPID          ( Business Key)`

Is Maintaining the Historical records in Fact Table is Good ?
One use of the surrogate Key is maintaining the Historical records in Fact table. As per my point of view maintaining the historical records in Fact table is not a good idea. As the Additive fact is destroyed and getting bad result in case of aggregation.
If we want to maintain the historical records in Fact table we must use another table to maintain the history.

Here nothing if fix, all depends on case scenario. My be others have different type of views related to it. So please share your views that we can share knowledge.

Posted by: JOYDEEP DAS

1 comment:

  1. Please provide your comments related too it. It is good if you provide some scenario related to it.