When poor performance blights an application, the developer rarely gets free-rein to "mess" with the underlying database structures, in pursuit of a solution; there are just too many dependencies and too much risk. This is intensely frustrating when the developer realizes that the database structure is fundamentally flawed because, without touching the database structures, the task is bound to be a fruitless search for a non-existent fast=true switch, involving snipping around the edges of the problem, tweaking parameters and adding indexes.

The problem is at its worst when the database is close-coupled to the application's other components. You can't then change the database schema without changing the database system's input/output behavior. To make it possible to refractor the database without compromising the integrity of the build, there must be a layer of abstraction between the database and the applications that access it.

'Close-coupling' means allowing direct base-table access from components outside the database. If your database is closely-coupled to your application then you have to change the inputs from the application, in order to produce the same output when you make any schema change. By disallowing direct access to tables, and allowing access only through SQL Server database routines (stored procedures, user defined function and views) in an abstraction layer, we decouple the underlying schema. We can change the schema as much as we want as long as we maintain the abstraction layer to ensure that the output column names and data types stay the same in our abstraction layer.

Even if you are using an Object-Relational Mapping tool (ORM), such as Entity framework, NHibernate, and so on, you still need a well-designed database abstraction layer, and most of the ORMs now work very well views and stored procedures. Here is the problem, though: in my experience, most projects still use ORMs to map objects directly to tables. It's as if the ORM mapping layer is regarded, in itself, as the abstraction layer. It's not; if you map directly to tables, you've still coupled your code directly to the database. As a result, any schema changes often result in application recompilation. If you store the mapping in XML files, you can, in theory, change the file and then just restart the application. Unfortunately, this is not the case most of the time.

ORMs offer a means to map your application domain to the relational database, but you still need the native SQL Server abstraction layer to decouple the database from all the applications that need to access it. Developers need to stop thinking of a database as a simple data store, and more as an equal partner in the application. After all there are usually many different kinds of applications all accessing a single database and we all know that databases last many years longer than most applications.

Mladen Prajdic (guest editor)

Mladen is co-author on the new book, SQL Server Team-based Development, from Red Gate Books, where he discusses database refactoring, as well as testing, in more detail.