First Look at SQL Server 2005 for Developers : 'Yukon' Beta for Developers

by Beauchemin, Bob; Berglund, Niels; Sullivan, Dan

• ISBN13: 9780321180599
• ISBN10: 0321180593
• Format: Paperback
• Copyright: 2004-01-01
• Publisher: Addison-Wesley Professional

Summary

bull; Written by acknowledged experts, with unprecedented cooperation from Microsoft bull; The next version of SQL Server will be the biggest change in years, and there is a great deal of intense interest in what exactly those changes will be bull; This will enable developers to start working with the beta, so they will be ready once the final product is available

Table of Contents

Foreword	p. xxi
Foreword	p. xxv
Foreword	p. xxvii
About the Authors	p. xxix
Preface	p. xxxi
Acknowledgments	p. xxxvii
Introduction	p. 1
The .NET Runtime and the Microsoft Platform	p. 1
.NET's Effects on SQL Server	p. 3
The SQL: 1999 Standard--Extending the Relational Model	p. 8
User-Defined Types and SQL Server	p. 10
XML--Data and Document Storage	p. 13
Web Services--XML as a Marshaling Format	p. 19
Client Access--And Then There Are Clients	p. 20
ObjectSpaces--Objects on the Middle Tier or Client	p. 23
Extending SQL Server into the Platform--Service Broker and Notification Services	p. 24
Where Are We?	p. 26
Hosting the Runtime: SQL Server as a Runtime Host	p. 27
Why Care How Hosting Works?	p. 27
What Is a .NET Runtime Host?	p. 28
SQL Server as a Runtime Host	p. 29
Loading the Runtime--Processes and AppDomains	p. 35
Safe Code--How the Runtime Makes It Safer to Run "Foreign Code"	p. 37
Where the Code Lives--Storing .NET Assemblies (Create Assembly)	p. 39
Assembly Dependencies--When Your Assemblies Use Other Assemblies	p. 42
Assemblies and SQL Schemas--Who Owns Assemblies (Information Schema)	p. 44
Maintaining User Assemblies (Alter Assembly, Drop Assembly)	p. 48
Specification Compliance	p. 51
Conclusions	p. 52
Where Are We?	p. 52
Procedures and Functions in .NET Languages	p. 53
Extending SQL Server	p. 53
CLR Methods in SQL Server	p. 55
System.Data.SqlTypes	p. 60
Procedures	p. 75
Scalar-Valued Functions	p. 81
Table-Valued Functions	p. 86
Triggers	p. 92
Where Are We?	p. 93
The In-Process Data Provider	p. 95
The SQL Server Programming Model	p. 95
Context--The SqlContext Class	p. 99
Connections	p. 102
Commands--Making Things Happen	p. 106
SqlExecutionContext and SqlDefinition	p. 110
Obtaining Results	p. 112
Transactions	p. 115
Pipe	p. 121
Exception Handling	p. 123
SqlTriggerContext	p. 126
Where Are We?	p. 129
User-Defined Types and Aggregates	p. 131
Why Do We Need User-Defined Types?	p. 131
Alias Types	p. 132
Overview of User-Defined Types	p. 133
Creating a User-Defined Type	p. 135
Implementing a User-Defined Type	p. 136
Implementing LDim	p. 142
Should Objects Be Represented by User-Defined Types?	p. 162
User-Defined Aggregates	p. 165
Where Are We?	p. 173
Security	p. 175
New Security Features in SQL Server 2005	p. 175
Optional Features Are Turned Off by Default	p. 177
A Quick Review of SQL Server Security Concepts with Enhancements	p. 178
SQL Server Password Policies and Credentials	p. 186
Separation of Users and Schemas	p. 189
Specifying Execution Context for Procedural Code	p. 194
SQL Server Permissions and the New Objects	p. 197
Assembly Permissions--Who Can Catalog and Use an Assembly?	p. 198
Permissions, Visibility, UDTs, and User-Defined Aggregates	p. 202
What Can .NET Code Do from within SQL Server: Safety Levels	p. 204
Where Are We?	p. 209
T-SQL Enhancements	p. 211
Improvements to Transact-SQL	p. 211
SNAPSHOT Isolation	p. 212
Statement-Level Recompilation	p. 222
DDL Triggers	p. 226
Event Notifications	p. 231
Large Value Data Types	p. 235
T-SQL Language Enhancements	p. 236
Transaction Abort Handling	p. 263
Where Are We?	p. 264
XML in the Database: The XML Data Type	p. 265
The XML Data Type	p. 265
Using the XML Data Type in Tables	p. 266
Using XML Data Variables and Parameters	p. 271
Typed and Untyped XML--Cataloging and Using XML Schema Collections	p. 272
Creating an Index on an XML Column	p. 282
XML Type Functions	p. 283
SELECT ... FOR XML Enhancements	p. 284
Mapping SQL and XML Data Types	p. 294
OpenXML Enhancements	p. 297
Using XMl Bulk Load inside the Database	p. 299
ANSI SQL Standard Compliance	p. 300
Where Are We?	p. 304
XML Query Languages: XQuery and XPath	p. 305
What Is XQuery?	p. 305
An Introduction to XQuery	p. 307
Comparing and Contrasting XQuery and SQL	p. 318
Using XQuery with the XML Data Type	p. 321
XML DML--Updating XML Columns	p. 337
Special Considerations When Using XQuery inside SQL Server	p. 343
Where Are We?	p. 345
SQL Server as a Platform for Web Services	p. 347
Mixing Databases and Web Services	p. 347
HTTP Endpoint Declaration	p. 349
Endpoint State	p. 351
Parameters That Relate to Serving HTTP	p. 351
Security Choices and XML Web Services	p. 353
Defining Access through HTTP	p. 358
HTTP Endpoint Metadata Views	p. 361
XML Input and Output Types from SQL Server Web Services	p. 361
Using the XML Data Type and Web Services	p. 372
Accessing HTTP Endpoints with .NET Code	p. 374
Where Are We?	p. 381
ADO and ADO.NET Enhancements	p. 383
User-Defined Types and Relational Data Access APIs	p. 383
Using .NET UDTs in ADO.NET	p. 384
Fetching UDT Data from a DataReader	p. 385
Using the SqlMetaData Class	p. 389
Using .NET UDTs in ODBC, OLE DB, and ADO Clients	p. 392
Supporting and XML Data Type in ADO and ADO.NET Clients	p. 399
Using the New Types with the .NET DataSet and SqlDataAdapter	p. 403
Comparing the Client and Server Model for Stored Procedures	p. 407
Where Are We?	p. 407
SQL Client Enhancements	p. 409
ADO.NET 2.0 and the SqlClient Data Provider	p. 409
Multiple Active Resultsets	p. 413
Notification Support	p. 418
Asynchronous Support	p. 422
Snapshot Isolation	p. 427
Bulk Import in SqlClient	p. 429
Miscellaneous Features	p. 432
Where Are We?	p. 434
Client-Side XML: SQLXML and Mapping	p. 437
The Common Query Abstraction	p. 438
Mapping between Different Data Models	p. 441
XML Queries over XML Views of Relational Data	p. 448
Using XQuery Command with SQL Server or XML Documents	p. 450
Client versus SQL Server XQuery Functionality	p. 453
SqlXml Bulk Load and the SqlXmlRowsetAdapter	p. 454
SqlXml DBObject	p. 456
Where Are We?	p. 457
ObjectSpaces	p. 459
Introduction to Object-Relational Mapping	p. 459
A Simple ObjectSpaces Application	p. 462
Data Manipulation Classes in the ObjectSpaces API	p. 469
Accessing a Database with the ObjectSpace	p. 469
Patterns and Mapping	p. 471
Maintaining Object Identity and State	p. 480
Reading Objects with an ObjectReader	p. 484
ObjectSet	p. 486
The ObjectSpaces Query Model	p. 488
OPath Language Essentials	p. 490
Manipulating Graphs of Related Objects--Optimizations	p. 495
Beyond the ObjectSpace Class--Customizations	p. 501
Where Are We?	p. 502
SQL Server Service Broker	p. 503
Messaging Applications	p. 503
SQL Server Service Broker Overview	p. 505
Service Broker Applications Guidelines	p. 518
Service Broker Example	p. 519
Message Type	p. 519
Contracts	p. 523
Queues	p. 524
Services	p. 529
Dialogs	p. 531
Service Programs	p. 536
Routes	p. 547
Security	p. 549
Where Are We?	p. 555
Notification Services	p. 557
What Is SQL Server Notification Services?	p. 557
Notification Applications	p. 559
Components of SQl Server Notification Services	p. 561
Notification Applications Design Patterns	p. 565
Notification Services Delivery Features	p. 566
Terms Used in Notification Services	p. 566
Designing, Coding, and Generating a Notification Services Application	p. 567
A Sample Notification Application	p. 569
Instance and Application Configuration Files	p. 570
Events	p. 580
Subscribers and Subscriptions	p. 592
Notifications	p. 600
Distributor and Formatters	p. 603
Delivery	p. 605
Where Are We?	p. 609
Wrap-up: Relations, XML, Objects, and Services	p. 611
Lots of New Features	p. 611
Data Models, Programming, and SQL Server	p. 612
Any Functionality at Any Tier	p. 613
So Which Tier and What Data Model?	p. 615
The Database as Part of the Platform	p. 618
.NET 101	p. 621
The Common Language Runtime	p. 621
Assemblies and Modules	p. 624
The CLR Type System	p. 629
Members of Types	p. 637
Memory Management	p. 641
Tools Integration	p. 645
SQL Server Management Studio	p. 645
Visual Studio 2005	p. 655
Bibliography	p. 663
Index	p. 665
Table of Contents provided by Rittenhouse. All Rights Reserved.

