Microsoft® SQL Server® 2008 Bible

Chapter 28. Building Out the Data Abstraction Layer

IN THIS CHAPTER

• Buying database extensibility

• Building CRUD stored procedures

• Searching stored procedures

I recently blogged the question, "Why use stored procedures?" (http://tinyurl.com/ohauye) and received a firestorm of replies. I invite you to add your view to the replies—let's attempt the most replies on SQLBlog.com.

My post is based on the discussion of extensibility presented in Chapter 2, "Data Architecture," and makes the point that the abstraction layer should be as permanent as the data it encapsulates. The only effective data abstraction layer is T-SQL.

One of the talks I give at conferences is "7 SQL Server development practices more evil than cursors." What's the number one worst development practice on my list? Ad-hoc SQL, because it violates the abstraction layer and creates a brittle database.

There are many good reasons for wrapping the database in a protective layer of stored procedures:

• Extensibility: It's far easier to modify the database when there's a consistent contract to access the database.

• Usability: It's far easier for application developers to call a set of stored procedure API calls that return the correct result set than for them to write correct SQL queries.

• Integrity: The goal is to get the correct answer to the question. Stored procedures written by database developers will include better queries than ad-hoc SQL written by application developers.

• Performance: Moving the lookups and validation closer to ...