| |
| |
| Acknowledgments | |
| |
| |
| Introduction | |
| |
| |
| |
| The SQL Server 2005 Environment and Tools | |
| |
| |
| SQL Server 2005 Tools | |
| |
| |
| SQL Server Configuration Manager | |
| |
| |
| SQL Server Management Studio | |
| |
| |
| SQLCMD Utility | |
| |
| |
| SQL Server Profiler | |
| |
| |
| The Help Subsystem and SQL Server Books OnLine | |
| |
| |
| Basic Operations in Management Studio | |
| |
| |
| What Are Stored Procedures? | |
| |
| |
| Execution of Stored Procedures in Management Studio | |
| |
| |
| Editing Stored Procedures | |
| |
| |
| Creating Stored Procedures | |
| |
| |
| Editing Stored Procedures in the Query Window | |
| |
| |
| Syntax Errors | |
| |
| |
| Viewing and Editing Tables | |
| |
| |
| Changing Table Structure | |
| |
| |
| |
| Stored Procedure Design Concepts | |
| |
| |
| Anatomy of a Stored Procedure | |
| |
| |
| Composition | |
| |
| |
| Functionality | |
| |
| |
| Syntax | |
| |
| |
| Types of Stored Procedures | |
| |
| |
| Managing Stored Procedures | |
| |
| |
| Listing Stored Procedures | |
| |
| |
| Viewing Code of Stored Procedures | |
| |
| |
| Renaming Stored Procedures | |
| |
| |
| Deleting Stored Procedures | |
| |
| |
| Listing Dependent and Depending Objects | |
| |
| |
| The Role of Stored Procedures in the Development of Database Applications | |
| |
| |
| Enforcement of Data Integrity | |
| |
| |
| Consistent Implementation of Complex Business Rules and Constraints | |
| |
| |
| Modular Design | |
| |
| |
| Maintainability | |
| |
| |
| Reduced Network Traffic | |
| |
| |
| Faster Execution | |
| |
| |
| Enforcement of Security | |
| |
| |
| |
| Basic Transact-SQL Programming Constructs | |
| |
| |
| T-SQL Identifiers | |
| |
| |
| Database Object Qualifiers | |
| |
| |
| Data Types | |
| |
| |
| Character Strings | |
| |
| |
| Unicode Character Strings | |
| |
| |
| Date and Time Data Types | |
| |
| |
| Integer Numbers | |
| |
| |
| Approximate Numbers | |
| |
| |
| Exact Numbers | |
| |
| |
| Monetary Data Types | |
| |
| |
| Binary Data Types | |
| |
| |
| Special Data Types | |
| |
| |
| Transact-SQL User-defined Data Types | |
| |
| |
| Variables | |
| |
| |
| Local Variables | |
| |
| |
| Global Variables | |
| |
| |
| Table Variables | |
| |
| |
| Flow-control Statements | |
| |
| |
| Comments | |
| |
| |
| Statement Blocks: Begin...End | |
| |
| |
| Conditional Execution: The If Statement | |
| |
| |
| Looping: The While Statement | |
| |
| |
| Unconditional Execution: The GoTo Statement | |
| |
| |
| Scheduled Execution: The WaitFor Statement | |
| |
| |
| Cursors | |
| |
| |
| Transact-SQL Cursors | |
| |
| |
| Cursor-related Statements and Functions | |
| |
| |
| Problems with Cursors | |
| |
| |
| The Justified Uses of Cursors | |
| |
| |
| |
| Functions | |
| |
| |
| Using Functions | |
| |
| |
| In Selection and Assignment | |
| |
| |
| As Part of the Selection Criteria | |
| |
| |
| In Expressions | |
| |
| |
| As Check and Default Constraints | |
| |
| |
| Instead of Tables | |
| |
| |
| Types of Functions | |
| |
| |
| Scalar Functions | |
| |
| |
| Rowset Functions | |
| |
| |
| |
| Composite Transact-SQL Constructs: Batches, Scripts, and Transactions | |
| |
| |
| Batches | |
| |
| |
| Using Batches | |
| |
| |
| Batches and Errors | |
| |
| |
| DDL Batches | |
| |
| |
| Self-sufficient Content | |
| |
| |
| Scripts | |
| |
| |
| Database Scripting | |
| |
| |
| Trasactions | |
| |
| |
| Autocommit Transactions | |
| |
| |
| Explicit Transactions | |
| |
| |
| Implicit Transactions | |
| |
| |
| Transaction Processing Architecture | |
| |
| |
| Nested Transactions | |
| |
| |
| Named Transactions | |
| |
| |
| Savepoints | |
| |
| |
| Locking | |
| |
| |
| Distributed Transactions | |
| |
| |
| Typical Transaction-related Problems | |
| |
| |
| |
| Error Handling | |
| |
| |
| Raiserror() | |
| |
| |
| Using Error Handling | |
| |
| |
| Why Bother? | |
| |
| |
| Error Handling Based on @@Error | |
| |
| |
| Error Handling Architecture: Based on @@Error | |
| |
| |
| Try - Catch Statement | |
| |
| |
| What Errors Are Trapped | |
| |
| |
| Functions of the Catch Block | |
| |
| |
| Try - Catch Statement with Explicit Transactions | |
| |
| |
| Deadlocks | |
| |
| |
| Deadlock Retries | |
| |
| |
| Try - Catch Statement Nesting | |
| |
| |
| Error Handling Architecture: With Try - Catch Statements | |
| |
| |
| Xact_Abort | |
| |
| |
| Error Handling Architecture: Based on Set Xact_Abort On | |
| |
| |
| Error Handling Architecture: Xact_Abort + No Transaction Nesting | |
| |
| |
| |
| Special Types of Stored Procedures | |
| |
| |
| User-defined Stored Procedures | |
| |
| |
| System Stored Procedures | |
| |
| |
| CLR Stored Procedures | |
| |
| |
| Extended Stored Procedures | |
| |
| |
| Design of Extended Stored Procedures | |
| |
| |
| Registering the Extended Stored Procedure | |
| |
| |
| Temporary Stored Procedures | |
| |
| |
| Global Temporary Stored Procedures | |
| |
| |
| Remote Stored Procedures | |
| |
| |
| |
| Views | |
| |
| |
| Design of Standard SQL Views | |
| |
| |
| Syntax | |
| |
| |
| Design View in Enterprise Manager | |
| |
| |
| Security | |
| |
| |
| Standard SQL Views in Execution Plans | |
| |
| |
| Limitations of Views | |
| |
| |
| Editing Data Using Views | |
| |
| |
| Dynamic Views | |
| |
| |
| Temporary Views-Common Table Expressions | |
| |
| |
| Limitations of Nonrecursive CTEs | |
| |
| |
| Recursive CTEs | |
| |
| |
| Information_Schema Views | |
| |
| |
| Indexed Views | |
| |
| |
| Indexed View Limitations | |
| |
| |
| Indexed Views in Execution Plans | |
| |
| |
| Nonclustered Indexes on Views | |
| |
| |
| Performance Implications | |
| |
| |
| Partitioned Views | |
| |
| |
| Horizontal and Vertical Partitioning | |
| |
| |
| Distributed Partitioned Views | |
| |
| |
| Execution Plans of Distributed Partitioned Views | |
| |
| |
| Updateable Distributed Partitioned Views | |
| |
| |
| Scalability and Performance of Distributed Systems | |
| |
| |
| A Poor Man's Federated Server | |
| |
| |
| Using SQL Views | |
| |
| |
| Export and Import | |
| |
| |
| Security Implementation | |
| |
| |
| Reduce Complexity | |
| |
| |
| Performance Improvement | |
| |
| |
| |
| Triggers | |
| |
| |
| DML Triggers | |
| |
| |
| Physical Design of After Triggers | |
| |
| |
| Inserted and Deleted Virtual Tables | |
| |
| |
| What Triggers a Trigger? | |
| |
| |
| Full Syntax of After Triggers | |
| |
| |
| Handling Changes on Multiple Records | |
| |
| |
| Nested and Recursive Triggers | |
| |
| |
| After Trigger Restrictions | |
| |
| |
| Instead-of Triggers | |
| |
| |
| Triggers on Views | |
| |
| |
| DML Trigger Order of Execution | |
| |
| |
| DDL Triggers | |
| |
| |
| Scope and Events of DDL Triggers | |
| |
| |
| EventData() | |
| |
| |
| Syntax of DDL Triggers | |
| |
| |
| Managing Triggers | |
| |
| |
| Managing DML Triggers in Management Studio | |
| |
| |
| Managing DDL Triggers from Management Studio | |
| |
| |
| Managing Triggers Using Transact-SQL Statements | |
| |
| |
| Trigger Design Recommendations | |
| |
| |
| Go Out ASAP | |
| |
| |
| Make It Simple | |
| |
| |
| Divide and Conquer | |
| |
| |
| Do Not Use Select and Print Inside a Trigger | |
| |
| |
| Do Not Use Triggers at All | |
| |
| |
| Transaction Management in Triggers | |
| |
| |
| Using Triggers | |
| |
| |
| Cascading Deletes | |
| |
| |
| Aggregates | |
| |
| |
| Enforce Schema Integrity Among Objects on Different Servers or Databases | |
| |
| |
| |
| User-defined Functions | |
| |
| |
| Design of Scalar User-defined Functions | |
| |
| |
| Side Effects | |
| |
| |
| Use of Built-in Functions | |
| |
| |
| Encryption | |
| |
| |
| Schema-binding | |
| |
| |
| Table-valued User-defined Functions | |
| |
| |
| Inline Table-valued User-defined Functions | |
| |
| |
| Managing User-defined Functions in Management Studio | |
| |
| |
| |
| Fundamentals of .NET Programming in SQL Server 2005 | |
| |
| |
| Unmanaged vs. Managed Code | |
| |
| |
| Common Language Runtime (CLR) | |
| |
| |
| CLR Integration in SQL Server 2005 | |
| |
| |
| Pros and Cons | |
| |
| |
| Supported and Not Supported | |
| |
| |
| Minimally Required Namespaces | |
| |
| |
| Development, Deployment, and Usage of CLR Database Objects in Visual Studio 2005 | |
| |
| |
| Development and Deployment of CLR Database Objects Without Visual Studio 2005 | |
| |
| |
| Develop Code | |
| |
| |
| Compile Program | |
| |
| |
| Load Assembly to SQL Server Database | |
| |
| |
| Create SQLCLR Database Objects | |
| |
| |
| Cataloging Assemblies and CLR Database Objects | |
| |
| |
| Cataloging Assemblies | |
| |
| |
| Cataloging Procedures | |
| |
| |
| Version of .NET Framework | |
| |
| |
| |
| Fundamentals of CLR Stored Procedure Development | |
| |
| |
| Structure of a CLR Stored Procedure | |
| |
| |
| Database Access from CLR Procedures | |
| |
| |
| CLR Data Types Mapping | |
| |
| |
| Basic Operations with Stored Procedures | |
| |
| |
| Returning Value | |
| |
| |
| Connection Context | |
| |
| |
| Returning a Result | |
| |
| |
| Returning Custom Records | |
| |
| |
| Parameters in CLR Stored Procedures | |
| |
| |
| Operations with Regular ADO.NET Objects | |
| |
| |
| SqlCommand | |
| |
| |
| SqlDataReader | |
| |
| |
| Managing LOB Parameters | |
| |
| |
| |
| CLR Functions and Triggers | |
| |
| |
| CLR Functions | |
| |
| |
| Scalar-valued CLR Functions | |
| |
| |
| SqlFunction Attribute | |
| |
| |
| Table-valued CLR Functions | |
| |
| |
| CLR Triggers | |
| |
| |
| Supported Features | |
| |
| |
| Creation of DML Triggers | |
| |
| |
| Creation of DDL Triggers | |
| |
| |
| |
| CLR Database Objects Advanced Topics | |
| |
| |
| CLR User-defined Types | |
| |
| |
| Structure of Managed UDT | |
| |
| |
| Deploying CLR UDT Without Visual Studio 2005 | |
| |
| |
| Using CLR User-defined Types | |
| |
| |
| Cross-database CLR User-defined Types | |
| |
| |
| To UDT or Not to UDT | |
| |
| |
| User-defined Aggregate CLR Functions | |
| |
| |
| Structure of Managed Aggregates | |
| |
| |
| Transactions in CLR Code | |
| |
| |
| Using TransactionScope Class | |
| |
| |
| Transaction Options | |
| |
| |
| Distributed Transactions | |
| |
| |
| Explicit Transactions | |
| |
| |
| Benefits of New Transaction Paradigm | |
| |
| |
| To CLR or Not to CLR (Design and Performance Considerations) | |
| |
| |
| Don't Say I Didn't Warn You | |
| |
| |
| |
| Advanced Stored Procedure Programming | |
| |
| |
| Dynamically Constructed Queries | |
| |
| |
| Executing a String | |
| |
| |
| Query by Form | |
| |
| |
| Data Script Generator | |
| |
| |
| Using the sp_executesql Stored Procedure | |
| |
| |
| Security Implications | |
| |
| |
| Optimistic Locking Using timestamp Values | |
| |
| |
| timestamp | |
| |
| |
| TSEqual() Function | |
| |
| |
| Full-text Search and Indexes | |
| |
| |
| Nested Stored Procedures | |
| |
| |
| Using Temporary Tables to Pass a Recordset to a Nested Stored Procedure | |
| |
| |
| Using a Cursor to Pass a Recordset to a Nested Stored Procedure | |
| |
| |
| How to Process the Result Set of a Stored Procedure | |
| |
| |
| Using Identity Values | |
| |
| |
| A Standard Problem and Solution | |
| |
| |
| Identity Values and Triggers | |
| |
| |
| GUIDs | |
| |
| |
| A While Loop with Min() or Max() Functions | |
| |
| |
| Looping with sp_MSForEachTable and sp_MSForEachDb | |
| |
| |
| Property Management | |
| |
| |
| |
| Debugging | |
| |
| |
| What Is a "Bug"? | |
| |
| |
| The Debugging Process | |
| |
| |
| Identification | |
| |
| |
| Resolution | |
| |
| |
| Debugging Tools and Techniques | |
| |
| |
| Transact-SQL Debugger in Visual Studio 2005 | |
| |
| |
| Debugging CLR Database Objects | |
| |
| |
| Poor Man's Debugger | |
| |
| |
| Execution in the Production Environment | |
| |
| |
| Nested Stored Procedures | |
| |
| |
| Output Clause | |
| |
| |
| SQL Profiler | |
| |
| |
| Using SQL Profiler | |
| |
| |
| Typical Errors | |
| |
| |
| Handling Null | |
| |
| |
| Assignment of Variable from the Result Set | |
| |
| |
| No Records Affected | |
| |
| |
| Wrong Size or Data Type | |
| |
| |
| Default Length | |
| |
| |
| Rollback of Triggers | |
| |
| |
| Warnings and Lower-priority Errors | |
| |
| |
| Return Codes vs. Raiserror | |
| |
| |
| Nested Comments | |
| |
| |
| Deferred Name Resolution | |
| |
| |
| Cursors | |
| |
| |
| Overconfidence | |
| |
| |
| |
| Source Code Management | |
| |
| |
| Introduction to Microsoft Visual SourceSafe | |
| |
| |
| Administering the Visual SourceSafe Database | |
| |
| |
| Adding Database Objects to Visual SourceSafe in Visual Studio .NET | |
| |
| |
| Managing Create Scripts in Visual Studio .NET | |
| |
| |
| Visual SourceSafe Explorer | |
| |
| |
| History | |
| |
| |
| Labels and Versions | |
| |
| |
| Adding Database Objects to Visual SourceSafe: Traditional Approach | |
| |
| |
| Creating Scripts Using Script Wizard | |
| |
| |
| Creating Scripts Using TbDbScript | |
| |
| |
| Putting Scripts to Visual SourceSafe Using TbDir2Vss.vbs | |
| |
| |
| |
| Database Deployment | |
| |
| |
| Deployment of a Complete Database: Traditional Approach | |
| |
| |
| Detach and Reattach the Database in Transact-SQL | |
| |
| |
| Attach and Detach in Management Studio | |
| |
| |
| Backup and Restore | |
| |
| |
| Potential Problems | |
| |
| |
| Deployment of Individual Objects | |
| |
| |
| Deployment Scripts: Traditional Approach | |
| |
| |
| Scripting Data: Traditional Approach | |
| |
| |
| Scripting Data in Visual Studio .NET | |
| |
| |
| Deploying Scripts: Traditional Approach | |
| |
| |
| Deploying Create Scripts in Visual Studio .NET | |
| |
| |
| Incremental Build: Traditional Approach | |
| |
| |
| Incremental Build in Visual Studio .NET | |
| |
| |
| |
| Security | |
| |
| |
| Security Architecture | |
| |
| |
| Authentication and Authorization | |
| |
| |
| Principals | |
| |
| |
| Securables | |
| |
| |
| Access Levels | |
| |
| |
| Roles | |
| |
| |
| Ownership Chains | |
| |
| |
| Cross-database Ownership Chains | |
| |
| |
| Switching of Execution Context | |
| |
| |
| Implementing Security | |
| |
| |
| Selection of Authentication Mode | |
| |
| |
| Managing Logins | |
| |
| |
| Granting Database Access | |
| |
| |
| Assigning Permissions | |
| |
| |
| Synchronization of Login and Usernames | |
| |
| |
| Managing Application Security Using Stored Procedures, User-defined Functions, and Views | |
| |
| |
| Managing Application Security Using a Proxy User | |
| |
| |
| Managing Application Security Using Application Roles | |
| |
| |
| |
| Stored Procedures for Web Search Engines | |
| |
| |
| Characteristics of the Environment | |
| |
| |
| A Simple Solution... | |
| |
| |
| ... and Its Disadvantages | |
| |
| |
| Available Solutions | |
| |
| |
| Result Splitting | |
| |
| |
| Quick Queries | |
| |
| |
| Advanced Queries | |
| |
| |
| Fancy New Solution: Row Versioning | |
| |
| |
| |
| Interaction with the SQL Server Environment | |
| |
| |
| Running Programs | |
| |
| |
| Running Windows Script Files | |
| |
| |
| Execution of OLE Automation/COM Objects | |
| |
| |
| Data Type Conversion | |
| |
| |
| Running SQL Server 2005 Integration Services (SSIS) Packages | |
| |
| |
| Running DTS Packages | |
| |
| |
| Running/Looping Through DTS Packages | |
| |
| |
| Interacting with the NT Registry | |
| |
| |
| xp_regread | |
| |
| |
| xp_regwrite | |
| |
| |
| Jobs | |
| |
| |
| Administration of Jobs | |
| |
| |
| An Alternative to Job Scheduler | |
| |
| |
| Stored Procedures for Maintaining Jobs | |
| |
| |
| Operators and Alerts | |
| |
| |
| E-mail | |
| |
| |
| Database Mail | |
| |
| |
| Configuring Database Mail | |
| |
| |
| Sending E-mails | |
| |
| |
| Check Status | |
| |
| |
| Exposing Stored Procedures As Web Services | |
| |
| |
| Endpoints | |
| |
| |
| |
| Naming Conventions | |
| |
| |
| Why Bother? | |
| |
| |
| Naming Objects and Variables | |
| |
| |
| Entity Description | |
| |
| |
| Name Length | |
| |
| |
| Abbreviations | |
| |
| |
| Name Formatting | |
| |
| |
| Suggested Convention | |
| |
| |
| Variables | |
| |
| |
| Database Objects | |
| |
| |
| Triggers | |
| |
| |
| Stored Procedures | |
| |
| |
| |
| Stored Procedure Compilation, Storage, and Reuse | |
| |
| |
| The Compilation and Execution Process | |
| |
| |
| Parsing | |
| |
| |
| Get Statistics | |
| |
| |
| Compilation | |
| |
| |
| Execution | |
| |
| |
| Reuse of Execution Plans | |
| |
| |
| Levels of Execution Plans | |
| |
| |
| Caching Levels | |
| |
| |
| Reuse of Query Execution Plans | |
| |
| |
| Parameterized Queries | |
| |
| |
| Autoparameterization | |
| |
| |
| Reuse of Stored Procedure Execution Plans | |
| |
| |
| Recompiling Stored Procedures | |
| |
| |
| Storing Stored Procedures | |
| |
| |
| |
| Data Types in SQL Server 2005 | |
| |
| |
| Index | |