Excerpts

After my last book,Essential ADO.NET, was handed in to the publisher ten days before .NET 1.0 shipped, I swore I'd never write another. To keep up with a technology while it was developing and the product features were being refined on an almost daily basis was too big an energy sink. Then, less than a year later, I caught wind of a new version of SQL Server, code-named Yukon. As with each version of SQL Server before it,since Microsoft's original 4.21 offering, there were lots of features for DBAs--high-availability features, tuning features, scalability features, andso on. A new, fast-growing field calledbusiness intelligencewas being developed,and SQL Server was on the cusp of this. The features in this business intelligence area of Yukon were truly astounding. But the biggest changes that caught my eye were those in the developer area. I was hooked. Transact-SQL has served us developers well all these years and continues to work quite well, thank you. This book lists the enhancements to this procedural dialect of SQL, and that chapter ended up to be much longer (because of the number of enhancements) than I originally thought. In the last few years, I'd been spending a lot of time in the XML space and done a lot of thinking about the differences and similarities between the XML and relational models. I liked the formal W3C standardization process for XML, slow as it seems at times. I started to investigate the ANSI SQL standards in earnest, though I'd read them before, and realized that SQL has a rich and deep foundation, starting with SQL-86 and up to the last mainstream standard, SQL-92, and past that to SQL:1999. But in 2002 there were specifications in progress to define how XML would be integrated into a relational database. There was a synergy with the XML work I'd been doing lately. I heard there would be XML functionality in Yukon, including an XML data type, XML schema validation, and an implementation of the emerging standard query language for XML, XQuery. In addition, beginning with the object-oriented graphical user interface on the NeXT computer, I'd spent a lot of the last ten years using object-oriented techniques. And Yukon promised to integrate the .NET runtime into the database engine itself. Not that SQL Server internals were to be written in .NET, but that .NET would be directly accessible as a language for stored procedures and user-defined functions. I could use object-oriented programming techniques with my database programming as well. This might be a big assist to the procedural programming in T-SQL I was already doing, in those cases where I needed it. I'd read about using object-oriented languages in the ANSI SQL specifications. Finally, there was the rumor that .NET classes might be available in SQL Server as types that the server knew about. I'd read the ANSI spec for that too. I justhadto see this product. So we had been working on writing this book since late 2002 when I met with Eric Brown in Redmond, Washington, and we got the OK and the software. Since 2002 we'd badgered the SQL Server and Webdata teams with copious questions about not only how the software worked but why it was designed that way. They were very understanding about our persistence,but sometimes I felt that I was being a bit of a pest. When we started to teach the class in earnest, we tried to pay them back with information about how software vendors and others thought the features would be useful. At that time, Niels and I were writing, and Dan was reviewing and making suggestions; however, Dan got hooked too. We almost published in the beta 1 time frame, but held back. There were too many major enhancements to the way things actually worked, and we'd written about how things worked too early. Readers would think things worked the old way instead of the improved way. And there were more enhancements coming in beta 2. We held off writing and went for another revision. We were perm