SQL SERVER 2005 FOR DEVELOPERS
CONTENTS INTRODUCTION THE NET RUNTIME AND THE MICROSOFT PLATFORM NET’S EFFECTS ON SQL SERVER THE SQL: 1999 STANDARD—EXTENDING THE RELATIONAL MODEL USER-DE?NED TYPES AND SQL SERVER XML—DATA AND DOCUMENT STORAGE WEB SERVICES—XML AS A MARSHALING FORMAT SQL SERVER AS AN XML REPOSITORY AND WEB SERVICE SERVER 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 OBJECTSPACES—OBJECTS ON THE MIDDLE TIER OR CLIENT NOTI?CATION SERVICES WHERE ARE WE? HOSTING THE RUNTIME: SQL SERVER AS A RUNTIME HOST WHY CARE HOW HOSTING WORKS? WHAT IS A NET RUNTIME HOST? SQL SERVER AS A RUNTIME HOST RESOURCE MANAGEMENT EXCEPTIONAL CONDITION HANDLING CODE LOADING SECURITY LOADING THE RUNTIME—PROCESSES AND APPDOMAINS SAFE CODE—HOW THE RUNTIME MAKES IT SAFER TO RUN “FOREIGN CODE” HOST PROTECTION ATTRIBUTES (CREATE ASSEMBLY) (INFORMATION SCHEMA) SYSTEM METADATA TABLES AND INFORMATION_SCHEMA ASSEMBLY METADATA DROP ASSEMBLY) SPECI?CATION COMPLIANCE CONCLUSIONS PROCEDURES AND FUNCTIONS IN NET LANGUAGES EXTENDING SQL SERVER CLR METHODS IN SQL SERVER SYSTEM DATA SQLTYPES PROCEDURES TABLE-VALUED FUNCTIONS TRIGGERS THE IN-PROCESS DATA PROVIDER THE SQL SERVER PROGRAMMING MODEL CONTEXT—THE SQLCONTEXT CLASS CONNECTIONS COMMANDS—MAKING THINGS HAPPEN SQLEXECUTIONCONTEXT AND SQLDE?NITION OBTAINING RESULTS TRANSACTIONS WHEN THERE IS NO TRANSACTION IN THE CONTEXT USING A TRANSACTION IN THE CONTEXT STARTING A TRANSACTION WITH T-SQL COMMAND TEXT BEST PRACTICE FOR USING TRANSACTIONS IN SQLCLR STORED PROCEDURES TRANSACTION EXOTICA PIPE EXCEPTION HANDLING SQLTRIGGERCONTEXT USER-DEFINED TYPES AND AGGREGATES WHY DO WE NEED USER-DE?NED TYPES? ALIAS TYPES OVERVIEW OF USER-DE?NED TYPES CREATING A USER-DE?NED TYPE IMPLEMENTING A USER-DE?NED TYPE IMPLEMENTING LDIM IMPLEMENTING INULLABLE IMPLEMENTING THE NULL PROPERTY IMPLEMENTING TOSTRING IMPLEMENTING PARSE USER-DE?NED TYPE PROPERTIES AND FIELDS USER-DE?NED TYPE VALIDATION USER-DE?NED TYPE METHODS UNSUPPORTED FUNCTIONS IN USER-DE?NED TYPES XML SERIALIZATION MAINTAINING USER-DE?NED TYPE DE?NITIONS SHOULD OBJECTS BE REPRESENTED BY USER-DE?NED TYPES? USER-DE?NED AGGREGATES NEW SECURITY FEATURES IN SQL SERVER 2005 OPTIONAL FEATURES ARE TURNED OFF BY DEFAULT SECURITY AND METADATA 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 CREDENTIALS SEPARATION OF USERS AND SCHEMAS SYNONYMS SPECIFYING EXECUTION CONTEXT FOR PROCEDURAL CODE SQL SERVER PERMISSIONS AND THE NEW OBJECTS ASSEMBLY PERMISSIONS—WHO CAN CATALOG AND USE AN ASSEMBLY? AGGREGATES WHAT CAN NET CODE DO FROM WITHIN SQL SERVER: SAFETY LEVELS INTRODUCTION TO CODE ACCESS SECURITY T-SQL ENHANCEMENTS IMPROVEMENTS TO TRANSACT-SQL SNAPSHOT ISOLATION DRAWBACKS OF VERSIONING MONITORING VERSIONING STATEMENT-LEVEL RECOMPILATION DDL TRIGGERS EVENTDATA EVENT NOTI?CATIONS LARGE VALUE DATA TYPES T-SQL LANGUAGE ENHANCEMENTS TOP OUTPUT COMMON TABLE EXPRESSIONS AND RECURSIVE QUERIES APPLY OPERATORS PIVOT COMMAND USING PIVOT FOR ANALYSIS USING PIVOT FOR OPEN SCHEMAS RANKING AND WINDOWING FUNCTIONS TRANSACTION ABORT HANDLING XML IN THE DATABASE: THE XML DATA TYPE THE XML DATA TYPE USING THE XML DATA TYPE IN TABLES USING XML DATA VARIABLES AND PARAMETERS TYPED AND UNTYPED XML—CATALOGING AND USING XML SCHEMA COLLECTIONS SQL SERVER XML SCHEMA COLLECTIONS TYPED XML MANAGEMENT OF XML SCHEMAS AND SCHEMA COLLECTIONS XML TYPE FUNCTIONS SELECT FOR XML ENHANCEMENTS FOR XML PATH MODE PRODUCING AN XML DATA TYPE PRODUCING AN INLINE XSD FORMAT SCHEMA NULL DATABASE VALUES PRODUCING A ROOT ELEMENT OTHER FEATURES MAPPING SQL AND XML DATA TYPES MAPPING SQL TYPES TO XML TYPES STRING, BINARY, AND DECIMAL TYPES OTHER GENERAL DATA TYPES PATTERN-BASED DATA TYPES WILDCARD DATA TYPES NULLABILITY OPENXML ENHANCEMENTS USING XML BULK LOAD INSIDE THE DATABASE ANSI SQL STANDARD COMPLIANCE XML DATA TYPE MAPPING SQL CATALOGS AND TABLES TO XML MAPPING SQL DATA TYPES TO XML DATA TYPES XML QUERY LANGUAGES: XQUERY AND XPATH WHAT IS XQUERY? AN INTRODUCTION TO XQUERY THE XQUERY EXPRESSION BODY COMPARING AND CONTRASTING XQUERY AND SQL USING XQUERY WITH THE XML DATA TYPE XML EXIST(STRING XQUERY-TEXT) XML VALUE(STRING XQUERY-TEXT, STRING SQLTYPE) XML QUERY(STRING XQUERY-TEXT) XML NODES(STRING XQUERY-TEXT) SQL SERVER XQUERY FUNCTIONS AND OPERATORS SQL SERVER XQUERY EXTENDED FUNCTIONS MULTIPLE DOCUMENT QUERY IN SQL SERVER XQUERY XML DML—UPDATING XML COLUMNS XML MODIFY(‘INSERT ’) XML MODIFY(‘DELETE ’) XML MODIFY(‘REPLACE VALUE OF ’) GENERAL CONCLUSIONS AND BEST PRACTICES SPECIAL CONSIDERATIONS WHEN USING XQUERY INSIDE SQL SERVER USING STRONGLY TYPED XML WITH XQUERY OPTIMIZATION DECISIONS IN THE XQUERY ENGINE 10 SQL SERVER AS A PLATFORM FOR WEB SERVICES MIXING DATABASES AND WEB SERVICES HTTP ENDPOINT DECLARATION ENDPOINT STATE PARAMETERS THAT RELATE TO SERVING HTTP SQLXML 3 0 FUNCTIONALITY SECURITY CHOICES AND XML WEB SERVICES HTTP ENDPOINT PERMISSIONS DE?NING ACCESS THROUGH HTTP SOAP MESSAGES AND SQL BATCHES IN SQLXML 3 0 HTTP ENDPOINT METADATA VIEWS XML INPUT AND OUTPUT TYPES FROM SQL SERVER WEB SERVICES MESSAGE FORMAT CHOICES IN CREATE ENDPOINT PARAMETER HANDLING IN HTTP/SOAP CALLS ERROR HANDLING HTTP SESSION SUPPORT IN ENDPOINTS USING THE XML DATA TYPE AND WEB SERVICES WRITING A STORED PROCEDURE THAT GENERATES WSDL SQLXML 3 0 11 ADO AND ADO NET ENHANCEMENTS USER-DE?NED TYPES AND RELATIONAL DATA ACCESS APIS USING NET UDTS IN ADO NET FETCHING UDT DATA FROM A DATAREADER USING THE SQLMETADATA CLASS USING NET UDTS IN ODBC, OLE DB, AND ADO CLIENTS OLE DB AND ADO UPDATING A UDT OLE DB–SPECI?C CODING CONSIDERATIONS OLE DB AND ADO STANDARD SCHEMA TABLES SUPPORTING AND XML DATA TYPE IN ADO AND ADO NET CLIENTS USING THE XML DATA TYPE IN ADO NET USING THE XML DATA TYPE IN ADO CLASSIC USING THE NEW TYPES WITH THE NET DATASET AND SQLDATAADAPTER COMPARING THE CLIENT AND SERVER MODEL FOR STORED PROCEDURES 12 SQL CLIENT ENHANCEMENTS ADO NET 2 0 AND THE SQLCLIENT DATA PROVIDER MULTIPLE ACTIVE RESULTSETS NOTI?CATION SUPPORT HANDLING THE NOTI?CATION ASYNCHRONOUS SUPPORT SNAPSHOT ISOLATION PROMOTABLE TRANSACTIONS BULK IMPORT IN SQLCLIENT PASSWORD CHANGE SUPPORT CLIENT STATISTICS 13 CLIENT-SIDE XML: SQLXML AND MAPPING THE COMMON QUERY ABSTRACTION MAPPING BETWEEN DIFFERENT DATA MODELS XML QUERIES OVER XML VIEWS OF RELATIONAL DATA USING XQUERY COMMAND WITH SQL SERVER OR XML DOCUMENTS OPTIMIZING QUERIES CLIENT VERSUS SQL SERVER XQUERY FUNCTIONALITY SQLXML BULK LOAD AND THE SQLXMLROWSETADAPTER SQLXML DBOBJECT 14 OBJECTSPACES INTRODUCTION TO OBJECT-RELATIONAL MAPPING A SIMPLE OBJECTSPACES APPLICATION DATA MANIPULATION CLASSES IN THE OBJECTSPACES API ACCESSING A DATABASE WITH THE OBJECTSPACE PATTERNS AND MAPPING TYPE AND RELATIONSHIP IN THE MAPPING FILE MAPPING ELEMENTS AND ONE-TO-MANY RELATIONSHIPS MAPPING ZERO-OR-ONE-TO-ONE RELATIONSHIPS MAPPING MANY-TO-MANY RELATIONSHIPS IMPLEMENTING INHERITANCE IN A RELATIONAL DATABASE USING MAPPINGS ASYMMETRIC MAPPING AND OTHER COMMON MAPPING PATTERNS MANAGEMENT OF OBJECT IDS MAINTAINING OBJECT IDENTITY AND STATE READING OBJECTS WITH AN OBJECTREADER OBJECTSET THE OBJECTSPACES QUERY MODEL DATA TYPES FUNCTIONS AND PREDICATES MANIPULATING GRAPHS OF RELATED OBJECTS—OPTIMIZATIONS FETCHING OPTIMIZATIONS UPDATING OPTIMIZATIONS TRANSACTION OPTIMIZATIONS BEYOND THE OBJECTSPACE CLASS—CUSTOMIZATIONS 15 SQL SERVER SERVICE BROKER MESSAGING APPLICATIONS SQL SERVER SERVICE BROKER OVERVIEW SERVICE BROKER APPLICATIONS GUIDELINES SERVICE BROKER EXAMPLE MESSAGE TYPE CHANGING MESSAGE TYPES CONTRACTS QUEUES SERVICES DIALOGS CONVERSATION GROUP SERVICE PROGRAMS ACTIVATION SENDING AND RECEIVING MESSAGES FLOW IN A SERVICE PROGRAM ROUTES TRANSPORT SECURITY DIALOGS AND MESSAGES 16 NOTIFICATION SERVICES WHAT IS SQL SERVER NOTI?CATION SERVICES? NOTI?CATION APPLICATIONS NOTI?CATION APPLICATIONS DESIGN PATTERNS NOTI?CATION SERVICES DELIVERY FEATURES TERMS USED IN NOTI?CATION SERVICES DESIGNING, CODING, AND GENERATING A NOTI?CATION SERVICES APPLICATION PLANNING STEPS CODING STEPS APPLICATION GENERATION STEPS A SAMPLE NOTI?CATION APPLICATION INSTANCE AND APPLICATION CON?GURATION FILES INSTANCE CON?GURATION FILE APPLICATION DE?NITION FILE NSCONTROL EVENTS EVENT CLASSES EVENT STORED PROCEDURES EVENT PROVIDERS CHRONICLES SUBSCRIBERS AND SUBSCRIPTIONS SUBSCRIPTION CLASSES SUBSCRIBERS SUBSCRIPTIONS NOTI?CATIONS NOTI?CATION RULES DISTRIBUTOR AND FORMATTERS FORMATTERS DELIVERY DELIVERY PROTOCOLS CUSTOMIZATION 17 WRAP-UP: RELATIONS, XML, OBJECTS, AND SERVICES LOTS OF NEW FEATURES DATA MODELS, PROGRAMMING, AND SQL SERVER SO WHICH TIER AND WHAT DATA MODEL? THE DATABASE AS PART OF THE PLATFORM COMPLIANCE, USER-DE?NED TYPES (UDTS), AND XML INTEGRATION TO PRESENT A PICTURE OF HOLISTIC DATA STORAGE, MANIPULATION, AND ACCESS THE NET RUNTIME AND THE MICROSOFT PLATFORM NET IS MICROSOFT’S LATEST ENVIRONMENT FOR RUNNING PROGRAM CODE THE CONCEPT OF MANAGED CODE, RUNNING UNDER CONTROL OF AN EXECUTION ENGINE, HAS QUICKLY PERMEATED ALL MAJOR OPERATING SYSTEMS, INCLUDING THOSE FROM MICROSOFT NET IS ONE OF THE CORE TECHNOLOGIES IN WINDOWS SERVER 2003, MICROSOFT’S LATEST COLLECTION OF SERVER PLATFORMS HANDHELD DEVICES AND COMPUTER-BASED MOBILE PHONES HAVE QUICKLY ACQUIRED NET-BASED DEVEL- OPMENT ENVIRONMENTS NET IS AN INTEGRAL PART OF BOTH INTERNET INFORMA- TION SERVER (IIS) AND INTERNET EXPLORER (IE) ASP NET RUNS ON THE WINDOWS 2000 VERSION AND UP OF IIS 5 0; IE 5 5 AND UP CAN LOAD AND RUN NET CODE REFERENCED BY <OBJECT> TAGS EMBEDDED IN WEB PAGES RICH NET-BASED WINDOWS APPLICATIONS, BASED ON THE WINFORMS LIBRARY THAT COMES WITH NET, MAY BE DEPLOYED DIRECTLY FROM THE INTERNET AND RUN ON WINDOWS- BASED DESKTOPS SO WHAT IS IT ABOUT NET THAT HAS CAUSED IT TO CATCH ON?