Figures

Figure 1-1: SQL Server architecture, version 7 and above 4

Figure 1-2: Preventing managed code from interfering with SQL Server processing or writing over buffers 7

Figure 1-3: Complex types in otherwise-relational tables 9

Figure 1-4: Messages and dialogs 25

Figure 2-1: SQLOS services 29

Figure 2-2: Hosting the CLR 32

Figure 2-3: SQL Server 2005 unhandled exception escalation policy 37

Figure 2-4: .NET Framework marshaling choices. The default is to not allow marshaling 41

Figure 2-5: AppDomains in SQL Server databases 42

Figure 3-1: Pulley system 64

Figure 3-2: Pulley distance calculation 65

Figure 3-3: Create function 65

Figure 3-4: Building assembly 67

Figure 3-5: Using a CLR function 68

Figure 3-6: INFORMATION_SCHEMA.ROUTINES 69

Figure 3-7: T-SQL PulleyDistance 69

Figure 3-8: PulleyDistance comparison 70

Figure 3-9: T-SQL SSN validation 70

Figure 3-10: Using the ValidateSSNTSQL function 71

Figure 3-11: CLR-Based validation 71

Figure 3-12: Using ValidateSNN 72

Figure 3-13: Improved SSN validation 72

Figure 3-14: Deterministic function 75

Figure 3-15: SqlFunction usage 76

Figure 3-16: Questionable result produced by NULL 77

Figure 3-17: Null values in T-SQL and C# 78

Figure 3-18: Visual Basic and C# treatment of unassigned variable 79

Figure 3-19: T-SQL–like SSN validation 80

Figure 3-20: Using types in T-SQL 83

Figure 3-21: Casting types 83

Figure 3-22: CLR conversions 85

Figure 3-23: Converting SqlString 86

Figure 3-24: SqlTypes overflow 86

Figure 3-25: SqlDecimal limits 87

Figure 3-26: SqlTypes addition 88

Figure 3-27: Using SqlBoolean 90

Figure 3-28: Comparing nulls 90

Figure 3-29: Using system.object 91

Figure 3-30: Processing system.object 91

Figure 3-31: T-SQL function calculation properties 94

Figure 3-32: CLR function calculation properties 95

Figure 3-33: SqlFunction usage 97

Figure 3-34: Nested class 98

Figure 3-35: Method overload 98

Figure 3-36: Function output error 99

Figure 3-37: Simple enumeration 100

Figure 3-38: Log ranges 101

Figure 3-39: Log-Range table 102

Figure 3-40: Range class 102

Figure 3-41: GetRow method 103

Figure 3-42: CLR table-valued function 104

Figure 3-43: Using LogRange 105

Figure 3-44: Summary of a table-valued function 106

Figure 3-45: CLR stored procedure 107

Figure 3-46: Loading stored procedure 108

Figure 3-47: Using stored procedure 108

Figure 3-48: CLR trigger 109

Figure 3-49: Loading trigger 109

Figure 4-1: The SqlClient provider object model 113

Figure 4-2: Connections from extended stored procedures 116

Figure 4-3: Interop between .NET framework and SQL Server code in process 118

Figure 4-4: Building a Web Service proxy and cataloguing it to SQL Server 142

Figure 4-5: Commenting out the asynchronous methods in a WSDL.exe–generated proxy 144

Figure 5-1: Date as binary number 155

Figure 5-2: Using a user-defined type 156

Figure 5-3: Comparison of numeric results 157

Figure 5-4: Basis for LDim 158

Figure 5-5: Skeleton of LDim implementation 159

Figure 5-6: Using NULL 160

Figure 5-7: Implementation of null 161

Figure 5-8: Using string 161

Figure 5-9: Implementation of ToString 162

Figure 5-10: Regular expression for validation 162

Figure 5-11: Implementation of parse 163

Figure 5-12: Checking range and accuracy 164

Figure 5-13: Format=Native data types 166

Figure 5-14: SqlUserDefinedType for LDim 167

Figure 5-15: Sort by byte 167

Figure 5-16: Trivial implementation of write 168

Figure 5-17: Comparison of numeric and byte-ordered sort 168

Figure 5-18: Implementation of write and read 169

Figure 5-19: Encoding of LDim 169

Figure 5-20: Creating a user-defined type 170

Figure 5-21: Casting LDim to VARCHAR 171

Figure 5-22: Selecting and sorting user-defined types 172

