What is a surrogate key, and under what circumstances would you use one?

Subrogated keys are a concept used in database design, especially in Data Warehouse (DW) and Business Intelligence (BI) enviroments. Subrogated Keys are often used especially in versioned or historical dimension tables, that is, dimesion tables that store both current data and historical data. The concept of Subrogated Key is not unique to SQL Server, that is, Subrogated Keys are a general concept that can be applied to any database engine (oracle, MySQL, etc).

The benefits of Subrogated Keys are:

- It facilities efficient partitioning of physical data.

- Create a separation of multidimensional models to facilitate change control.

- Improve the performance of operations.

- It allows you to read more data with less input and output operations because the indexes are smaller.

In conclusion, the main one is to improve the response time of the database since searching between integers is the least cost of the database; but there are also others, for example, if we are gathering information from different transactional systems in a data warehouse and each one of them has keys that do not look anything like each other, the surrogated keys will allow us to have a homogeneous key for both systems.