Writing Stored Procedures for Microsoft SQL Server focuses on teaching you not only how to build effective stored procedures, but also how to optimize and streamline them for long term use. Get an understanding of how SQL Server interprets stored procedures verses direct access via SQL queries. This is followed by detailed instructions on creating and maintaining a repository of stored procedures. Once you know how stored procedures are created and built, you are taught how to determine which logic within an application makes sense to be put into this scheme. Additionally, you're provided with guidance on making stored procedures that are portable across applications.
In this chapter, I cover the following:
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 computeroriented and all the data that would have normally been stored on paper forms in filing cabinets began to be stored in computer databases. Today, almostevery 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.
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 the marketplace. 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-of-use, 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's success 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 toward faster and cheaper Intelbased 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.
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:
Introduction.
I. INTRODUCTION TO DEVELOPING STORED PROCEDURES.
1. SQL Server and Stored Procedure Background.
The Old Staple Functionality. New Functionality.
Uses for Stored Procedures. Conclusion.
2. Stored Procedure Camps.
What Is Business Logic? 9. Why Differences Have Come About.
The All Business Logic Camp. Advantages of Business Logic in the Database. Disadvantages of Business Logic in the Database.
The No Business Logic Camp. Advantages of the No Business Logic Camp. Disadvantages of the No Business Logic Camp.
Finding Middle Ground. Conclusion.
3. Processing Stored Procedures.
How SQL Server Creates Stored Procedures. How SQL Server Processes Stored Procedures.
Deferred Name Resolution. Creation of an Execution Plan.
Execution Plans.
Execution Plan Makeup. How SQL Server Chooses Query Plans. Aging and Deallocating Execution Plans. Execution Plan Recompilation.
Viewing and Interpreting Execution Plans. Conclusion.
4. Stored Procedures Rules.
Programming Stored Procedure Rules.
Basic Rules. Referencing Objects in Stored Procedures. Source Code Protection. ODBC Options. Deferred Name Resolution and Query Plan Creation.
Using Naming Conventions.
Microsoft Discrepancies in Naming Convention. Microsoft Identifier Rules. Suggested Naming Conventions.
Special Stored Procedure Naming Conventions. Conclusion.
II. SIMPLE STORED PROCEDURES.
5. Creating and Altering Stored Procedures.
Creating Stored Procedures. SELECT Statements.
Limiting the Number of Columns. Limiting the Number of Rows.
Using Cursors.
Creating Cursors in SQL Server. Using the DECLARE Statement.
CREATE PROCEDURE. The Changing of the Code—Altering Stored Procedures.
The Old Way. The New Way—ALTER PROCEDURE.
Alteration Considerations. Conclusion.
6. Creating More Complex Stored Procedures.
Complex Stored Procedure Creation. Passing Parameters into Stored Procedures. Returning Information to the User.
The RETURN Statement. The PRINT Command. The RAISERROR Function. OUTPUT Parameters.
Conclusion.
7. Creating Stored Procedures that Insert Data.
Inserting Data with the INSERT Statement. Inserting Data into Views. Inserting Data Based on a SELECT Statement. Inserting Data with an EXECUTE Statement. Minimizing Blocking Conditions. Automating the Creation of the Code. Conclusion.
8. Creating Stored Procedures that Modify Data.
Verifying the Data to Be Modified. Creating Procedures to Delete Data. Automating Code Creation. Conclusion.
III. SECURITY AND ADVANCED PROCEDURE DEVELOPMENT.
9. Providing Security for Stored Procedures.
Security Basics. SQL Server Security.
Logins. Users. Roles. Permissions. Permission States. Assigning Permissions. Adding Logins and Users.
Stored Procedures as a Security Mechanism. Application Security. Ownership Chains. Conclusion.
10. Programming Structures in SQL.
Operators.
Assignment Operator. Arithmetic Operators. Comparison Operators. Logical Operators. String Concatenation Operator. Bitwise Operators. Unary Operators.
Looping Structures.
WHILE Loop. GOTO Loop.
Control-of-Flow Statements.
IF ELSE. CASE.
Functions.
String Functions. Date Functions. Mathematical Functions. System Functions.
Conclusion.
11. Writing Utility Stored Procedures.
Defining Utility Stored Procedures. Utility Stored Procedure Code.
Updating Index Statistics. Reindexing a Database. Finding Object Permissions. Finding Database-Level Permissions. The New sp_who. Checking for Blocks. Checking Table Sizes. Numbering Source Code Lines. Retrieving Index Members.
Conclusion.
12. Extended Stored Procedures.
How Extended Stored Procedures Work. Writing the Required C Code. Registering an Extended Stored Procedure. Useful System Extended Stored Procedures. Conclusion.
13. Creating Custom Functions.
New Advances in SQL Server Functions. User-Defined Functions. Functions That Return a Table.
Multi-Statement Tabular Functions. In-Line Functions.
Calling User-Defined Functions. Calling Scalar Functions. Calling Tabular Functions. Calling Built-in Functions.
Function Determinism. User-Defined Function Determinism. Determinism of System Functions.
Conclusion.
IV. XML AND TRIGGERS.
14. SQL Server XML Support.
What Is XML? 9. XML and HTML. Microsoft and XML. XML Fundamentals.
A Basic XML Demonstration.
Using XML with SQL Server and Internet Information Server's Server. Extensions.
Creating a Test Database. Installing the Server Extensions. Using the Registration Tool Snap-in (Regxmlss Utility).
SQL XML.
Creating a URL Query. Canning a Query. Passing Parameters.
The FOR XML Clause.
FOR XML RAW. FOR XML AUTO. FOR XML EXPLICIT.
Formatting Data with XSL.
XSL Versus CSS.
XML and Style Sheets.
Enabling an XML Document for Browsing with Style Sheets.
Putting it All Together.
Creating the XML Template File. Creating the Style Sheet. Creating the HTML Document.
Update Grams.
The Update Gram Standard Format.
Setting Basic Security. Security Configuration for URL Access.
Using IIS Directory Security Settings.
Conclusion.
15. Writing Triggers 297.
How Triggers Are Defined. How Triggers Are Processed.
AFTER Triggers. INSTEAD OF Triggers.
Creating Triggers.
Building an AFTER Trigger. Building INSTEAD OF Triggers. Trigger Restrictions.
Nested Triggers. Problems with Triggers. Conclusion.
16. Considerations When Using Stored Procedures and Triggers.
Problems with Stored Procedures.
Blocking. Sequential Numbering.
Problems with Triggers. General Performance Issues. Conclusion.
Index.
Overview
Writing Stored Procedures for Microsoft SQL Server focuses on teaching you not only how to build effective stored procedures, but also how to optimize and streamline them for long term use. Get an understanding of how SQL Server interprets stored procedures verses direct access via SQL queries. This is followed by detailed instructions on creating and maintaining a repository of stored procedures. Once you know how stored procedures are created and built, you are taught how to determine which logic within an ...