What is database refactoring?

Well, I’m glad you asked :) The best way to explain database refactoring is to offer two different perspectives, depending on the background you are coming from. I offer a view on database refactoring both from the perspective of a seasoned application developer and a traditional database professional.

Application Developer View

For most application developers, the concept of refactoring is not new. Most of the mainstream IDEs, including Visual Studio, have built-in support for automated refactoring. A refactoring is often defined as “a small change to your code which improves its design without changing its semantics.” The whole idea around refactoring is to make small incremental improvements to the design of your application in an iterative and agile way. For the more academic sort, you’ve probably read Martin Fowler’s Refactoring book. The kinds of refactorings you are probably used to are rename refactorings, where you rename a symbol name and all of its references, and extract method, where you take a fragment of a method and separate it out into a reusable method of its own.

Database refactoring is merely extending this concept of refactoring from the application code to your database code. Much in the same way, you can imagine a database rename refactoring would rename a database object and all of its references in the database. Similarly, an extract stored procedure refactoring would pull out a SQL fragment from a stored procedure and place it in its own reusable procedure.

Team Edition for Database Professionals is the first tool to try to bring the same refactoring experience that you have always enjoyed for application development to the data world. We have started this effort with initial support for database rename refactoring fully automated inside the toolset.

Database Professional View

The term refactoring likely means nothing to you. However, let me translate. Let’s take for example database rename refactoring. Terms that we found resonated better with DBAs and DB Devs was the idea of “cascading changes”. Let’s say for example you wanted to rename the Customers table. If you were to perform a rename refactoring on the table, you would rename the table and all the references to the table in views, stored procedures, functions, etc. What a database rename refactoring tool does is find all the references and automate the cascading of these changes for you.

To many database professionals, cascading changes as described above is quite a risky endeavor and often simply avoided. The whole hope with such automated tooling is that managing such changes moves from a fear-inspiring task to one which you can do with confidence.

But now I’m sure you can imagine additional database refactorings. For example, a change type refactoring would allow you to change, say, a column's data type, and it would appropriately change all associated object data types (of foreign keys, of variable assignments of the column, etc). In the future I’m sure we’ll see automated support for many other kinds of database refactorings.

I hope that helped to give you a couple of perspectives on just what is database refactoring. In future posts I’ll be sharing exactly how to leverage Team Edition for Database Professionals to perform a rename refactoring and how to do it safely with confidence.

Sachin Rekhi