Uses for Stored Procedures

You might already have a good idea of what can be done with stored procedures.However, probably much more can be done with stored procedures than you've everthought of. Truthfully, probably quite a bit more can be done than I have ever thought of. The great thing about stored procedures is that, as a developer, you cancreate stored procedures that do whatever you need them to do. The following is alist of some of what you can do with stored procedures and reasons for using them.Most of these items are covered in this book; over time, you will discover others.

? Encapsulation of Queries— One of the first uses I discovered for stored procedures was to encapsulate queries so that I didn't have to worry aboutwhere they were saved, and I could execute them from anywhere on the network. The first queries I wrote were simple SELECT statements that returned extremely simple information. At that time, the only query tool available to the users of the system I was working with was ISQL/w, the precursor to Query Analyzer, so I also used stored procedures to roll out the queries to the users. All the users had to do was log in to the system and execute the stored procedure and they would get all the results they needed.
? Parameterized Queries— After I figured out how basic stored procedure worked and mastered the SELECT statement, the next thing that I did was start to work on parameterized queries. These stored procedures accepted one or two parameters and returned a subset of the information in the tables in which the user was interested. This enabled the users to return only those results that were important to them.

? Encapsulation of Data Modification Statements— Another great use of stored procedures is to encapsulate data modification statements. When you type data modification statements into a query window and execute them,there is a possibility that you will mistype something and cause severe problems in the database. If you encapsulate the data modification statements into a stored procedure that has been adequately tested, you are able to better control the statement and limit the amount of damage that can be done in the statement.

? Maintainability of Application Logic— One very widespread use of stored procedures is to use them as a container for application logic. This way, you can maintain all your company's business rules and logic in a single location,which makes them extremely easy to maintain. If a business rule changes, all you have to do is change the code in the stored procedure, and all users would have the new code.

? Standardization— If you roll all the data access, data modification, and
business logic statements into stored procedures, you are virtually guaranteed that all access to your database will be standardized. That means if a user accesses a particular table, you know exactly what he is doing and how he is doing it.

? Ease of Troubleshooting— This point closely follows the previous point. If you standardize all your database access through a common set of stored procedures, troubleshooting is much easier. This ease is because you have only one place to look to find the problems and, when the problem is fixed,one place to roll the changes to.

? Security— One of the best, but least implemented, uses for stored procedures is as a security measure. If you create a stored procedure that accesses a table, you can revoke access to that table; the only way your users can access that table is through the stored procedure you've created.This is an extremely powerful method for locking down the server andkeeping users from accessing information they aren't supposed to.

? Automation of Administration Tasks— The most fun and most types of stored procedures to write (for me at least) are procedures that assist in the automation of the SQL Server. Like system stored procedures,the core set of procedures installed with SQL Server, these procedures are used to perform low-level system functions and to return information about the server and the objects on the server. I call these procedures utility stored procedures. Later in this book, I present some of the most useful utility stored procedures that I have written for you to use and learn from.

Relational Database Management Systems

SQL Server 2000, the latest and greatest of the SQL Server platforms, is still a relational database management system (RDBMS). All RDBMSs share some qualities.These features are the basics of what make up any RDBMSs, and include thefollowing:

? As its name implies, the purpose of an RDBMS is to manage relational databases. A relational database is a grouping of tables. The tables are broken down into rows, also known as records, and the records are broken down into columns, also known as fields. Without these, you would have nothing to manage.

? All RDBMSs use SQL, or a variation of it, to manipulate the data contained in any of the databases. SQL (correctly pronounced S-Q-L) was developed at IBM in the late 1970s.

? RDBMSs must maintain data integrity. In other words, every relational database needs to ensure that if data in multiple tables is updated, all the updates take place. For example, imagine a banking system that contains two tables: one for your savings account and one for your checking account. You call your bank and ask to transfer $100 from savings to checking. This process involves subtracting $100 from your savings account and adding $100 to your checking account. If the bank loses power after the $100 is deleted from the table, what happens? If there is no data integrity, you have just lost $100. With data integrity, when the server is powered back on, the RDBMS realizes that the subtraction completed but that the addition didn't,and it cancels the whole transaction.

? Most RDBMSs strive to maintain separation between the actual data and the business logic that ensures that the data in the database is maintained in a constant state. In most cases, you will want to try to limit the amount of business functionality that you maintain in the database server.

