Programmers have struggled for years to provide a bulletproof method of manipulating data. This endeavor has brought about many changes in the way applications are written and designed. While each application takes a slightly different approach to managing data, one methodology remains constant for all applications: Use a consistent procedure for access, additions, modifications, or deletion of data. You will have far fewer problems in the long run.
Enter Stored Procedures
Adopting a standard method of data access is not difficult. Each company may choose a different path, but all have the same end result in mind. Use one way to do things, with as few “special-case” changes as possible. Following the same procedures for inserting data or error checking across all your code ensures that anyone looking at the code can easily figure out what must be done to add data to one of your systems. As long as every programmer follows the guidelines that you have established for that particular system, he or she will not create problems for other applications or processes running in your production environment.
Most of the standards presented here are gleaned from production
environments I’ve encountered. I am, of course, not the absolute authority on your system, nor is anyone else that does not have a thorough understanding of your specific needs. Take what I have to say here with a grain of salt. Apply what will work for you and put the remaining things into memory for later consumption. After working with these for a while and seeing what standards can do for you, you might find that you can come up with standards and methodologies that meet your needs precisely.
Establishing Standards
At this point you’re probably asking, “What standards are you talking about?” Any standard you can come up with will probably work, and each enterprise has unique requirements that would cause one that I might suggest here to be invalid. I will offer a set of standards here; however, you must decide for yourself whether to adopt them in whole or in part.
I use standard naming conventions with database objects, just as I do with programs that I write. The naming conventions help me recall the function or purpose of the object if I have not looked at it for a few months. I also use standard methods for manipulating data. Using a certain method to edit data ensures that consistent client manipulation is used across all programs.
Previous Table of Contents Next
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
Brief Full Advanced
Search Search Tips
To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book
(Publisher: The Coriolis Group)
Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97
Search this book:
Previous Table of Contents Next
Getting Data
When a client application needs data, whether in a true client/server environment or in a multitiered system, the act of reading data usually is different than the act of making changes or additions. Therefore, for getting data to client applications I use a stored procedure.
These procedures usually are called something like CP_GETMESSAGE, CP_GETORDER, or CP_LOOKUPRECORD. The CP is used for a few reasons. First, in an alpha list, these names would show up before any system or external procedures, since these latter procedures are prefaced with SP and XP, respectively. Second, each of these objects, along with many others, exists in many system tables on your server. You can distinguish easily between a stored procedure and another object by looking at the prefix.
It is well worth the time to develop a good naming convention for all your server objects. Having a standard prefix for each object in your system and publishing them among all programmers will avoid any potential confusion and can be a lifesaver. In a multiple-programmer environment, consistent naming conventions are practically a requirement. See Table 6.1 for some sample naming conventions. Keep in mind that these are only suggestions; they should be thought out thoroughly before being adopted in any
environment. If your enterprise has an existing naming convention in use, try to mirror that as closely as possible to reduce confusion.
Table 6.1 Some server naming conventions.
Object Prefix http://www.itknowledge.com/reference/standard/1576101495/ch06/191-193.html (1 of 3) [1/27/2000 6:16:31 PM] Go! Keyword --- Go!
Table TBL_
View VIEW_
Clustered Index CIDX_
Unique Index UIDX_
Index IDX_
Cursor CUR_
Local Variables TMP_
Stored Procedure CP_
Insert Trigger INST_
Update Trigger UPDT_
Delete Trigger DELT_
Rules RUL_
Defaults DEF_
Another benefit of using stored procedures to transfer data between client applications is the security associated with stored procedures. Users or groups of users can be granted permission to run only those procedures to which they need access. If no permission is granted, the data is protected from access. I rarely recommend giving users direct access to query tables or views on production systems. Because ad hoc query tools make maintaining data
security much more difficult, using stored procedures as a method of accessing data is invaluable. If a user is only allowed to call a procedure to read the data, you are protected from any accidental user modification.
For performance reasons, I typically do not allow general queries to run against tables. If I write the queries that are getting the data from the server and allow others to run that code through stored procedures, I am ensuring that no long-running or system-resource-killing query can be introduced while production is running its queries.
I can better spend my time tuning the queries for speed and maintaining data integrity. Most client applications that use stored procedures to get data need to know only the name of the procedure and the data type of any parameters required to execute the procedure. If I change the underlying code of the procedure without changing the parameter list or names, I can update and improve my stored procedures without having to rebuild any client
applications. Improving performance without changing or affecting client code is an important reason to use stored procedures instead of standard SQL in a client/server environment.
Modifying Data
All systems must have the ability to add, change, or delete records on an ongoing basis. Stored procedures are valuable in this area as well. They provide two types of security: the additional security of not allowing anyone who doesn’t have permission to run a particular procedure to modify data, and the built-in integrity of each client utilizing the same method to perform any
modification task.
In addition, stored procedures allow you to mask from your client applications the complexity of the underlying data structure. If a procedure requires derived or table-driven data to complete a task, you can look up what is needed in your stored procedures without the user ever having to know what is happening. Concealing the complexity of your data structure from the user and client application has many benefits. It typically reduces the amount of traffic between the client and the server and gives you much more control over potential locking issues.