SQL Server™ 2005 Bible

Chapter 25. Creating Extensibility with a Data Abstraction Layer

In This Chapter

• Database flexibility

• Hardening the database

• AddNew, Update, Fetch, and Delete stored procedures

It's a common morass. The data schema was designed several years or decades ago and while everyone in the IT organization wants to improve the design, it's just too risky and expensive because there are too many direct connections to the data tables. The greatest software cost is not development, but maintenance; and for the database, dynamic SQL statements that directly access the tables are the greatest cost of maintenance. Like dominoes, any change to the schema will cause a ripple effect of broken code, reports, and DTS packages. The simple select statement has robbed the database of any hope for extensibility and made the database brittle. The strength of SQL—its ability to easily query the data—becomes its weakness. The answer is an abstraction layer.

There are abstraction layers all around us—any common interface that hides the complexity and details of the implementation below the surface is an abstraction layer. A good example of an everyday abstraction layer is the standard driver controls of a car. The steering wheel works as expected without an understanding of the actual linkages to the front wheels. The brake pedal slows the car without the driver understanding how the brake system works. The same is true for the accelerator, the turn signals, the headlights, and so on. The common interface serves ...