Microsoft SQL Server 2005 stored procedure programming in T-SQL & .NET

Publisher's Note: Products purchased from Third Party sellers are not guaranteed by the publisher for quality, authenticity, or access to any online entitlements included with the product.

Create and Use Stored Procedures for Optimal Database Performance

Develop 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 Professionals

Group and execute T-SQL statements using batches, scripts, and transactions Create user-defined, system, extended, temporary, global temporary, and remote stored procedures Develop and manage stored procedures using C# and Visual Basic .NET Implement database access using ADO.NET Create CLR user-defined functions and triggers Implement reliable debugging and error handling techniques and security measures Manage source code in a repository such as Visual SourceSafe Create stored procedures for web search engines Use system and extended stored procedures to interact with the SQL Server environment

Author Notes

Dejan Sunderić, MCDBA, is the principal consultant at Trigon Blue, Inc., and the president of the Toronto SQL Server User Group. He specializes in database and application development on the SQL Server platform.

Acknowledgments	p. xix
Introduction	p. xxi
Chapter 1 The SQL Server 2005 Environment and Tools	p. 1
SQL Server 2005 Tools	p. 2
SQL Server Configuration Manager	p. 3
SQL Server Management Studio	p. 6
SQLCMD Utility	p. 11
SQL Server Profiler	p. 12
The Help Subsystem and SQL Server Books OnLine	p. 12
Basic Operations in Management Studio	p. 13
What Are Stored Procedures?	p. 14
Execution of Stored Procedures in Management Studio	p. 14
Editing Stored Procedures	p. 17
Creating Stored Procedures	p. 18
Editing Stored Procedures in the Query Window	p. 19
Syntax Errors	p. 22
Viewing and Editing Tables	p. 23
Changing Table Structure	p. 26
Chapter 2 Stored Procedure Design Concepts	p. 27
Anatomy of a Stored Procedure	p. 28
Composition	p. 28
Functionality	p. 31
Syntax	p. 38
Types of Stored Procedures	p. 40
Managing Stored Procedures	p. 41
Listing Stored Procedures	p. 41
Viewing Code of Stored Procedures	p. 43
Renaming Stored Procedures	p. 44
Deleting Stored Procedures	p. 45
Listing Dependent and Depending Objects	p. 45
The Role of Stored Procedures in the Development of Database Applications	p. 47
Enforcement of Data Integrity	p. 47
Consistent Implementation of Complex Business Rules and Constraints	p. 48
Modular Design	p. 48
Maintainability	p. 48
Reduced Network Traffic	p. 49
Faster Execution	p. 49
Enforcement of Security	p. 49
Chapter 3 Basic Transact-SQL Programming Constructs	p. 51
T-SQL Identifiers	p. 52
Database Object Qualifiers	p. 54
Data Types	p. 55
Character Strings	p. 57
Unicode Character Strings	p. 58
Date and Time Data Types	p. 59
Integer Numbers	p. 60
Approximate Numbers	p. 61
Exact Numbers	p. 62
Monetary Data Types	p. 62
Binary Data Types	p. 63
Special Data Types	p. 64
Transact-SQL User-defined Data Types	p. 67
Variables	p. 68
Local Variables	p. 69
Global Variables	p. 73
Table Variables	p. 76
Flow-control Statements	p. 77
Comments	p. 78
Statement Blocks: Begin...End	p. 80
Conditional Execution: The If Statement	p. 81
Looping: The While Statement	p. 86
Unconditional Execution: The GoTo Statement	p. 89
Scheduled Execution: The WaitFor Statement	p. 90
Cursors	p. 91
Transact-SQL Cursors	p. 92
Cursor-related Statements and Functions	p. 95
Problems with Cursors	p. 97
The Justified Uses of Cursors	p. 97
Chapter 4 Functions	p. 101
Using Functions	p. 102
In Selection and Assignment	p. 102
As Part of the Selection Criteria	p. 103
In Expressions	p. 103
As Check and Default Constraints	p. 104
Instead of Tables	p. 104
Types of Functions	p. 105
Scalar Functions	p. 106
Rowset Functions	p. 136
Chapter 5 Composite Transact-SQL Constructs: Batches, Scripts, and Transactions	p. 143
Batches	p. 144
Using Batches	p. 145
Batches and Errors	p. 146
DDL Batches	p. 150
Self-sufficient Content	p. 151
Scripts	p. 152
Database Scripting	p. 152
Trasactions	p. 155
Autocommit Transactions	p. 155
Explicit Transactions	p. 157
Implicit Transactions	p. 158
Transaction Processing Architecture	p. 159
Nested Transactions	p. 160
Named Transactions	p. 165
Savepoints	p. 165
Locking	p. 169
Distributed Transactions	p. 173
Typical Transaction-related Problems	p. 175
Chapter 6 Error Handling	p. 179
Raiserror()	p. 180
Using Error Handling	p. 183
Why Bother?	p. 183
Error Handling Based on @@Error	p. 184
Error Handling Architecture: Based on @@Error	p. 186
Try - Catch Statement	p. 186
What Errors Are Trapped	p. 187
Functions of the Catch Block	p. 190
Try - Catch Statement with Explicit Transactions	p. 191
Deadlocks	p. 194
Deadlock Retries	p. 194
Try - Catch Statement Nesting	p. 198
Error Handling Architecture: With Try - Catch Statements	p. 199
Xact_Abort	p. 199
Error Handling Architecture: Based on Set Xact_Abort On	p. 201
Error Handling Architecture: Xact_Abort + No Transaction Nesting	p. 205
Chapter 7 Special Types of Stored Procedures	p. 213
User-defined Stored Procedures	p. 214
System Stored Procedures	p. 214
CLR Stored Procedures	p. 216
Extended Stored Procedures	p. 216
Design of Extended Stored Procedures	p. 217
Registering the Extended Stored Procedure	p. 222
Temporary Stored Procedures	p. 223
Global Temporary Stored Procedures	p. 224
Remote Stored Procedures	p. 225
Chapter 8 Views	p. 227
Design of Standard SQL Views	p. 228
Syntax	p. 229
Design View in Enterprise Manager	p. 230
Security	p. 230
Standard SQL Views in Execution Plans	p. 231
Limitations of Views	p. 232
Editing Data Using Views	p. 233
Dynamic Views	p. 234
Temporary Views-Common Table Expressions	p. 234
Limitations of Nonrecursive CTEs	p. 235
Recursive CTEs	p. 236
Information_Schema Views	p. 237
Indexed Views	p. 239
Indexed View Limitations	p. 239
Indexed Views in Execution Plans	p. 240
Nonclustered Indexes on Views	p. 241
Performance Implications	p. 241
Partitioned Views	p. 241
Horizontal and Vertical Partitioning	p. 241
Distributed Partitioned Views	p. 244
Execution Plans of Distributed Partitioned Views	p. 248
Updateable Distributed Partitioned Views	p. 253
Scalability and Performance of Distributed Systems	p. 255
A Poor Man's Federated Server	p. 255
Using SQL Views	p. 256
Export and Import	p. 256
Security Implementation	p. 256
Reduce Complexity	p. 257
Performance Improvement	p. 259
Chapter 9 Triggers	p. 261
DML Triggers	p. 262
Physical Design of After Triggers	p. 262
Inserted and Deleted Virtual Tables	p. 264
What Triggers a Trigger?	p. 266
Full Syntax of After Triggers	p. 267
Handling Changes on Multiple Records	p. 270
Nested and Recursive Triggers	p. 272
After Trigger Restrictions	p. 272
Instead-of Triggers	p. 274
Triggers on Views	p. 275
DML Trigger Order of Execution	p. 278
DDL Triggers	p. 279
Scope and Events of DDL Triggers	p. 280
EventData()	p. 281
Syntax of DDL Triggers	p. 283
Managing Triggers	p. 283
Managing DML Triggers in Management Studio	p. 283
Managing DDL Triggers from Management Studio	p. 284
Managing Triggers Using Transact-SQL Statements	p. 284
Trigger Design Recommendations	p. 289
Go Out ASAP	p. 289
Make It Simple	p. 290
Divide and Conquer	p. 290
Do Not Use Select and Print Inside a Trigger	p. 290
Do Not Use Triggers at All	p. 291
Transaction Management in Triggers	p. 291
Using Triggers	p. 292
Cascading Deletes	p. 294
Aggregates	p. 296
Enforce Schema Integrity Among Objects on Different Servers or Databases	p. 299
Chapter 10 User-defined Functions	p. 303
Design of Scalar User-defined Functions	p. 304
Side Effects	p. 306
Use of Built-in Functions	p. 306
Encryption	p. 307
Schema-binding	p. 307
Table-valued User-defined Functions	p. 308
Inline Table-valued User-defined Functions	p. 311
Managing User-defined Functions in Management Studio	p. 313
Chapter 11 Fundamentals of .NET Programming in SQL Server 2005	p. 315
Unmanaged vs. Managed Code	p. 316
Common Language Runtime (CLR)	p. 316
CLR Integration in SQL Server 2005	p. 318
Pros and Cons	p. 318
Supported and Not Supported	p. 319
Minimally Required Namespaces	p. 320
Development, Deployment, and Usage of CLR Database Objects in Visual Studio 2005	p. 320
Development and Deployment of CLR Database Objects Without Visual Studio 2005	p. 324
Develop Code	p. 325
Compile Program	p. 326
Load Assembly to SQL Server Database	p. 328
Create SQLCLR Database Objects	p. 329
Cataloging Assemblies and CLR Database Objects	p. 330
Cataloging Assemblies	p. 330
Cataloging Procedures	p. 332
Version of .NET Framework	p. 332
Chapter 12 Fundamentals of CLR Stored Procedure Development	p. 335
Structure of a CLR Stored Procedure	p. 336
Database Access from CLR Procedures	p. 337
CLR Data Types Mapping	p. 338
Basic Operations with Stored Procedures	p. 339
Returning Value	p. 339
Connection Context	p. 340
Returning a Result	p. 341
Returning Custom Records	p. 343
Parameters in CLR Stored Procedures	p. 346
Operations with Regular ADO.NET Objects	p. 346
SqlCommand	p. 346
SqlDataReader	p. 348
Managing LOB Parameters	p. 349
Chapter 13 CLR Functions and Triggers	p. 355
CLR Functions	p. 356
Scalar-valued CLR Functions	p. 356
SqlFunction Attribute	p. 359
Table-valued CLR Functions	p. 360
CLR Triggers	p. 372
Supported Features	p. 373
Creation of DML Triggers	p. 373
Creation of DDL Triggers	p. 379
Chapter 14 CLR Database Objects Advanced Topics	p. 385
CLR User-defined Types	p. 386
Structure of Managed UDT	p. 386
Deploying CLR UDT Without Visual Studio 2005	p. 395
Using CLR User-defined Types	p. 395
Cross-database CLR User-defined Types	p. 403
To UDT or Not to UDT	p. 403
User-defined Aggregate CLR Functions	p. 404
Structure of Managed Aggregates	p. 404
Transactions in CLR Code	p. 416
Using TransactionScope Class	p. 417
Transaction Options	p. 419
Distributed Transactions	p. 420
Explicit Transactions	p. 423
Benefits of New Transaction Paradigm	p. 424
To CLR or Not to CLR (Design and Performance Considerations)	p. 425
Don't Say I Didn't Warn You	p. 426
Chapter 15 Advanced Stored Procedure Programming	p. 427
Dynamically Constructed Queries	p. 428
Executing a String	p. 428
Query by Form	p. 430
Data Script Generator	p. 432
Using the sp_executesql Stored Procedure	p. 437
Security Implications	p. 439
Optimistic Locking Using timestamp Values	p. 443
timestamp	p. 444
TSEqual() Function	p. 446
Full-text Search and Indexes	p. 446
Nested Stored Procedures	p. 448
Using Temporary Tables to Pass a Recordset to a Nested Stored Procedure	p. 449
Using a Cursor to Pass a Recordset to a Nested Stored Procedure	p. 451
How to Process the Result Set of a Stored Procedure	p. 453
Using Identity Values	p. 459
A Standard Problem and Solution	p. 459
Identity Values and Triggers	p. 460
GUIDs	p. 462
A While Loop with Min() or Max() Functions	p. 464
Looping with sp_MSForEachTable and sp_MSForEachDb	p. 466
Property Management	p. 467
Chapter 16 Debugging	p. 471
What Is a "Bug"?	p. 472
The Debugging Process	p. 472
Identification	p. 473
Resolution	p. 474
Debugging Tools and Techniques	p. 475
Transact-SQL Debugger in Visual Studio 2005	p. 476
Debugging CLR Database Objects	p. 480
Poor Man's Debugger	p. 484
Execution in the Production Environment	p. 488
Nested Stored Procedures	p. 488
Output Clause	p. 488
SQL Profiler	p. 490
Using SQL Profiler	p. 490
Typical Errors	p. 494
Handling Null	p. 495
Assignment of Variable from the Result Set	p. 495
No Records Affected	p. 496
Wrong Size or Data Type	p. 496
Default Length	p. 496
Rollback of Triggers	p. 497
Warnings and Lower-priority Errors	p. 497
Return Codes vs. Raiserror	p. 498
Nested Comments	p. 498
Deferred Name Resolution	p. 498
Cursors	p. 499
Overconfidence	p. 499
Chapter 17 Source Code Management	p. 501
Introduction to Microsoft Visual SourceSafe	p. 502
Administering the Visual SourceSafe Database	p. 504
Adding Database Objects to Visual SourceSafe in Visual Studio .NET	p. 505
Managing Create Scripts in Visual Studio .NET	p. 507
Visual SourceSafe Explorer	p. 509
History	p. 510
Labels and Versions	p. 511
Adding Database Objects to Visual SourceSafe: Traditional Approach	p. 513
Creating Scripts Using Script Wizard	p. 513
Creating Scripts Using TbDbScript	p. 516
Putting Scripts to Visual SourceSafe Using TbDir2Vss.vbs	p. 517
Chapter 18 Database Deployment	p. 519
Deployment of a Complete Database: Traditional Approach	p. 521
Detach and Reattach the Database in Transact-SQL	p. 521
Attach and Detach in Management Studio	p. 522
Backup and Restore	p. 523
Potential Problems	p. 523
Deployment of Individual Objects	p. 525
Deployment Scripts: Traditional Approach	p. 525
Scripting Data: Traditional Approach	p. 525
Scripting Data in Visual Studio .NET	p. 526
Deploying Scripts: Traditional Approach	p. 527
Deploying Create Scripts in Visual Studio .NET	p. 535
Incremental Build: Traditional Approach	p. 537
Incremental Build in Visual Studio .NET	p. 538
Chapter 19 Security	p. 539
Security Architecture	p. 540
Authentication and Authorization	p. 540
Principals	p. 540
Securables	p. 541
Access Levels	p. 542
Roles	p. 546
Ownership Chains	p. 549
Cross-database Ownership Chains	p. 549
Switching of Execution Context	p. 550
Implementing Security	p. 551
Selection of Authentication Mode	p. 551
Managing Logins	p. 552
Granting Database Access	p. 554
Assigning Permissions	p. 556
Synchronization of Login and Usernames	p. 559
Managing Application Security Using Stored Procedures, User-defined Functions, and Views	p. 562
Managing Application Security Using a Proxy User	p. 565
Managing Application Security Using Application Roles	p. 565
Chapter 20 Stored Procedures for Web Search Engines	p. 567
Characteristics of the Environment	p. 568
A Simple Solution...	p. 568
... and Its Disadvantages	p. 570
Available Solutions	p. 571
Result Splitting	p. 572
Quick Queries	p. 581
Advanced Queries	p. 585
Fancy New Solution: Row Versioning	p. 589
Chapter 21 Interaction with the SQL Server Environment	p. 597
Running Programs	p. 598
Running Windows Script Files	p. 599
Execution of OLE Automation/COM Objects	p. 600
Data Type Conversion	p. 603
Running SQL Server 2005 Integration Services (SSIS) Packages	p. 604
Running DTS Packages	p. 604
Running/Looping Through DTS Packages	p. 604
Interacting with the NT Registry	p. 606
xp_regread	p. 607
xp_regwrite	p. 608
Jobs	p. 608
Administration of Jobs	p. 608
An Alternative to Job Scheduler	p. 614
Stored Procedures for Maintaining Jobs	p. 614
Operators and Alerts	p. 617
E-mail	p. 618
Database Mail	p. 619
Configuring Database Mail	p. 619
Sending E-mails	p. 621
Check Status	p. 622
Exposing Stored Procedures As Web Services	p. 622
Endpoints	p. 623
Appendix A Naming Conventions	p. 625
Why Bother?	p. 626
Naming Objects and Variables	p. 627
Entity Description	p. 627
Name Length	p. 628
Abbreviations	p. 629
Name Formatting	p. 630
Suggested Convention	p. 630
Variables	p. 630
Database Objects	p. 632
Triggers	p. 633
Stored Procedures	p. 634
Appendix B Stored Procedure Compilation, Storage, and Reuse	p. 637
The Compilation and Execution Process	p. 638
Parsing	p. 638
Get Statistics	p. 638
Compilation	p. 639
Execution	p. 639
Reuse of Execution Plans	p. 639
Levels of Execution Plans	p. 640
Caching Levels	p. 640
Reuse of Query Execution Plans	p. 641
Parameterized Queries	p. 641
Autoparameterization	p. 641
Reuse of Stored Procedure Execution Plans	p. 644
Recompiling Stored Procedures	p. 646
Storing Stored Procedures	p. 648
Appendix C Data Types in SQL Server 2005	p. 651
Index	p. 655

Available:*

On Order

Summary

Summary

Author Notes

Table of Contents