Figure 5-23: UDT and comparison 172

Figure 5-24: Inches property 173

Figure 5-25: Using SqlFacet 174

Figure 5-26: Selecting the inches property 175

Figure 5-27: Updating the property 175

Figure 5-28: Using methods, fields, and properties 176

Figure 5-29: User-defined-type mutator method 178

Figure 5-30: Using ScaleBy 178

Figure 5-31: User-defined-type nonmutator method 179

Figure 5-32: NULL user-defined type 179

Figure 5-33: Using GetScaled 180

Figure 5-34: Using method on null instance 180

Figure 5-35: Comparing iS NULL to IsNull 181

Figure 5-36: Ensuring that IsNull works as expected 182

Figure 5-37: Implementing add 182

Figure 5-38: Using add 183

Figure 5-39: Client program using LDimAdd 183

Figure 5-40: Invalid LDim 184

Figure 5-41: Range error 184

Figure 5-42: Validation method 185

Figure 5-43: Validation error 185

Figure 5-44: Casting a user-defined type to and from XML 187

Figure 5-45: Using FOR XML 188

Figure 5-46: Using SGen 189

Figure 5-47: Using an assembly created by SGen 190

Figure 5-48: Built-in XML serialization 190

Figure 5-49: XmlIgnore attribute 191

Figure 5-50: WriteXml 192

Figure 5-51: ReadXml 193

Figure 5-52: Answering the “between” question for dates 194

Figure 5-53: Answering the “between” question for points 195

Figure 5-54: Using the SUM aggregate function 207

Figure 5-55: Calculating a sum without an aggregate 208

Figure 5-56: Product aggregate 208

Figure 5-57: Using a user-defined aggregate 210

Figure 5-58: SumLDim implementation skeleton 210

Figure 5-59: Representing decimal 213

Figure 5-60: Implementation of init 213

Figure 5-61: Implementation of accumulate 214

Figure 5-62: Implementation of merge 214

Figure 5-63: Implementation of terminate 215

Figure 5-64: Loading a user-defined aggregate 215

Figure 6-1: Surface area configuration for features 223

Figure 6-2: Principals, permissions, and securables in SQL Server 2005 225

Figure 6-3: Defining database users for SQL Server logins 227

Figure 6-4: An ownership chain. BOB can access the table only through the stored procedure 233

Figure 6-5: If FRED’s stored procedure accesses ALICE’s table, this breaks the ownership chain 234

Figure 6-6: Dynamic SQL will always break an ownership chain 254

Figure 6-7: Using EXECUTE AS SELF with dynamic SQL 257

Figure 7-1: Snapshot versioning 279

Figure 7-2: Recompile 302

Figure 7-3: Capturing an XML showplan for use with plan forcing 308

Figure 8-1: @@ERROR error handler 313

Figure 8-2: TRY/CATCH error handler 314

Figure 8-3: BadInsert stored procedure 315

Figure 8-4: Catching error 316

Figure 8-5: Error handler output 316

Figure 8-6: Uncommittable transaction 318

Figure 8-7: Handling uncommittable transaction 318

Figure 8-8: Reraising error 319

Figure 8-9: Retrying transaction 322

Figure 8-10: Testing reraise 326

Figure 8-11: Publisher states with no authors 326

Figure 8-12: Equivalent EXCEPT syntax 327

Figure 8-13: Author states with no publishers 328

Figure 8-14: States with an author and publisher 328

Figure 8-15: Wrong ways to find publisher names 329

Figure 8-16: Publisher names from states without authors 329

Figure 8-17: Relative execution plans 330

Figure 8-18: Using INTO clause 330

Figure 8-19: TOP value as a parameter 331

Figure 8-20: Selecting top publishers 331

Figure 8-21: Top titles, including ties 332

Figure 8-22: Delete in blocks 332

Figure 8-23: Departments and employees 334

Figure 8-24: Using ON UPDATE and ON DELETE 335

Figure 8-25: Effects of ON UPDATE and ON DELETE 335

Figure 8-26: Obtaining identity 337

Figure 8-27: Typical usage of OUTPUT 338

Figure 8-28: Obtaining identities from bulk insert 339

Figure 8-29: Wildcard usage 339

Figure 8-30: Using OUTPUT INTO 340

Figure 8-31: Cartesian product 340

Figure 8-32: JOIN cartesian product 341

Figure 8-33: CROSS APPLY 342

Figure 8-34: OUTER APPLY 342

