A Developer's Guide to SQL Server 2005

by Beauchemin, Bob; Sullivan, Dan

• ISBN13: 9780321382184
• ISBN10: 0321382188
• Edition: 1st
• Format: Paperback
• Copyright: 2006-04-28
• Publisher: Addison-Wesley Professional

Summary

"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 covera

Author Biography

Bob Beauchemin is a database-centric application practitioner and architect, DBA

Table of Contents

Figures	xxv
Tables	xxxix
Foreword	xliii
Roger Wolter
Foreword	xlv
Gert E. R. Drapers
Preface	xlix
Acknowledgments	lv
About the Authors	lvii
Introduction	1	(26)
The .NET Framework and the Microsoft Platform	1	(2)
The .NET Framework's Effects on SQL Server	3	(5)
The SQL:1999 Standard: Extending the Relational Model	8	(3)
User-Defined Types and SQL Server	11	(3)
XML: Data and Document Storage	14	(6)
Web Services: XML As a Marshaling Format	20	(2)
Client Access . . . And Then There Are Clients	22	(2)
Client-Side Database APIs and SQL Server 2005	22	(1)
Client-Side XML-Based APIs and SQL Server 2005 Integration	23	(1)
Extending SQL Server into the Platform: Service Broker and Notification Services	24	(2)
Where Are We?	26	(1)
Hosting the Runtime: SQL Server As a Runtime Host	27	(34)
Why Care How Hosting Works?	27	(2)
What Is a .NET Framework Runtime Host?	29	(2)
SQL Server As a Runtime Host	31	(9)
SQL Server Resource Management	31	(3)
Exceptional Condition Handling	34	(3)
Code Loading	37	(2)
Security	39	(1)
Loading the Runtime: Processes and AppDomains	40	(3)
Safe Code: How the Runtime Makes It Safer to Run ``Foreign'' Code	43	(3)
Where the Code Lives: Storing .NET Framework Assemblies (Create Assembly)	46	(3)
Assembly Dependencies: When Your Assemblies Use Other Assemblies	49	(2)
Assemblies and SQL Schemas: Who Owns Assemblies (Information Schema)	51	(4)
System Metadata Tables and Information_Schema	52	(1)
Assembly Metadata	53	(2)
Maintaining User Assemblies (Alter Assembly, Drop Assembly)	55	(3)
Specification Compliance	58	(2)
Conclusions	60	(1)
Where Are We?	60	(1)
Procedures and Functions in .NET CLR Languages	61	(50)
Extending SQL Server	61	(2)
CLR Extension Basics	63	(11)
CLR Extension Details	74	(3)
System.Data.SqlTypes	77	(16)
A Few Words About Null Values	77	(3)
Using SqlTypes	80	(13)
Parameters and Return Value	93	(1)
User-Defined Functions	93	(6)
Table-Valued Functions	99	(7)
Stored Procedures	106	(3)
Triggers	109	(1)
Where Are We?	110	(1)
In-Process Data Access	111	(42)
Programming with SqlClient	111	(4)
Context: The SqlContext Class	115	(3)
Connections	118	(3)
Commands: Making Things Happen	121	(3)
Obtaining Results	124	(4)
Transactions	128	(7)
TransactionScope Exotica	132	(1)
Best Practices	133	(2)
Pipe	135	(2)
Creating and Sending New Rowsets	137	(2)
Using the WindowsIdentity	139	(1)
Calling a Web Service from SQLCLR	140	(3)
Exception Handling	143	(6)
SqlTriggerContext	149	(1)
SqlClient Classes That You Can't Use on the Server	150	(1)
Where Are We?	151	(2)
User-Defined Types and Aggregates	153	(66)
Why Do We Need User-Defined Types?	153	(1)
Overview of User-Defined Types	154	(3)
Creating a User-Defined Type	157	(60)
Null Value Implementation	160	(1)
String Implementation	160	(5)
Binary Implementation	165	(2)
IBinarySerialize. Read/Write	167	(3)
Creating User-Defined Types	170	(3)
Public Properties, Fields, and Methods	173	(8)
Helper Functions	181	(2)
User-Defined-Type Validation	183	(2)
Maintaining User-Defined-Type Definitions	185	(2)
User-Defined Types and XML	187	(6)
Should Objects Be Represented by User-Defined Types?	193	(14)
User-Defined Aggregates	207	(2)
Implementing a User-Defined Aggregate	209	(5)
Creating User-Defined Aggregate	214	(1)
Format.Native vs. Format.UserDefined	215	(2)
Where Are We?	217	(2)
Security	219	(58)
New Security Features in SQL Server 2005	219	(2)
Optional Features Are Turned Off by Default	221	(3)
Configuring Security Settings	221	(2)
Security and Metadata	223	(1)
A Quick Review of SQL Server Security Concepts with Enhancements	224	(11)
Authentication and Authorization: Principals and Permissions	224	(8)
Execution Context and Ownership Chaining	232	(3)
SQL Server Password Policies and Credentials	235	(3)
Encryption Keys and Built-in Encryption Functions	238	(4)
Encryption Functions	242	(6)
Separation of Users and Schemas	248	(4)
Synonyms	252	(1)
Specifying Execution Context for Procedural Code	253	(6)
Code Signing	259	(2)
SQL Server Permissions and SQLCLR Objects	261	(1)
Assembly Permissions: Who Can Catalog and Use an Assembly?	262	(6)
What .NET Framework Code Can Do from within SQL Server: Safety Levels	268	(7)
Introduction to Code Access Security	269	(1)
Code Access Security and .NET Framework Assemblies	270	(5)
Where Are We?	275	(2)
SQL Engine Enhancements	277	(34)
Improvements to the SQL Engine	277	(1)
Snapshot Isolation	278	(11)
Drawbacks of Versioning	286	(2)
Monitoring Versioning	288	(1)
Data Definition Language Triggers	289	(5)
Event Notifications	294	(4)
Large Value Data Types	298	(3)
Loading Data with the New Bulk Provider	301	(1)
Statement-Level Recompilation	301	(2)
Query Hints, Plan Guides, and Plan Forcing	303	(6)
Where Are We?	309	(2)
T-SQL Language Enhancements	311	(72)
Improvements to Transact-SQL	311	(1)
Error Handling	312	(14)
Intersect and Except	326	(3)
Top	329	(4)
On Delete and On Update	333	(3)
Output	336	(4)
Apply Operators	340	(3)
Common Table Expressions	343	(8)
Recursive Queries	351	(8)
Pivot and Unpivot Operators	359	(7)
Pivot	359	(6)
Unpivot	365	(1)
Ranking and Partitioning	366	(10)
Row_Number	368	(1)
Rank	369	(1)
Dense_Rank	370	(1)
Ntile	371	(2)
Partition By	373	(1)
Aggregate Partitions	374	(2)
Tablesample	376	(5)
Where Are We?	381	(2)
XML in the Database: The XML Data Type	383	(46)
The XML Data Type	383	(1)
Using XML Data Type in Tables	384	(6)
Using XML Data Variables and Parameters	390	(2)
Typed and Untyped XML: Cataloguing and Using XML Schema Collections	392	(9)
SQL Server XML Schema Collections	394	(1)
Typed XML	395	(2)
Management of XML Schemas and Schema Collections	397	(3)
Security for XML Schema Collections and Strongly Typed Instances	400	(1)
Creating an Index on an XML Column	401	(1)
XML Type Functions	402	(1)
Select . . . For XML Enhancements	403	(11)
For XML Path Mode	406	(1)
Producing an XML Data Type	407	(2)
Producing an Inline XSD Format Schema	409	(2)
Null Database Values	411	(1)
Producing a Root Element	412	(1)
Other Features	413	(1)
Mapping SQL and XML Data Types	414	(4)
Mapping SQL Types to XML Types	415	(1)
String, Binary, and Decimal Types	415	(1)
Other General Data Types	416	(1)
Date Data Type	417	(1)
Pattern-Based Data Types	417	(1)
Wildcard Data Types	418	(1)
Nullability	418	(1)
OpenXML Enhancements	418	(2)
Loading XML into the Database from Files	420	(2)
ANSI SQL Standard Compliance	422	(4)
XML Data Type	422	(2)
Mapping SQL Catalogs and Tables to XML	424	(1)
Mapping SQL Data Types to XML Data Types	425	(1)
Where Are We?	426	(3)
XML Query Languages: XQuery and XPath	429	(52)
What Is XQuery?	429	(2)
An Introduction to XQuery	431	(15)
The XQuery Prolog	436	(1)
The XQuery Body	437	(9)
Comparing and Contrasting XQuery and SQL	446	(3)
Using XQuery with the XML Data Type	449	(14)
xml.exist(string xquery-text)	450	(2)
xml.value(string xquery-text, string SQLType)	452	(2)
xml.query(string xquery-text)	454	(3)
xml.nodes(string xquery-text)	457	(4)
XQuery Standard Functions and Operators	461	(2)
SQL Server XQuery Functions and Operators	463	(1)
XQuery Functions Supported by SQL Server	463	(1)
XQuery Operators Supported by SQL Server	464	(4)
SQL Server XQuery Extended Functions	465	(3)
Multiple-Document Query in SQL Server XQuery	468	(1)
XML DML: Updating XML Columns	468	(7)
xml.modify('insert . . .')	470	(3)
xml.modify('delete . . .')	473	(1)
xml.modify('replace value of . . .')	474	(1)
General Conclusions and Best Practices	474	(1)
Special Considerations When Using XQuery Inside SQL Server	475	(5)
XML Schemas and SQL Server 2005 XQuery	475	(2)
XML Indexes Usage in XQuery	477	(3)
Where Are We?	480	(1)
SQL Server Service Broker	481	(80)
Messaging Applications	481	(13)
Processing Messages	494	(5)
Business Transactions	499	(60)
Service Programs	503	(6)
Conversations	509	(7)
Conversation Groups	516	(8)
Message Types	524	(4)
Contracts	528	(2)
Send and Message Type	530	(4)
Processing Loop	534	(6)
Poison Messages	540	(1)
Compensation	541	(3)
Distribution	544	(15)
Where Are We?	559	(2)
SQL Server As a Platform for Web Services	561	(80)
Mixing Databases and Web Services	561	(79)
HTTP Endpoint Declaration	564	(5)
Create Endpoint	569	(1)
Endpoint URL	570	(1)
Endpoint State	571	(1)
Endpoint Metadata	572	(1)
Webmethods	573	(5)
WSDL Web Service Definition Language	578	(26)
XML Namespaces	604	(1)
WSDL Generation	605	(9)
Stored Procedure in Web Service	614	(8)
SQL Batches	622	(4)
Other Features	626	(1)
SQLXML 4.0 Functionality and SQL Server 2005	627	(13)
Where Are We?	640	(1)
SQL Server 2005 and Clients	641	(64)
SQL Native Client	641	(4)
New Data Types and Data Type Compatibility Mode	645	(1)
User-Defined Types and Relational Data Access APIs	646	(1)
Using .NET Framework UDTs in ADO.NET	647	(1)
Fetching UDT Data from a DataReader	648	(6)
Using .NET Framework UDTs in ODBC, OLE DB, and ADO Clients	654	(3)
Supporting the XML Data Type on the Client	657	(10)
Using the XML Data Type in ADO.NET	657	(2)
Getting the XML As XML or a String	659	(2)
Documents, Fragments, and For XML Support	661	(3)
Using the XML Data Type in ADO Classic	664	(3)
Supporting the Large Value Data Types on the Client	667	(3)
Query Notification Support	670	(19)
Query Notifications in SQL Server 2005	673	(3)
Using Query Notifications in OLE DB and ODBC	676	(1)
Dispatching a Notification to an End User or Cache	677	(2)
Using Query Notifications from a Database Client	679	(1)
Using SqlDependency	680	(2)
Using SqlNotificationRequest	682	(3)
Using SqlCacheDependency in ASP.NET	685	(2)
Eager Notifications	687	(1)
When Not to Use Notifications	688	(1)
Multiple Active Resultsets	689	(6)
Using Mars in ADO.NET 2.0	691	(4)
Mars in SQL Native Client	695	(1)
New Transaction and Isolation Features in ADO.NET	695	(4)
Using the New Isolation Levels	695	(2)
Promotable and Declarative Transactions	697	(2)
Changes Related to SQL Server 2005 Login	699	(4)
Password Change Support	699	(2)
Failover Support	701	(1)
Encryption Support	702	(1)
Comparing the Client and Server Model for Stored Procedures	703	(1)
Where Are We?	704	(1)
ADO.NET 2.0 and SqlClient	705	(56)
Generic Coding with the ADO.NET 2.0 Base Classes and Factories	705	(4)
Provider Factories	709	(3)
Specifying Configuration Information	712	(2)
Enumerating Data Sources and Building Connection Strings	714	(3)
Other Generic Coding Considerations	717	(2)
Schemas in ADO.NET 2.0	719	(12)
Who Needs Metadata, Anyway?	720	(1)
What Metadata Is Available?	721	(5)
Customizing and Extending the Metadata	726	(3)
User Customization	729	(2)
Tracing Data Access	731	(12)
Setting Up Data Tracing	732	(1)
Running the Trace	733	(1)
Harvest the Results As a CSV File	734	(1)
Reading the Trace Output	735	(1)
User Data and ADO.NET Tracing	736	(1)
Configuring Which Applications Are Traced	736	(4)
Using Tracing to Debug a Parameter Binding Problem	740	(2)
Inside Data Tracing	742	(1)
What Is ETW?	742	(1)
Asynchronous Support	743	(6)
Bulk Import in SqlClient	749	(3)
Client Statistics	752	(1)
.NET Framework DataSet and SqlDataAdapter Enhancements	753	(5)
Where Are We?	758	(3)
SQL Server Management Objects	761	(58)
Introduction	761	(8)
Why Use SMO?	764	(3)
ADO vs. SMO	767	(2)
Object Model	769	(3)
SMO Projects	772	(6)
Connections	778	(9)
Default Windows Credentials Connection	778	(2)
Using Runas	780	(1)
Automatic Connection	780	(2)
Windows Identity Impersonation	782	(1)
SQL Server Login	783	(1)
Changing SQL Server Login Password	784	(1)
Connection Management	785	(2)
Server	787	(3)
SMO Objects	790	(12)
Object Identity and URNs	790	(3)
GetSmoObject	793	(1)
URN Retrieval Restriction	794	(1)
SMO Object Model	795	(1)
SMO Properties	796	(4)
SMO Parent Navigation	800	(2)
SMO State	802	(1)
Create, Alter, and Drop	802	(5)
Scripts	807	(8)
Configuration Management	815	(3)
Where Are We?	818	(1)
Notification Services	819	(60)
What Is SQL Server Notification Services?	819	(2)
Notification Applications	821	(3)
Components of SQL Server Notification Services	824	(4)
Notification Applications Design Patterns	828	(1)
Notification Services Delivery Features	829	(1)
Terms Used in Notification Services	829	(2)
Designing, Coding, and Generating a Notification Services Application	831	(2)
Planning Steps	831	(1)
Coding Steps	832	(1)
Application Generation Steps	833	(1)
A Sample Notification Application	833	(1)
Instance and Application Configuration Files	834	(12)
Instance Configuration File	835	(4)
Application Definition File	839	(4)
NSControl	843	(3)
Events	846	(12)
Event Classes	846	(4)
Event Stored Procedures	850	(2)
Event Providers	852	(5)
Chronicles	857	(1)
Subscribers and Subscriptions	858	(9)
Subscription Classes	860	(2)
Subscribers	862	(3)
Subscriptions	865	(2)
Notifications	867	(4)
Distributor and Formatters	871	(2)
Delivery	873	(5)
Delivery Protocols	874	(3)
Customization	877	(1)
Where Are We?	878	(1)
Wrap-Up: Service-Oriented Database Applications	879	(18)
Lots of New Features: How to Use Them	879	(1)
Data Models, Programming, and SQL Server	880	(1)
Any Functionality at Any Tier	881	(1)
What Are the Best Practices?	882	(9)
Toward a Service-Oriented Database Architecture	891	(3)
The Database As Part of the Platform	894	(3)
Appendixes
A. .NET Framework 101	897	(26)
The Common Language Runtime	897	(4)
Development Languages	899	(2)
Assemblies and Modules	901	(4)
Modules	901	(2)
Assemblies	903	(1)
Assembly Names and Versions	904	(1)
The CLR Type System	905	(9)
Classes and Types	907	(1)
Visibility	908	(2)
Instances	910	(2)
Namespaces	912	(2)
Members of Types	914	(4)
Fields and Properties	916	(1)
Parameters and Methods	917	(1)
Memory Management	918	(5)
Finalization and Destructors	919	(1)
Disposing	920	(3)
B. SQL Server Management Studio	923	(26)
Introduction	923	(1)
Solutions and Projects	924	(13)
Configuration	925	(2)
Making a Solution/Project	927	(10)
Templates	937	(7)
Object Explorer	944	(5)
C. Visual Studio 2005 Integration: SQL Server Projects	949	(14)
Visual Studio 2005	949	(14)
Custom Attributes and Automatic Deployment	955	(1)
Automatic Deployment	956	(2)
Data Types and SqlFacet	958	(1)
Executing and Debugging the Code	959	(4)
References	963	(2)
Index	965

