Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
1st Edition
Published on 18. May 2006
614 pages
978-0-07-149170-9 (ISBN)
Available for download
Description
Create and Use Stored Procedures for Optimal Database PerformanceDevelop complex stored procedures to retrieve, manipulate, update, and delete data. Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET identifies and describes the key concepts, techniques, and best practices you need to master in order to take full advantage of stored procedures using SQL Server's native Transact-SQL and .NET CLR languages. You'll learn to incorporate effective Transact-SQL stored procedures into client or middleware code, and produce CLR methods that will be compiled into CLR stored procedures. This is a must-have resource for all SQL Server 2005 developers. Essential Skills for Database ProfessionalsGroup and execute T-SQL statements using batches, scripts, and transactionsCreate user-defined, system, extended, temporary, global temporary, and remote stored procedures Develop and manage stored procedures using C# and Visual Basic .NETImplement database access using ADO.NETCreate CLR user-defined functions and triggersImplement reliable debugging and error handling techniques and security measuresManage source code in a repository such as Visual SourceSafeCreate stored procedures for web search enginesUse system and extended stored procedures to interact with the SQL Server environment
More details
Other editions
Additional editions
Book
06/2006
3rd Edition
Osborne/McGraw-Hill
€69.40
Shipment within 15-20 days
Content
- Intro
- Copyright
- Dedication
- About the Author
- Contents at a Glance
- Contents
- Acknowledgments
- Introduction
- Chapter 1. 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
- Chapter 2. 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
- Chapter 3. 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
- Chapter 4. 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
- Chapter 5. Composite Transact-SQL Constructs: Batches, Scripts, and Transactions
- Batches
- Using Batches
- Batches and Errors
- DDL Batches
- Self-sufficient Content
- Scripts
- Database Scripting
- Transactions
- Autocommit Transactions
- Explicit Transactions
- Implicit Transactions
- Transaction Processing Architecture
- Nested Transactions
- Named Transactions
- Savepoints
- Locking
- Distributed Transactions
- Typical Transaction-related Problems
- Chapter 6. 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
- Chapter 7. 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
- Chapter 8. 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
- Chapter 9. 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
- Chapter 10. 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
- Chapter 11. 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
- Chapter 12. 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
- Chapter 13. 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
- Chapter 14. 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
- Chapter 15. 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
- Chapter 16. 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
- Chapter 17. 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
- Chapter 18. 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
- Chapter 19. 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
- Chapter 20. 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
- Chapter 21. 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
- Database Mail
- Configuring Database Mail
- Sending E-mails
- Check Status
- Exposing Stored Procedures As Web Services
- Endpoints
- Appendix A. Naming Conventions
- Why Bother?
- Naming Objects and Variables
- Entity Description
- Name Length
- Abbreviations
- Name Formatting
- Suggested Convention
- Variables
- Database Objects
- Triggers
- Stored Procedures
- Appendix B. 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
- Appendix C. Data Types in SQL Server 2005
- Index