Figure 8-35: Using a table-valued function with CROSS APPLY 343

Figure 8-36: CTE syntax 343

Figure 8-37: Expanded CTE 344

Figure 8-38: CTE usage 345

Figure 8-39: Equivalent subquery 345

Figure 8-40: Salesperson IDs for orders for ProductID 744 346

Figure 8-41: Big Boss report using subquery 346

Figure 8-42: Report for Big Boss using a CTE 347

Figure 8-43: Expensive orders using subquery 348

Figure 8-44: Expensive order report 349

Figure 8-45: Expensive orders using CTE 349

Figure 8-46: Execution plan comparison 350

Figure 8-47: Chart of accounts 351

Figure 8-48: Building a chart of accounts 352

Figure 8-49: Account 1000 balance 353

Figure 8-50: Phases of calculation 354

Figure 8-51: MAXRECURSION 355

Figure 8-52: Limiting recursion 356

Figure 8-53: Tree rollup function 356

Figure 8-54: Rollup for employee 273 357

Figure 8-55: Ancestor function 358

Figure 8-56: Employee ancestors 358

Figure 8-57: Using tree functions 359

Figure 8-58: Sales recorded as entered 360

Figure 8-59: Q1 results, year over year 360

Figure 8-60: Query for quarterly results 361

Figure 8-61: All quarterly results 361

Figure 8-62: PIVOT operator 362

Figure 8-63: PIVOT comparison with hand-built query 362

Figure 8-64: PIVOT with id 363

Figure 8-65: Expanded PIVOT 364

Figure 8-66: PIVOT with extra columns 364

Figure 8-67: PIVOT with headings 365

Figure 8-68: UNPIVOT operator 366

Figure 8-69: UNPIVOTing a row 367

Figure 8-70: ROW_NUMBER function 368

Figure 8-71: Referencing a ROW_NUMBER directly 369

Figure 8-72: Referencing a ROW_NUMBER via subquery 369

Figure 8-73: Shipping priority 370

Figure 8-74: DENSE_RANK 371

Figure 8-75: NTILE 371

Figure 8-76: DENSE_RANK via subquery 372

Figure 8-77: Ranking within an order 373

Figure 8-78: Sum across partitions 374

Figure 8-79: PARTITION/GROUP BY comparison 375

Figure 8-80: No column restrictions with OVER 375

Figure 8-81: Multiple partitions 376

Figure 8-82: Approximate calculation 377

Figure 8-83: Row count variance 378

Figure 8-84: TABLESAMPLE on derived table 378

Figure 8-85: Count discrepancy 379

Figure 8-86: Repeatable TABLESAMPLE 380

Figure 8-87: ROWS clause 380

Figure 9-1: XSD base data types 393

Figure 10-1: SQL Server 2005 internals 431

Figure 10-2: XML document illustrating document order 435

Figure 10-3: XPath axes 440

Figure 10-4: SQL SELECT and FLWOR 446

Figure 11-1: Business transaction 483

Figure 11-2: Creating InspectionService 486

Figure 11-3: Services and queues metadata 487

Figure 11-4: BEGIN DIALOG CONVERSATION 487

Figure 11-5: Conversation endpoints 489

Figure 11-6: Sending message 490

Figure 11-7: Contents of sys.transmission_queue 491

Figure 11-8: CentralMaintenanceService 492

Figure 11-9: CentralMaintenanceQueue 493

Figure 11-10: NVARCHAR(MAX) message_body 493

Figure 11-11: Receiving a single message 495

Figure 11-12: Results of processing messages 496

Figure 11-13: Activation procedure 506

Figure 11-14: Continuous message processing 506

Figure 11-15: Enabling activation procedure 507

Figure 11-16: Enabling queue 508

Figure 11-17: Conversation preserving order 510

Figure 11-18: Normal END CONVERSATION 511

Figure 11-19: END CONVERSATION with error 512

Figure 11-20: Dialog lifetime 514

Figure 11-21: Conversation timer 515

Figure 11-22: Preserving processing order 517

Figure 11-23: Adding conversation to conversation group 519

Figure 11-24: Conversation group locking 520

Figure 11-25: Conversation message processing 520

Figure 11-26: Conversation groups across multiple queues 521

Figure 11-27: RECEIVE returning multiple rows 523

Figure 11-28: CREATE MESSAGE TYPE 524

Figure 11-29: XML documents 526

