SQL database refactoring – Introduction

What is database refactoring?

Database refactoring is a change in a database schema with a goal to improve a database design and retain both informational and behavior semantics

Informational semantics refers to the information contained in a database from the user’s point of view meaning if the data stored in a column is changed external users of the data shouldn’t be affected by the change. For example, changing the format of the telephone numbers from (541) 754-3010 to 5417543010 will simplify the required coding to work with the data, but for the end user the actual information will remain intact

The same idea applies to retaining behavioral semantic meaning that regardless of the change, the goal is to keep black box functionality the same, meaning that any source code that works with the changed part of database schema must be rewritten to reflect the changes

Reasons for database refactoring

The most common reason for database refactoring is to improve performance whether due to a bad database design or when dealing with a legacy database

The first step is to determine that database refactoring is actually needed and whether refactoring will be more work than a potential gain. In cases where there are many external applications that need to be tested, updated, and deployed to support one refactoring it may not be practical to refactor. However, if the database issue is severe enough refactoring may be needed regardless of a number of effected applications

When the decision to implement refactoring is made, the next step is to choose the most appropriate database refactoring method which also requires detailed analysis and understanding of a problem

Database refactoring step-by-step

The complete process of refactoring is given in the picture below:

Database refactoring process

One of the most important steps in the process of a database refactoring is a database testing. The tests should be implemented before, during, and after the change is made. You should also test all external applications that access the refactored portion of the database before and after refactoring is applied

In a single client application environment there may be no need to delay changes. After the refactoring and testing, you need to refactor only the source code of the application connected to a database, test it, and after a successful test all changes can be deployed to a production

In a multi-application environment every external application is deployed at a different time, so you may need a transition period in which both the refactored and the original schema exist and some applications will use the refactored schema while the other will use the original schema. Suppose you refactored your database schema with the rename method. In the transition period the table with an old name will exist in the original schema, and the table with a new name will exist in the refactored schema. You’ll need to introduce a trigger on both tables to keep the two database schemas synchronized. After the transition period is over the original schema along with any temporary code (e.g. triggers) can be removed from production

All changes to a database schema should usually be implemented as a group of small refactorings. When you apply a small change, and test it to be sure that nothing is broken the possibility that an error will occur is smaller. The advantage of this approach is that if any error occurs it’s most likely that the bug is in the part of the schema where the last change is made

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in for database refactoring and SQL code formatting, and offers database and code refactoring methods. All refactoring features are available under the ApexSQL Refactor menu:

Refactoring features are available under the ApexSQL Refactor menu

Table refactoring methods:

Smart Rename Split Table Add Surrogate Key Replace One to Many Relationship Change Parameters
Table Yes Yes Yes Yes No
Column Yes No No No No
Stored procedure Yes No No No Yes
User defined function Yes No No No Yes
View Yes No No No No

Useful resources:
Introduction To Database Refactoring
Refactoring Databases
What is database refactoring?

March 14, 2014