How to refactor a SQL Server database table to add a surrogate key

Choosing a primary key is one of the most important steps in a database design. There are two types of a primary key – a natural key and a surrogate key.

In this article we will describe how to apply database refactoring on a SQL Server database and replace a natural key with a surrogate key by using ApexSQL Refactor a free SQL Server Management Studio and a Visual Studio add-in that provides numerous features for refactoring of SQL code and uses one of fastest SQL formatters.

Here we will focus on the Add surrogate key option which can replace a complex natural key with a simple surrogate key thus keeping referential integrity, unifying key support for the whole database, and improving database refactoring performance

To perform a surrogate key to a table, select table from the SQL Server Management Studio’s Object Explorer and from the ApexSQL Refactor main menu under the Other refactors sub-menu choose Add surrogate key:

This will open the Add surrogate key dialog. After specifying the Surrogate column name, by clicking the Preview button, the following Preview tabs will be populated with information:

  • Generated script
  • Warnings
  • Sequence
  • Dependencies

The Generated script tab

The preview tab displays a SQL script that will be executed in order to modify the selected table. It can be opened in a new query window by selecting the Create script button, ready for editing or executing via SQL Server Management Studio

The Warnings tab

Second in order, the Warnings tab, will provide warnings, for example, if stored procedure in the database contain INSERT, UPDATE or DELETE statements for the selected table since its structure is about to be changed

The Sequence tab

This tab summarizes the generated SQL script in a tree-like form by showing actions in the order they will be performed. Please note that the action sequence is specifically generated in such a way as to prevent any dependency problems

The Dependencies tab

The last, Dependencies tab, provides a list of dependent objects that refer to the table which will be modified. To preserve dependencies, a generated SQL script will modify dependent objects accordingly

As described, the Add surrogate key dialog provides a detailed preview in several categories of what’s affected and how adding a surrogate key column process will be executed. These can be further analyzed, or you can simply and safely add a surrogate key to the table by selecting the Create script button, which will open script in the Query Editor. After Execute button is clicked, an additional surrogate key column will be created and any dependent objects will be modified to avoid breaking dependencies and improve database refactoring

February 27, 2015