Excerpts

Bob Beauchemin Wow. I can't believe it's been almost two years since I was writing the preface for our first book,A First Look at SQL Server 2005 for Developers. Those two years, and the two years before, have been a whirlwind of working with the SQL Server product, including teaching, consulting, writing, and conferences. I still haven't stopped to catch my breath. SQL Server 2005 was officially launched in September 2005. And we've been working on enhancements to theFirst Lookbook since it was published in mid-2004. As with any book on a beta version of a product, the product itself changed after the book was published. In fact, one of the first changes was a feature cut that came two weeks after the book shipped. Keeping up has been a high-energy endeavor, because since beta 2, there were an order of magnitude more additions and enhancements than cuts. This book is the product of the great response we got to theFirst Lookbook and the answer to the frequently asked question, "When are you guys going to do an update?" I'd say that this is quite a bit more than an update, however. We've included lots of new and revised material; there are too many changes to enumerate. There is even a section on best practices because at this point in time, the early adopters who shipped revised versions of their software that works with SQL Server 2005 have already had a few years to practice. We did, however, wait for the "golden bits" to appear before embarking on the last of the series of revisions. This turned out to be a good move, as the implementation details of a few of the features changed slightly up until the last candidate release. This is a Sisyphus-like strategy for the most part, however, as the software keeps improving. As I write this, Service Pack 1 has been announced and may indeed change things slightly. Work has begun on the next release of SQL Server, code-named Katamai, and the next version of the .NET Framework is currently available in beta. Software products are never static. Dan and I will be including the code samples from the book and updates on our Web sites: http://www.SQLskills.com (Books section) http://www.pluralsight.com We'll also be writing about SQL Server 2005 features and enhancements in our blogs. The ANSI SQL 2003 specification "shipped," too, and included a slightly different version of Part 14: XML Related Specifications (SQL/XML). The W3C XQuery committee is closing in on becoming a recommendation. As an additional point of note, since theFirst Lookbook was published, both of Microsoft's biggest competitors in the database space have released .NET Framework procedure inclusion (although integration is not as tight) and have announced or released XQuery support. By now, you probably have heard about the .NET Framework-based procedures, the inclusion of the XML data type and XQuery language, the enhancements to T-SQL to support robust error handling and queries against hierarchical data and open-schema based designs, and versioningbased transaction isolation. These features, however, combined with inclusion of an asynchronous messaging system, SQL Server Service Broker, permit SQL Server to move toward a Service Oriented Database Architecture. Event Notifications, Query Notifications, and database mail are the first manifestations of this type of architecture; it moves SQL Server toward being more of a system that responds asynchronously to external events, rather than being strictly connection-and-statement-based. Service Broker, combined with CLR procedural code and XML data support, enables database programmers to build their own service-based applications. The inclusion of support for Web Services as an alternate client stack is another manifestation of this architecture. This is such a compelling programming model that Mi