The Role of Stored Procedures in the Development of Database Applications

To properly design and use stored procedures in the development of applications, it is critical to understand their role and advantages.

Enforcement of Data Integrity

The most important task for each DBA is to maintain the data integrity of the database that he or she is managing. If a DBA is not almost fanatical about data integrity, the results for the database will be potentially disastrous. During my career, I have encountered databases with

• 106 different provinces of Canada (one of them was France)

• An Address column filled with "Guest had frozen Fish."

• Nine ways to write HP LaserJet III…

Stored procedures are an ideal tool to help you standardize and control data entry, and to implement validation of information and even the most complex constraints.

Consistent Implementation of Complex Business Rules and Constraints

Transact-SQL stored procedures are powerful enough to implement even the most complex business rules because they can combine both procedural and set-oriented statements. Everything that is too complicated to be implemented using other constraints and that is procedural and not just set-oriented can be implemented in the form of a stored procedure. These are complex and important considerations and will be expanded upon throughout the remainder of this book.

Note 

Naturally, stored procedures are not the only way to implement business logic on the server. Three-tier architecture envisions implementation of business services on a middleware server.

Modular Design

Stored procedures allow developers to encapsulate business functionality and provide callers with a simple interface. Stored procedures behave like a black box. The caller does not have to know how they are implemented, just what they do, what input is required, and what output will be generated. From a development standpoint, this also reduces the complexity of the design process. You do not need to know how a stored procedure is implemented. You just need to reference it in your application or your own procedures.

Maintainability

System design is a cyclic process. Every system needs to be reviewed, changed, and improved. By hiding database structure details behind stored procedures, database administrators can reduce or hopefully eliminate the need to change all other components (that is, client applications and middleware components) of the system whenever they change the database structure.

Microsoft has achieved the same goal with system stored procedures and system tables. Although you can use the contents of system tables directly in your applications, you should base your code on system stored procedures because Microsoft reserves the right to change tables from version to version but has promised to keep the interface and functionality of stored procedures intact.

Another advantage is that stored procedures are implemented on the server and can be maintained centrally. If the business logic is implemented in the client application, a huge effort will be needed to deploy changes.

Reduced Network Traffic

One of the major disadvantages of file-server architecture is high network traffic due to the fact that entire files are being transferred across the network. If a client/server system is well designed, the client will receive just the information it needs, which is usually just a slight portion of the database, thus significantly reducing the network traffic.

If a client/server system is implemented with even more of the processing/business logic on the server (that is, using stored procedures), even less data will be transferred back and forth through the network.

Faster Execution

Stored procedures have several performance advantages over ad hoc queries. Stored procedures are cached in a compiled form on the database server, so when they need to be used, the server does not have to parse and compile them again.

A developer can optimize a stored procedure's code so that every user will use the best possible method to perform an action.

Enforcement of Security

One sign of a well-designed database system is that it prevents users from directly accessing the tables and forces them to use stored procedures to perform specific functions. It is also easier to manage a set of stored procedures by functionality than to manage table-and column-level permissions.