Figure 11-30: XML Schema and message 527

Figure 11-31: Message types 528

Figure 11-32: Contract 528

Figure 11-33: Contracts view 530

Figure 11-34: Supporting contracts 530

Figure 11-35: Conversation contract 531

Figure 11-36: Message types in queue 531

Figure 11-37: SEND and message type 532

Figure 11-38: Sending malformed message 532

Figure 11-39: Using CONVERT with DTD 534

Figure 11-40: Message processing loop 535

Figure 11-41: Retention 542

Figure 11-42: Retention of sent message 542

Figure 11-43: Retention of received message 543

Figure 11-44: Service broker instances 545

Figure 11-45: Service broker instance 546

Figure 11-46: Default routing table 547

Figure 11-47: Route assignment 549

Figure 11-48: Routing in msdb 550

Figure 11-49: Windows logon 551

Figure 11-50: Connections identities 551

Figure 11-51: Endpoint for Klamath 552

Figure 11-52: Endpoints 553

Figure 12-1: httpcfg 568

Figure 12-2: CREATE ENDPOINT 569

Figure 12-3: Mapping of AS HTTP to URL 570

Figure 12-4: URL reserved by SQL Server 571

Figure 12-5: Surface area configuration tool 572

Figure 12-6: Endpoint metadata 572

Figure 12-7: Mapping Webmethod to function 573

Figure 12-8: Visual studio adding Web Reference 574

Figure 12-9: Visual studio making proxy 575

Figure 12-10: Using a proxy 576

Figure 12-11: Estimate 577

Figure 12-12: WSDL generator 579

Figure 12-13: Using Internet Explorer 597

Figure 12-14: GetEstimate schema 598

Figure 12-15: Definition of nvarchar 599

Figure 12-16: Element for GetEstimate 600

Figure 12-17: Proxy for ?WSDLComplex 600

Figure 12-18: GetEstimateResponse schema 601

Figure 12-19: Proxy for ?WSDLSimple 603

Figure 12-20: Namespaces 604

Figure 12-21: HTTP Endpoint information 609

Figure 12-22: Using URL function 611

Figure 12-23: SqlResultStream 619

Figure 12-24: Result of processing SqlResultStream 622

Figure 12-25: Result of processing sqlBatchResponse 626

Figure 13-1: Network library functionality in ADO.NET 2.0 SqlClient and in SQL Native Client 643

Figure 13-2: Using SqlNotification request 684

Figure 13-3: Using query notifications with the ASP.NET OutputCache directive 686

Figure 13-4: MARS multiplexes resultsets over the same connection 690

Figure 13-5: Command execution between sessions is not guaranteed to be serialized 694

Figure 14-1: Layout of the SqlDataAdapter 754

Figure 14-2: Supported types in the ADO.NET DataSet 755

Figure 15-1: ManagedComputer and server 762

Figure 15-2: SMO object model 763

Figure 15-3: Listing databases with ADO.NET 765

Figure 15-4: Running ADOList program 767

Figure 15-5: SMO program to list databases 767

Figure 15-6: Monad script to list databases 768

Figure 15-7: Monad IntelliSense 768

Figure 15-8: Basic SMO hierarchy 769

Figure 15-9: SMO object explorer 770

Figure 15-10: ToolTip for SqlSmoObject 771

Figure 15-11: Code for SMO object explorer 772

Figure 15-12: Database lister application 773

Figure 15-13: Starting a Windows project 774

Figure 15-14: Naming a Windows project 774

Figure 15-15: Adding assembly references 775

Figure 15-16: Selecting assembly reference 775

Figure 15-17: Adding a list box 776

Figure 15-18: Form for database lister application 776

Figure 15-19: Button handler 777

Figure 15-20: Connecting to SQL Server 778

Figure 15-21: Default connection 779

Figure 15-22: DefaultSMOConnection program 779

Figure 15-23: Using default connection 780

Figure 15-24: Windows test identities 780

Figure 15-25: Using Runas utility 781

Figure 15-26: Automatic connection 781

Figure 15-27: Connection upon property access 782

Figure 15-28: ImpersonateConnect program 783

Figure 15-29: Impersonating Windows identity 783

Figure 15-30: SQLConnect program 784

Figure 15-31: SQL Server login 785

Figure 15-32: SQL connection by constructor 785

Figure 15-33: Changing a password 786

Figure 15-34: Server constructed with ServerConnection 788

Figure 15-35: URN format 791

