| |
| |
| Introduction | |
| |
| |
| |
| Introduction to Developing Stored Procedures | |
| |
| |
| |
| SQL Server and Stored Procedure Background | |
| |
| |
| The History of Databases | |
| |
| |
| SQL Server's History | |
| |
| |
| SQL Server in Business Today | |
| |
| |
| Relational Database Management Systems | |
| |
| |
| The Features of SQL Server 2000 | |
| |
| |
| The Old Staple Functionality | |
| |
| |
| New Functionality | |
| |
| |
| Uses for Stored Procedures | |
| |
| |
| Conclusion | |
| |
| |
| |
| Stored Procedure Camps | |
| |
| |
| What Is Business Logic? | |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Simple Stored Procedures | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Creating Stored Procedures that Modify Data | |
| |
| |
| Verifying the Data to Be Modified | |
| |
| |
| Creating Procedures to Delete Data | |
| |
| |
| Automating Code Creation | |
| |
| |
| Conclusion | |
| |
| |
| |
| Security and Advanced Procedure Development | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| Extended Stored Procedures | |
| |
| |
| How Extended Stored Procedures Work | |
| |
| |
| Writing the Required C Code | |
| |
| |
| Registering an Extended Stored Procedure | |
| |
| |
| Useful System Extended Stored Procedures | |
| |
| |
| Conclusion | |
| |
| |
| |
| 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 | |
| |
| |
| |
| XML and Triggers | |
| |
| |
| |
| SQL Server XML Support | |
| |
| |
| What Is XML? | |
| |
| |
| 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 | |
| |
| |
| |
| Writing Triggers | |
| |
| |
| 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 | |
| |
| |
| |
| Considerations When Using Stored Procedures and Triggers | |
| |
| |
| Problems with Stored Procedures | |
| |
| |
| Blocking | |
| |
| |
| Sequential Numbering | |
| |
| |
| Problems with Triggers | |
| |
| |
| General Performance Issues | |
| |
| |
| Conclusion | |
| |
| |
| Index | |