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 it will be described how to apply database refactoring on a SQL Server database and replace a natural key with a surrogate key by using ApexSQL Refactor a 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, the focus will be 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 menu under the Other refactors sub-menu, choose the Add surrogate key command:
This will open the Add surrogate key window. After specifying the Surrogate column name, with a click on 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 with a click on 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 window 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 simply and safely add a surrogate key to the table with a click on the Create script button, which will open script in the Query Editor. After the 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
