A Developer's Guide to SQL Server 2005
Published on 11. May 2006
Book
Paperback/Softback
1088 pages
978-0-321-38218-4 (ISBN)
Description
"I come from a T-SQL background, so when I first laid my eyes on SQL Server 2005, I was shocked--and then, I was scared! I didn't have a CLR or XML background and suddenly had an urgent need to learn it. SQL Server 2005 is too big of a release to learn from the books online. Fortunately, now there is a book for developers who need to go from SQL Server 2000 to SQL Server 2005 and to do it as painlessly as possible. Basically, it's one-stop shopping for serious developers who have to get up to speed quickly. I'll keep this one on my desk--not on my bookshelf. Well done, Bob and Dan!"
--Dr. Tom Moreau
SQL Server MVP and Monthly Columnist
SQL Server Professional, Brockman Moreau Consulting Inc.
"A SQL book truly for developers, from two authorities on the subject. I'll be turning to this book first when I need to understand a component of SQL Server 2005."
--Matt Milner
Instructor
Pluralsight
"An excellent book for those of us who need to get up to speed on what's new in SQL Server 2005. The authors made sure this book includes the final information for the release version of the product. Most other books out now are based on beta versions. It covers key areas from XML and SQLCLR to Notification Services. Although the wide variety of information is great, my favorite part was the advice given on when to use what, and how performance is affected."
--Laura Blood
Senior Software Developer
Blue Note Computing, Inc.
"SQL Server 2005 is a massive release with a large number of new features. Many of these features were designed to make SQL Server a great application development platform. This book provides comprehensive information about the SQL Server features of most interest to application developers. The lucid text and wealth of examples will give a developer a clear understanding of how to use SQL Server 2005 to a whole new class of database applications. It should be on every SQL Server developer's bookshelf."
--Roger Wolter
Solutions Architect
Microsoft Corporation
"While there will be a lot of good books on SQL Server 2005 development, when people refer to the 'bible,' they'll be talking about this book."
--Dr. Greg Low
Senior Consultant
Readify Pty Ltd
"SQL Server 2005 is loaded with new features and getting a good overview is essential to understand how you can benefit from SQL Server 2005's features as a developer. Bob and Dan's book goes beyond enumerating the new SQL Server 2005 features, and will provide you with lots of good examples. They did a good job striking a balance between overview and substance."
--Michiel Wories
Senior Program Manager, SQL Server
Microsoft Corporation
Few technologies have been as eagerly anticipated as Microsoft SQL Server 2005. Now, two SQL Server insiders deliver the definitive hands-on guide--accurate, comprehensive, and packed with examples. A Developer's Guide to SQL Server 2005 starts where Microsoft's documentation, white papers, and Web articles leave off, showing developers how to take full advantage of SQL Server 2005's key innovations. It draws on exceptional cooperation from Microsoft's SQL Server developers and the authors' extensive access to SQL Server 2005 since its earliest alpha releases.
You'll find practical explanations of the new SQL Server 2005 data model, built-in .NET hosting, improved programmability, SQL:1999 compliance, and much more. Virtually every key concept is illuminated via sample code that has been fully updated for and tested with the shipping version of the product.
Key coverage includes
Using SQL Server 2005 as a .NET runtime host: extending the server while enhancing security, reliability, and performance
Writing procedures, functions, triggers, and types in .NET languages
Exploiting enhancements to T-SQL for robust error-handling, efficient queries, and improved syntax
Effectively using the XML data type and XML queries
Implementing native SQL Server 2005 Web Services
Writing efficient, robust clients for SQL Server 2005 using ADO.NET, classic ADO, and other APIs
Taking full advantage of user-defined types (UDTs), query notifications, promotable transactions, and multiple active result sets (MARS)
Using SQL Management Objects (SMO), SQL Service Broker, and SQL Server Notification Services to build integrated applications
--Dr. Tom Moreau
SQL Server MVP and Monthly Columnist
SQL Server Professional, Brockman Moreau Consulting Inc.
"A SQL book truly for developers, from two authorities on the subject. I'll be turning to this book first when I need to understand a component of SQL Server 2005."
--Matt Milner
Instructor
Pluralsight
"An excellent book for those of us who need to get up to speed on what's new in SQL Server 2005. The authors made sure this book includes the final information for the release version of the product. Most other books out now are based on beta versions. It covers key areas from XML and SQLCLR to Notification Services. Although the wide variety of information is great, my favorite part was the advice given on when to use what, and how performance is affected."
--Laura Blood
Senior Software Developer
Blue Note Computing, Inc.
"SQL Server 2005 is a massive release with a large number of new features. Many of these features were designed to make SQL Server a great application development platform. This book provides comprehensive information about the SQL Server features of most interest to application developers. The lucid text and wealth of examples will give a developer a clear understanding of how to use SQL Server 2005 to a whole new class of database applications. It should be on every SQL Server developer's bookshelf."
--Roger Wolter
Solutions Architect
Microsoft Corporation
"While there will be a lot of good books on SQL Server 2005 development, when people refer to the 'bible,' they'll be talking about this book."
--Dr. Greg Low
Senior Consultant
Readify Pty Ltd
"SQL Server 2005 is loaded with new features and getting a good overview is essential to understand how you can benefit from SQL Server 2005's features as a developer. Bob and Dan's book goes beyond enumerating the new SQL Server 2005 features, and will provide you with lots of good examples. They did a good job striking a balance between overview and substance."
--Michiel Wories
Senior Program Manager, SQL Server
Microsoft Corporation
Few technologies have been as eagerly anticipated as Microsoft SQL Server 2005. Now, two SQL Server insiders deliver the definitive hands-on guide--accurate, comprehensive, and packed with examples. A Developer's Guide to SQL Server 2005 starts where Microsoft's documentation, white papers, and Web articles leave off, showing developers how to take full advantage of SQL Server 2005's key innovations. It draws on exceptional cooperation from Microsoft's SQL Server developers and the authors' extensive access to SQL Server 2005 since its earliest alpha releases.
You'll find practical explanations of the new SQL Server 2005 data model, built-in .NET hosting, improved programmability, SQL:1999 compliance, and much more. Virtually every key concept is illuminated via sample code that has been fully updated for and tested with the shipping version of the product.
Key coverage includes
Using SQL Server 2005 as a .NET runtime host: extending the server while enhancing security, reliability, and performance
Writing procedures, functions, triggers, and types in .NET languages
Exploiting enhancements to T-SQL for robust error-handling, efficient queries, and improved syntax
Effectively using the XML data type and XML queries
Implementing native SQL Server 2005 Web Services
Writing efficient, robust clients for SQL Server 2005 using ADO.NET, classic ADO, and other APIs
Taking full advantage of user-defined types (UDTs), query notifications, promotable transactions, and multiple active result sets (MARS)
Using SQL Management Objects (SMO), SQL Service Broker, and SQL Server Notification Services to build integrated applications
More details
Language
English
Place of publication
New Jersey
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
Dimensions
Height: 179 mm
Width: 236 mm
Thickness: 49 mm
Weight
1515 gr
ISBN-13
978-0-321-38218-4 (9780321382184)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Persons
Bob Beauchemin is a database-centric application practitioner and architect, DBA, instructor, course author, and writer. He's Director of Developer Skills at SQLskills (www.sqskills.com), and teaches his SQL Server 2005 courses around the world. Bob has written extensively on SQL Server and other databases, database security, ADO.NET, and OLE DB.
Dan Sullivan runs his own consulting company, does training for Pluralsight (www.pluralsight.com), and has worked with SQL Server since it was first distributed by Microsoft and ran on OS/2. Dan speaks and writes widely on SQL Server.
Dan Sullivan runs his own consulting company, does training for Pluralsight (www.pluralsight.com), and has worked with SQL Server since it was first distributed by Microsoft and ran on OS/2. Dan speaks and writes widely on SQL Server.
Content
Figures xxvTables xxxixForeword by Roger Wolter xliiiForeword by Gert E. R. Drapers xlvPreface xlixAcknowledgments lvAbout the Authors lviiChapter 1: Introduction 1The .NET Framework and the Microsoft Platform 1
The .NET Framework's Effects on SQL Server 3
The SQL:1999 Standard: Extending the Relational Model 8
User-Defined Types and SQL Server 11
XML: Data and Document Storage 14
Web Services: XML As a Marshaling Format 20
Client Access . . . And Then There Are Clients 22
Extending SQL Server into the Platform: Service Broker and Notification Services 24
Where Are We? 26
Chapter 2: Hosting the Runtime: SQL Server As a Runtime Host 27 Why Care How Hosting Works? 27
What Is a .NET Framework Runtime Host? 29
SQL Server As a Runtime Host 31
Loading the Runtime: Processes and AppDomains 40
Safe Code: How the Runtime Makes It Safer to Run "Foreign" Code 43
Where the Code Lives: Storing .NET Framework Assemblies (CREATE ASSEMBLY) 46
Assembly Dependencies: When Your Assemblies Use Other Assemblies 49
Assemblies and SQL Schemas: Who Owns Assemblies (Information Schema) 51
Maintaining User Assemblies (ALTER ASSEMBLY, DROP ASSEMBLY) 55
Specification Compliance 58
Conclusions 60
Where Are We? 60
Chapter 3: Procedures and Functions in .NET CLR Languages 61 Extending SQL Server 61
CLR Extension Basics 63
CLR Extension Details 74
System.Data.SqlTypes 77
Parameters and Return Value 93
User-Defined Functions 93
Table-Valued Functions 99
Stored Procedures 106
Triggers 109
Where Are We? 110
Chapter 4: In-Process Data Access 111 Programming with SqlClient 111
Context: The SqlContext Class 115
Connections 118
Commands: Making Things Happen 121
Obtaining Results 124
Transactions 128
Pipe 135
Creating and Sending New Rowsets 137
Using the WindowsIdentity 139
Calling a Web Service from SQLCLR 140
Exception Handling 143
SqlTriggerContext 149
SqlClient Classes That You Can't Use on the Server 150
Where Are We? 151
Chapter 5: User-Defined Types and Aggregates 153 Why Do We Need User-Defined Types? 153
Overview of User-Defined Types 154
Creating a User-Defined Type 157
Where Are We? 217
Chapter 6: Security 219 New Security Features in SQL Server 2005 219
Optional Features Are Turned Off by Default 221
A Quick Review of SQL Server Security Concepts with Enhancements 224
SQL Server Password Policies and Credentials 235
Encryption Keys and Built-In Encryption Functions 238
Encryption Functions 242
Separation of Users and Schemas 248
Synonyms 252
Specifying Execution Context for Procedural Code 253
Code Signing 259
SQL Server Permissions and SQLCLR Objects 261
Assembly Permissions: Who Can Catalog and Use an Assembly? 262
What .NET Framework Code Can Do from within SQL Server: Safety Levels 268
Where Are We? 275
Chapter 7: SQL Engine Enhancements 277 Improvements to the SQL Engine 277
SNAPSHOT Isolation 278
Data Definition Language Triggers 289
Event Notifications 294
Large Value Data Types 298
Loading Data with the New BULK Provider 301
Statement-Level Recompilation 301
Query Hints, Plan Guides, and Plan Forcing 303
Where Are We? 309
Chapter 8: T-SQL Language Enhancements 311 Improvements to Transact-SQL 311
Error Handling 312
INTERSECT and EXCEPT 326
TOP 329
ON DELETE and ON UPDATE 333
OUTPUT 336
APPLY Operators 340
Common Table Expressions 343
Recursive Queries 351
PIVOT and UNPIVOT Operators 359
Ranking and Partitioning 366
TABLESAMPLE 376
Where Are We? 381
Chapter 9: XML in the Database: The XML Data Type 383 The XML Data Type 383
Using XML Data Type in Tables 384
Using XML Data Variables and Parameters 390
Typed and Untyped XML: Cataloguing and Using XML SCHEMA COLLECTIONs 392
Creating an Index on an XML Column 401
XML Type Functions 402
SELECT . . . FOR XML Enhancements 403
Mapping SQL and XML Data Types 414
OpenXML Enhancements 418 Loading XML into the Database from Files 420
ANSI SQL Standard Compliance 422
Where Are We? 426
Chapter 10: XML Query Languages: XQuery and XPath 429 What Is XQuery? 429
An Introduction to XQuery 431
Comparing and Contrasting XQuery and SQL 446
Using XQuery with the XML Data Type 449
XQuery Functions Supported by SQL Server 463
XQuery Operators Supported by SQL Server 464
XML DML: Updating XML Columns 468
Special Considerations When Using XQuery Inside SQL Server 475
Where Are We? 480
Chapter 11: SQL Server Service Broker 481 Messaging Applications 481
Processing Messages 494
Business Transactions 499
Where Are We? 559
Chapter 12: SQL Server As a Platform for Web Services 561 Mixing Databases and Web Services 561
Where Are We? 640
Chapter 13: SQL Server 2005 and Clients 641 SQL Native Client 641
New Data Types and Data Type Compatibility Mode 645
User-Defined Types and Relational Data Access APIs 646
Using .NET Framework UDTs in ADO.NET 647
Fetching UDT Data from a DataReader 648
Using .NET Framework UDTs in ODBC, OLE DB, and ADO Clients 654
Supporting the XML Data Type on the Client 657
Supporting the Large Value Data Types on the Client 667
Query Notification Support 670
Multiple Active Resultsets 689
New Transaction and Isolation Features in ADO.NET 695
Changes Related to SQL Server 2005 Login 699
Comparing the Client and Server Model for Stored Procedures 703
Where Are We? 704
Chapter 14: ADO.NET 2.0 and SqlClient 705 Generic Coding with the ADO.NET 2.0 Base Classes and Factories 705
Provider Factories 709
Specifying Configuration Information 712
Enumerating Data Sources and Building Connection Strings 714
Other Generic Coding Considerations 717
Schemas in ADO.NET 2.0 719
Tracing Data Access 731
Asynchronous Support 743
Bulk Import in SqlClient 749
Client Statistics 752
.NET Framework DataSet and SqlDataAdapter Enhancements 753
Where Are We? 758
Chapter 15: SQL Server Management Objects 761 Introduction 761
Object Model 769
SMO Projects 772
Connections 778
Server 787
SMO Objects 790
Create, Alter, and Drop 802
Scripts 807
Configuration Management 815
Where Are We? 818
Chapter 16: Notification Services 819 What Is SQL Server Notification Services? 819
Notification Applications 821
Components of SQL Server Notification Services 824
Notification Applications Design Patterns 828
Notification Services Delivery Features 829
Terms Used in Notification Services 829
Designing, Coding, and Generating a Notification Services Application 831
A Sample Notification Application 833
Instance and Application Configuration Files 834
Events 846
Subscribers and Subscriptions 858
Notifications 867
Distributor and Formatters 871
Delivery 873
Where Are We? 878
Chapter 17: Wrap-Up: Service-Oriented Database Applications 879 Lots of New Features: How to Use Them 879
Data Models, Programming, and SQL Server 880
Any Functionality at Any Tier 881
What Are the Best Practices? 882
Toward a Service-Oriented Database Architecture 891
The Database As Part of the Platform 894
Appendix A .NET Framework 101 897 The Common Language Runtime 897
Assemblies and Modules 901
The CLR Type System 905
Members of Types 914
Memory Management 918
Appendix B SQL Server Management Studio 923Introduction 923
Solutions and Projects 924
Templates 937
Object Explorer 944
Appendix C Visual Studio 2005 Integration: SQL Server Projects 949 Visual Studio 2005 949
References 963Index 965
The .NET Framework's Effects on SQL Server 3
The SQL:1999 Standard: Extending the Relational Model 8
User-Defined Types and SQL Server 11
XML: Data and Document Storage 14
Web Services: XML As a Marshaling Format 20
Client Access . . . And Then There Are Clients 22
Extending SQL Server into the Platform: Service Broker and Notification Services 24
Where Are We? 26
Chapter 2: Hosting the Runtime: SQL Server As a Runtime Host 27 Why Care How Hosting Works? 27
What Is a .NET Framework Runtime Host? 29
SQL Server As a Runtime Host 31
Loading the Runtime: Processes and AppDomains 40
Safe Code: How the Runtime Makes It Safer to Run "Foreign" Code 43
Where the Code Lives: Storing .NET Framework Assemblies (CREATE ASSEMBLY) 46
Assembly Dependencies: When Your Assemblies Use Other Assemblies 49
Assemblies and SQL Schemas: Who Owns Assemblies (Information Schema) 51
Maintaining User Assemblies (ALTER ASSEMBLY, DROP ASSEMBLY) 55
Specification Compliance 58
Conclusions 60
Where Are We? 60
Chapter 3: Procedures and Functions in .NET CLR Languages 61 Extending SQL Server 61
CLR Extension Basics 63
CLR Extension Details 74
System.Data.SqlTypes 77
Parameters and Return Value 93
User-Defined Functions 93
Table-Valued Functions 99
Stored Procedures 106
Triggers 109
Where Are We? 110
Chapter 4: In-Process Data Access 111 Programming with SqlClient 111
Context: The SqlContext Class 115
Connections 118
Commands: Making Things Happen 121
Obtaining Results 124
Transactions 128
Pipe 135
Creating and Sending New Rowsets 137
Using the WindowsIdentity 139
Calling a Web Service from SQLCLR 140
Exception Handling 143
SqlTriggerContext 149
SqlClient Classes That You Can't Use on the Server 150
Where Are We? 151
Chapter 5: User-Defined Types and Aggregates 153 Why Do We Need User-Defined Types? 153
Overview of User-Defined Types 154
Creating a User-Defined Type 157
Where Are We? 217
Chapter 6: Security 219 New Security Features in SQL Server 2005 219
Optional Features Are Turned Off by Default 221
A Quick Review of SQL Server Security Concepts with Enhancements 224
SQL Server Password Policies and Credentials 235
Encryption Keys and Built-In Encryption Functions 238
Encryption Functions 242
Separation of Users and Schemas 248
Synonyms 252
Specifying Execution Context for Procedural Code 253
Code Signing 259
SQL Server Permissions and SQLCLR Objects 261
Assembly Permissions: Who Can Catalog and Use an Assembly? 262
What .NET Framework Code Can Do from within SQL Server: Safety Levels 268
Where Are We? 275
Chapter 7: SQL Engine Enhancements 277 Improvements to the SQL Engine 277
SNAPSHOT Isolation 278
Data Definition Language Triggers 289
Event Notifications 294
Large Value Data Types 298
Loading Data with the New BULK Provider 301
Statement-Level Recompilation 301
Query Hints, Plan Guides, and Plan Forcing 303
Where Are We? 309
Chapter 8: T-SQL Language Enhancements 311 Improvements to Transact-SQL 311
Error Handling 312
INTERSECT and EXCEPT 326
TOP 329
ON DELETE and ON UPDATE 333
OUTPUT 336
APPLY Operators 340
Common Table Expressions 343
Recursive Queries 351
PIVOT and UNPIVOT Operators 359
Ranking and Partitioning 366
TABLESAMPLE 376
Where Are We? 381
Chapter 9: XML in the Database: The XML Data Type 383 The XML Data Type 383
Using XML Data Type in Tables 384
Using XML Data Variables and Parameters 390
Typed and Untyped XML: Cataloguing and Using XML SCHEMA COLLECTIONs 392
Creating an Index on an XML Column 401
XML Type Functions 402
SELECT . . . FOR XML Enhancements 403
Mapping SQL and XML Data Types 414
OpenXML Enhancements 418 Loading XML into the Database from Files 420
ANSI SQL Standard Compliance 422
Where Are We? 426
Chapter 10: XML Query Languages: XQuery and XPath 429 What Is XQuery? 429
An Introduction to XQuery 431
Comparing and Contrasting XQuery and SQL 446
Using XQuery with the XML Data Type 449
XQuery Functions Supported by SQL Server 463
XQuery Operators Supported by SQL Server 464
XML DML: Updating XML Columns 468
Special Considerations When Using XQuery Inside SQL Server 475
Where Are We? 480
Chapter 11: SQL Server Service Broker 481 Messaging Applications 481
Processing Messages 494
Business Transactions 499
Where Are We? 559
Chapter 12: SQL Server As a Platform for Web Services 561 Mixing Databases and Web Services 561
Where Are We? 640
Chapter 13: SQL Server 2005 and Clients 641 SQL Native Client 641
New Data Types and Data Type Compatibility Mode 645
User-Defined Types and Relational Data Access APIs 646
Using .NET Framework UDTs in ADO.NET 647
Fetching UDT Data from a DataReader 648
Using .NET Framework UDTs in ODBC, OLE DB, and ADO Clients 654
Supporting the XML Data Type on the Client 657
Supporting the Large Value Data Types on the Client 667
Query Notification Support 670
Multiple Active Resultsets 689
New Transaction and Isolation Features in ADO.NET 695
Changes Related to SQL Server 2005 Login 699
Comparing the Client and Server Model for Stored Procedures 703
Where Are We? 704
Chapter 14: ADO.NET 2.0 and SqlClient 705 Generic Coding with the ADO.NET 2.0 Base Classes and Factories 705
Provider Factories 709
Specifying Configuration Information 712
Enumerating Data Sources and Building Connection Strings 714
Other Generic Coding Considerations 717
Schemas in ADO.NET 2.0 719
Tracing Data Access 731
Asynchronous Support 743
Bulk Import in SqlClient 749
Client Statistics 752
.NET Framework DataSet and SqlDataAdapter Enhancements 753
Where Are We? 758
Chapter 15: SQL Server Management Objects 761 Introduction 761
Object Model 769
SMO Projects 772
Connections 778
Server 787
SMO Objects 790
Create, Alter, and Drop 802
Scripts 807
Configuration Management 815
Where Are We? 818
Chapter 16: Notification Services 819 What Is SQL Server Notification Services? 819
Notification Applications 821
Components of SQL Server Notification Services 824
Notification Applications Design Patterns 828
Notification Services Delivery Features 829
Terms Used in Notification Services 829
Designing, Coding, and Generating a Notification Services Application 831
A Sample Notification Application 833
Instance and Application Configuration Files 834
Events 846
Subscribers and Subscriptions 858
Notifications 867
Distributor and Formatters 871
Delivery 873
Where Are We? 878
Chapter 17: Wrap-Up: Service-Oriented Database Applications 879 Lots of New Features: How to Use Them 879
Data Models, Programming, and SQL Server 880
Any Functionality at Any Tier 881
What Are the Best Practices? 882
Toward a Service-Oriented Database Architecture 891
The Database As Part of the Platform 894
Appendix A .NET Framework 101 897 The Common Language Runtime 897
Assemblies and Modules 901
The CLR Type System 905
Members of Types 914
Memory Management 918
Appendix B SQL Server Management Studio 923Introduction 923
Solutions and Projects 924
Templates 937
Object Explorer 944
Appendix C Visual Studio 2005 Integration: SQL Server Projects 949 Visual Studio 2005 949
References 963Index 965