Surrogate Key Concept Part -7

Surrogate Key

Ø  4-byte integer key
(can hold more than 2 billion positive integers)

Ø  Primary key for Dimensional Table

Ø  Internally assigned and meaningless

4  insures uniqueness

4  always known

Ø  Used in conjunction with business keys

4  business key is often mnemonic; for example, OTA used for Ottawa office

4  surrogate key is numeric; for example, 000128

Ø  Surrogate keys are never used in reports. They are used to link dimension tables to fact tables.

Ø  Play a vital role in maintaining SCD in Data Warehousing

Advantages of using Surrogate Key

Ø  Reduce space in big fact tables

Ø  Play a vital role in maintaining SCD in Data Warehousing

Ø  It allows duplicate natural key entry in the table in case of SCD

Ø  This allows the database to query the single key column faster than it could multiple columns.

Ø  Search is faster on numbers which is very important for huge data in a data warehouse.

Ø  Flexibility for changing requirements

Ø  Attributes that uniquely identify an entity might change. So attributes initially chosen will no longer be natural key for the table. Surrogate keys avoid problems from choosing a natural key that later turns out to be incorrect.

Surrogate Keys Exp

Surrogate Key Exp