Figure 15-36: Direct object access 794

Figure 15-37: Direct object access via string 794

Figure 15-38: Default server 795

Figure 15-39: Using wrong server to get URN 796

Figure 15-40: Exception due to mismatched server 796

Figure 15-41: Using statically and dynamically referenced properties 797

Figure 15-42: FillPropertyList for filling ListBox with properties dynamically 798

Figure 15-43: Program using dynamic properties 799

Figure 15-44: SMO parent 801

Figure 15-45: Collection indexer 801

Figure 15-46: Pseudocode for the implementation of Create 803

Figure 15-47: Database.Create 804

Figure 15-48: Altering a database 804

Figure 15-49: Dropping a database 805

Figure 15-50: Creating a database 805

Figure 15-51: Database with Table and Columns 806

Figure 15-52: Changing a database 806

Figure 15-53: Database after changes 807

Figure 15-54: Scripting a table 808

Figure 15-55: Customer create script 808

Figure 15-56: ScriptingOptions 809

Figure 15-57: Customer drop script 809

Figure 15-58: Scripting multiple objects 810

Figure 15-59: Script to create objects 810

Figure 15-60: Scripting with dependencies 811

Figure 15-61: Script with dependencies 811

Figure 15-62: FilterCallback 812

Figure 15-63: Script in dependency order 813

Figure 15-64: Depth first dependency tree 814

Figure 15-65: Dependency tree 814

Figure 15-66: Capturing SQL 815

Figure 15-67: Results of CaptureSql 816

Figure 15-68: Enumerating client protocols 816

Figure 15-69: Client protocols on CANOPUS5 817

Figure 15-70: Managing services 817

Figure 16-1: An overview of SQL Server Notification Services 823

Figure 16-2: One application per instance versus multiple applications per instance 827

Figure 16-3: Financial application 834

Figure 16-4: Subscription management 859

Figure A-1: The CLR programming model 898

Figure A-2: Compilation of code into IL 900

Figure A-3: Screenshot of ILDasm 902

Figure A-4: The CLR type system 907

Figure B-1: SSMS main screen 924

Figure B-2: SSMS solution 925

Figure B-3: Summary of local instance of SQL server 926

Figure B-4: Configuration options 926

Figure B-5: Options dialog box 927

Figure B-6: Empty environment 927

Figure B-7: Making new project and solution 928

Figure B-8: New project dialog box 928

Figure B-9: New project 929

Figure B-10: Directories and files for new project 929

Figure B-11: Making new connection 930

Figure B-12: Connect to server dialog box 930

Figure B-13: Connection in tree 931

Figure B-14: Connection Properties 931

Figure B-15: Adding query 932

Figure B-16: New query 932

Figure B-17: Changing query connection 933

Figure B-18: Query results 933

Figure B-19: Selecting query designer 934

Figure B-20: Query designer 934

Figure B-21: Adding table 935

Figure B-22: Add table dialog box 935

Figure B-23: Table added to query designer 936

Figure B-24: Selected query 936

Figure B-25: Adding miscellaneous file 937

Figure B-26: Excel spreadsheet added to project 937

Figure B-27: Selecting template explorer 938

Figure B-28: Template explorer 938

Figure B-29: Dragging in template 939

Figure B-30: Dropped template 939

Figure B-31: Specify values for template parameters command 940

Figure B-32: Filling in template parameters 940

Figure B-33: Script with parameters filled in 941

Figure B-34: Adding a new template 941

Figure B-35: Template added to tree 942

Figure B-36: Selecting a template to edit 942

Figure B-37: Template parameter mapping 943

Figure B-38: Parameter name used twice 943

Figure B-39: Result of using same parameter name twice 943

Figure B-40: Object explorer command 944

Figure B-41: Service-Connection menu 945

Figure B-42: Explorer tree 945

Figure B-43: Typical objects 946

Figure B-44: Object menu 946

Figure B-45: Executing a stored procedure 947

Figure B-46: Summary window 947

Figure B-47: Scripting multiple objects 948

Figure C-1: Choosing to create a new project 951

Figure C-2: C# Database project 952

Figure C-3: Add database reference 953

Figure C-4: Server and solution explorers 953

Figure C-5: Enabling debugging 954

Figure C-6: Solution explorer in a C# SQL server project 955

Figure C-7: Adding user-defined function 955

Figure C-8: Code skeleton 956

Figure C-9: Attach to process dialog box 962