In the data ware house we generally we are hearing about surrogate key when we are implementing the SDC Type -2. A Surrogate Key in SQL Server is a unique identifier for each row in the table. It is just a key. Using this key we can identify a unique row. There is no business meaning for Surrogate Keys. A Surrogate Key is just a unique identifier for each row and it may use as a Primary Key. There is only requirement for a surrogate Primary Key, which is that each row must have a unique value for that column. A Surrogate Key is also known as an artificial key or identity key. It is unique value and it is generally generated by system.
For example: see the table of SCD type 2 dimension
https://bageshkumarbagi-msbi.blogspot.com/2022/05/implementation-of-slowly-changing_17.html
|
CREATE TABLE dimcustomer_scd_2 ( dimcustomer_scd_2_id INT IDENTITY(1, 1) PRIMARY KEY, cus_id INT, cus_nm VARCHAR(50), cus_year VARCHAR(10), cus_add VARCHAR(500), start_date DATE, end_date DATE ) |
dimcustomer_scd_2_id is the Surrogate key for this dimension
table.
