Contents
The .NET Framework and the Microsoft Platform
The .NET Framework’s Effects on SQL Server
The SQL:1999 Standard: Extending the Relational Model
User-Defined Types and SQL Server
XML: Data and Document Storage
Web Services: XML As a Marshaling Format
Client Access . . . And Then There Are Clients
Client-Side Database APIs and SQL Server 2005
Client-Side XML-Based APIs and SQL Server 2005 Integration
Extending SQL Server into the Platform: Service Broker and Notification Services
2. Hosting the Runtime: SQL Server As a Runtime Host
What Is a .NET Framework Runtime Host?
SQL Server Resource Management
Exceptional Condition Handling
Loading the Runtime: Processes and AppDomains
Safe Code: How the Runtime Makes It Safer to Run “Foreign” Code
Where the Code Lives: Storing .NET Framework Assemblies (CREATE ASSEMBLY)
Assembly Dependencies: When Your Assemblies Use Other Assemblies
Assemblies and SQL Schemas: Who Owns Assemblies (Information Schema)
System Metadata Tables and INFORMATION_SCHEMA
Maintaining User Assemblies (ALTER ASSEMBLY, DROP ASSEMBLY)
3. Procedures and Functions in .NET CLR Languages
Commands: Making Things Happen
Creating and Sending New Rowsets
Calling a Web Service from SQLCLR
SqlClient Classes That You Can’t Use on the Server
5. User-Defined Types and Aggregates
Why Do We Need User-Defined Types?
Overview of User-Defined Types
Public Properties, Fields, and Methods
Maintaining User-Defined-Type Definitions
Should Objects Be Represented by User-Defined Types?
Implementing a User-Defined Aggregate
Creating User-Defined Aggregate
Format.Native vs. Format.UserDefined
New Security Features in SQL Server 2005
Optional Features Are Turned Off by Default
A Quick Review of SQL Server Security Concepts with Enhancements
Authentication and Authorization: Principals and Permissions
Execution Context and Ownership Chaining
SQL Server Password Policies and Credentials
Encryption Keys and Built-In Encryption Functions
Separation of Users and Schemas
Specifying Execution Context for Procedural Code
SQL Server Permissions and SQLCLR Objects
Assembly Permissions: Who Can Catalog and Use an Assembly?
What .NET Framework Code Can Do from within SQL Server: Safety Levels
Introduction to Code Access Security
Code Access Security and .NET Framework Assemblies
Improvements to the SQL Engine
Data Definition Language Triggers
Loading Data with the New BULK Provider
Query Hints, Plan Guides, and Plan Forcing
8. T-SQL Language Enhancements
9. XML in the Database: The XML Data Type
Using XML Data Variables and Parameters
Typed and Untyped XML: Cataloguing and Using XML SCHEMA COLLECTIONs
SQL Server XML SCHEMA COLLECTIONs
Management of XML Schemas and Schema Collections
Security for XML SCHEMA COLLECTIONS and Strongly Typed Instances
Creating an Index on an XML Column
SELECT . . . FOR XML Enhancements
Producing an Inline XSD Format Schema
Mapping SQL and XML Data Types
Mapping SQL Types to XML Types
String, Binary, and Decimal Types
Loading XML into the Database from Files
Mapping SQL Catalogs and Tables to XML
Mapping SQL Data Types to XML Data Types
10. XML Query Languages: XQuery and XPath
Comparing and Contrasting XQuery and SQL
Using XQuery with the XML Data Type
xml.value(string xquery-text, string SQLType)
XQuery Standard Functions and Operators
SQL Server XQuery Functions and Operators
XQuery Functions Supported by SQL Server
Constructors and Functions on Booleans
XQuery Operators Supported by SQL Server
SQL Server XQuery Extended Functions
Multiple-Document Query in SQL Server XQuery
xml.modify(‘replace value of . . .’)
General Conclusions and Best Practices
Special Considerations When Using XQuery Inside SQL Server
XML Schemas and SQL Server 2005 XQuery
12. SQL Server As a Platform for Web Services
Mixing Databases and Web Services
WSDL Web Service Definition Language
Stored Procedure in Web Service
SQLXML 4.0 Functionality and SQL Server 2005
13. SQL Server 2005 and Clients
New Data Types and Data Type Compatibility Mode
User-Defined Types and Relational Data Access APIs
Using .NET Framework UDTs in ADO.NET
Fetching UDT Data from a DataReader
Using .NET Framework UDTs in ODBC, OLE DB, and ADO Clients
Supporting the XML Data Type on the Client
Using the XML Data Type in ADO.NET
Getting the XML As XML or a String
Documents, Fragments, and FOR XML Support
Using the XML Data Type in ADO Classic
Supporting the Large Value Data Types on the Client
Query Notifications in SQL Server 2005
Using Query Notifications in OLE DB and ODBC
Dispatching a Notification to an End User or Cache
Using Query Notifications from a Database Client
Using SqlCacheDependency in ASP.NET
New Transaction and Isolation Features in ADO.NET
Using the New Isolation Levels
Promotable and Declarative Transactions
Changes Related to SQL Server 2005 Login
Comparing the Client and Server Model for Stored Procedures
Generic Coding with the ADO.NET 2.0 Base Classes and Factories
Specifying Configuration Information
Enumerating Data Sources and Building Connection Strings
Other Generic Coding Considerations
Customizing and Extending the Metadata
Harvest the Results As a CSV File
Configuring Which Applications Are Traced
Using Tracing to Debug a Parameter Binding Problem
.NET Framework DataSet and SqlDataAdapter Enhancements
15. SQL Server Management Objects
Default Windows Credentials Connection
Windows Identity Impersonation
Changing SQL Server Login Password
What Is SQL Server Notification Services?
Components of SQL Server Notification Services
Notification Applications Design Patterns
Notification Services Delivery Features
Terms Used in Notification Services
Designing, Coding, and Generating a Notification Services Application
A Sample Notification Application
Instance and Application Configuration Files
17. Wrap-Up: Service-Oriented Database Applications
Lots of New Features: How to Use Them
Data Models, Programming, and SQL Server
Toward a Service-Oriented Database Architecture
The Database As Part of the Platform
Appendixes
B. SQL Server Management Studio
C. Visual Studio 2005 Integration: SQL Server Projects
Custom Attributes and Automatic Deployment