? Many RDBMSs store data in such a way that redundant data is eliminated through some of type of compression. That doesn't mean data is lost—rather,it means that less storage space is needed.

? All RDBMSs provide some sort of security for the databases they manage.This security is usually at least a two-level process. First, any user who wants to access the system must identify herself with a valid login and password.When she passes this level of authentication, most systems have rules, called permissions, that block a user from accessing data to which she shouldn't have access.

SQL Server in Business Today

SQL Server has made huge inroads into the business market since its inception.The real advances have come since SQL Server 6.5. A huge number of applications in the marketplace have been written to utilize the advantages of SQL Server.A few categories of these systems are as follows:

?Customer Relationship Management (CRM) Software— A number of CRM packages on the market have been designed to take advantage of SQL Server in combination with other Microsoft products such as Exchange and Outlook. CRM products track customer information and all contact that has been made with the customers.

? Data Collection Systems— These applications are designed to capture realtime information and store it away for later processing and summarization.These systems usually require extremely high availability and reliability. To produce these types of results, SQL Server Enterprise Edition used with Windows NT Enterprise Edition in a clustered environment provides a platform with built-in redundancy and support for automatic failover.

?Data Warehouses— A data warehouse stores a large collection of data that can be analyzed for trends that are useful to the company that owns the data.For example, a simple data warehouse could show a company's inventory manager how a specific product has sold during a specific week over the past few years so that when the week in question rolls around, enough of that product is on hand to meet the projected need.Data warehouses were all the rage a few years ago. Support for data
warehouses dwindled because of the large investment it took to create them and their immense size. Smaller data warehouses, known as data marts, have lately begun to reemerge to provide this important information to companies that choose to implement them. SQL Server is a great choice for these types of applications because of built-in OLAP (Online Analytical Processing)support.

? E-Commerce— The surge in popularity of the Internet over the past few years has brought about a new way for companies to buy and sell products.Many companies have chosen SQL Server as the platform they base their ecommerce engines on because of its stability, high availability, and low cost.

SQL Server's History

SQL Server has actually been around for quite a while, in one form or another. SQL Server was originally introduced in 1988. The first version was a joint venture between Sybase and Microsoft, ran only on OS/2, and was a complete flop in themarketplace. In 1993, SQL Server 4.2 for Windows NT Advanced Server 3.1 was
released. This version made some small advances in the marketplace, but still didn't
have what it needed to make it an enterprise-class RDBMS. Microsoft and Sybase went their separate ways in 1994, and shortly afterward Microsoft released SQL Server 6.0. In 1996, SQL Server 6.5 was released. This version of SQL Server succeeded in the marketplace primarily because it had the speed, power, ease-ofuse, and low cost that purchasers and IT staffers were looking for.
In addition to the features that administrators were looking for, part of SQL Server'ssuccess has to do with the direction that the marketplace took around the same time that SQL Server 6.5 was released. For the most part, the market was moving towardfaster and cheaper Intel-based servers running Windows NT Server. This meant that,on abandoning other platforms, when there was a need for an RDBMS, SQL Server
became the natural selection.
SQL Server 7.0, released in early 1999, moved SQL Server into the enterprise database arena. Although previous versions of SQL Server contained large amounts of the original Sybase code, SQL Server 7.0 is said to be 100% Microsoft code.It's even said that Microsoft developers threw a party when the final lines of original code were removed. If SQL Server 7.0 is not a complete rewrite, it is pretty close.The latest version, SQL Server 2000, allows Microsoft to step a little further into the
enterprise database arena. It has a large amount of new features that make it a stronger competitor of the largest, most widely accepted enterprise database—Oracle. SQL Server will probably never completely take over this particular database arena, but it will continue to make strides to do so.

The History of Databases

There probably is a true written account on how databases evolved throughout the last few years. I won't get too far into the specifics here. From a high level, databases have been around for quite some time, just not computerized. Originally, databases were known as ledgers and card catalogs. These were maintained by people and really took a lot of manpower to maintain. When computers came around, it was really a logical progression to move databases to that platform. As processing power and storage increased and overall cost decreased, databases became more prevalent in the business world. Many paper processes became computer- oriented and all the data that would have normally been stored on paper forms in filing cabinets began to be stored in computer databases. Today, almost every business has a database of some sort, whether it be a lower-end Microsoft Access database or a higher-end SQL Server or Oracle database.