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