A first look at Microsoft SQL Server 2005 for developers / Bob Beauchemin, Niels Berglund, Dan Sullivan.

By:

Beauchemin, Bob

Contributor(s):

Material type: Text

TextSeries: Microsoft .NET development seriesPublication details: Boston, Mass. ; London : Addison-Wesley, c2004.Description: xxxviii, 693 p. : ill. ; 23 cmISBN:

0321180593 (pbk.)
9780321180599 (pbk.)

Subject(s):

SQL server

DDC classification:

005.7585 BEA

Holdings
Item type	Current library	Call number	Status	Date due	Barcode
Standard Loan	Thurles Library Main Collection	005.7585 BEA (Browse shelf(Opens below))	Available		30026000068782

Enhanced descriptions from Syndetics:

Shows working developers how to take full advantage of Yukon's key innovations. This work includes practical explanations of Yukon's data model, built-in .NET hosting, improved programmability, SQL-99 compliance, and more.

Table of contents provided by Syndetics

Foreword (p. xxi)
Foreword (p. xxv)
Foreword (p. xxvii)
About the Authors (p. xxix)
Preface (p. xxxi)
Acknowledgments (p. xxxvii)
1 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)
2 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)
3 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)
4 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)
5 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)
6 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)
7 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)
8 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)
9 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)
10 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)
11 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)
12 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)
13 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)
14 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)
15 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)
16 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)
17 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)
Appendix A .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)
Appendix B Tools Integration (p. 645)
SQL Server Management Studio (p. 645)
Visual Studio 2005 (p. 655)
Bibliography (p. 663)
Index (p. 665)

Excerpt provided by Syndetics

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 called business intelligence was 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 just had to 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 permitted, however, to write and teach a class to early adopters, based on the current state of our work and of the product. I think we've taught about 400 to 500 students as of this writing. The product evolved. More revisions. I'd told Mary that I was "almost done" so many times that she shook her head and laughed when I mentioned I might really be done again five minutes ago. It's certainly possible that some of the features or the implementation of them could change between now and when SQL Server 2005 ships. We'll try to keep you up to date with all the changes, update the book's code examples, and post some additional samples on the book's Web site. Look for pointers to the code and updates at the following locations: http://staff.develop.com/bobb http://www.danal.com http://staff.develop.com/nielsb Yukon has all the stuff that I'd read about and more. At a SQL*PASS conference, I ran into Roger Wolter, a friend from the SQL Server team. We'd originally met when I was speaking at a Web Services conference on SQLXML Web Services in SQL Server 2000 Web Release 3. Little did I know that Roger, the "owner" of this feature, was in the audience. He said he liked the talk; I was relieved. When we met again at SQL*PASS, I asked Roger about Web Services in Yukon, and he told me about his latest project, called SQL Server Service Broker. It sounded to me like an implementation of a Web Service-like concept built over the robustness of a queuing system, built on top of the robustness of a database. Otherwise, his recollection of the meeting is about the same as mine. I was further intrigued. So far I've mentioned Service Broker, Transact-SQL enhancements, security, .NET-based procedures, functions and user-defined types, built-in XML data type and queries, and Web Services. What else could there possibly be for developers? Most developers spend the greatest percentage of their time not in SQL Server Management Studio, but in Visual Studio 2005, writing the client front end. Many developers know the database engine and how to get the most out of it as well as race car drivers know how to get that last bit of speed out of their cars. Developers and application designers,as well as DBAs, must know how the new features work. In the case of snapshot isolation (versioning) or the XML data type, it could drastically affect how they design and write the application. With Service Broker, it opens a whole new raft of scalability choices that simply weren't there before. The last part of this book talks about client and application server features enabled either inside or outside SQL Server itself. The enabling technologies outside the database are ADO.NET's SqlClient and the client-side XML stack featuring its own XQuery engine. Client-side XQuery works against XML documents on the file system, against SQLServer, or both at the same time. Finally, there are those developers who would rather not know that the database exists at all. They deal in objects and would just like to call "Load" and "Save" on their object model and have it taken care of. They're more interested in their business or application domain than in how to create a distributed partitioned view to spread the Customer table over multiple SQL Server instances. They see only Customer instances and collections. For these folks, ObjectSpaces and Microsoft Business Framework are what float their boat in this release. In addition to being able to persist their instances, they want "Load" and "Save" to take nanoseconds. ObjectSpaces was designed with optimization in mind. In conclusion, I think there's quite a bit in Yukon for just about every developer, DBA, application designer, business analyst, and data miner. I've read in some trade publications that the new features just aren't that interesting; they're more like a recitation of glitzy acronyms than substance. This may be the initial perception, but let's rewind to mid-1981. I'm working for an insurance company in Seattle, and we're planning to convert our indexed file data, which we'd just converted from ISAM (indexed sequential access method) to VSAM (virtual storage access method), to a better, more robust database engine. The one we had in mind was IMS (IBM's Information Management System product). The salesperson, however, wants us to look at some new-fangled database they call SQL/DS (which eventually became DB2). After designing some tables and playing around with some queries, we asked some tough questions like "Why does it matter that a database engine is built on a mathematical theory?" and "Why would you want to learn a foreign query language called SQL rather than using nice, fast assembly language or COBOL programs?" and "Why did you just decompose our 2 nice, understandable records into 30 little tables just to join them back together again?" and "Why does it go so slow?" It was the beginning of the relational era. Relational engines weren't all that optimized yet, and smart programmers with fast disks could beat the engine every time. In 1981 we sent the product back, and I didn't learn SQL until 1987. By then I was a bit behind on the learning curve, but relational engines were a heck of a lot faster, and programmers wrote a little SQL and much less procedural code. And they got much more work done. So I smile when I see folks shake their head about the XML data models or the XQuery Formal Semantics. I saw the same raised eyebrows when mixing object-oriented concepts and data first came on the scene. Maybe the head-shakers are right, but I'm not waiting until 2010 to learn XQuery. It doesn't matter whether you choose to wait, however, or use relational exclusively. Yukon and .NET 2.0 have the enabling engines for all these data storage and query technologies--and more. Bob Beauchemin Portland, Oregon, March 2004 Excerpted from A First Look at SQL Server 2005 for Developers: 'Yukon' Beta for Developers by Bob Beauchemin, Niels Berglund, Dan Sullivan All rights reserved by the original copyright owners. Excerpts are provided for display purposes only and may not be reproduced, reprinted or distributed without the written permission of the publisher.

Author notes provided by Syndetics

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.

Niels Berglund, Microsoft Valued Professional for OLE DB and ADO, teaches .NET and Microsoft data access for DevelopMentor. He has consulted widely on the development of distributed financial applications built with 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 has spoken and written widely on SQL Server.