Table of Contents
Foreword xv
Acknowledgments xvii
Introduction xxi
1 Views 1
What Are Views? 1
ORDER BY in a View 3
Refreshing Views 6
Modular Approach 8
Updating Views 16
View Options 20
ENCRYPTION 20
SCHEMABINDING 20
CHECK OPTION 22
VIEW_METADATA 23
Indexed Views 24
Conclusion 29
2 User-Defned Functions 31
Some Facts About UDFs 31
Scalar UDFs 32
T-SQL Scalar UDFs 32
Performance Issues 34
UDFs Used in Constraints 40
CLR Scalar UDFs 43
SQL Signature 57
Table-Valued UDFs 63
Inline Table-Valued UDFs 64
Split Array 66
vi Table of Contents
ORDER Option for CLR Table-Valued UDFs 71
Multistatement Table-Valued UDFs 73
Per-Row UDFs 77
Conclusion 80
3 Stored Procedures 81
Types of Stored Procedures 81
User-Defned Stored Procedures 82
Special Stored Procedures 85
System Stored Procedures 87
Other Types of Stored Procedures 89
The Stored Procedure Interface 90
Scalar Input Parameters 90
Table-Valued Parameters 92
Output Parameters 95
Resolution 97
Dependency Information 98
Compilations, Recompilations, and Reuse of Execution Plans 101
Reuse of Execution Plans 102
Recompilations 107
Variable Sniffng 111
Plan Guides 118
EXECUTE AS 129
Parameterizing Sort Order 130
CLR Stored Procedures 136
Conclusion 144
4 Triggers 145
AFTER Triggers 146
The inserted and deleted Special Tables 146
Identifying the Number of Affected Rows 147
Identifying the Type of Trigger 151
Not Firing Triggers for Specifc Statements 152
Nesting and Recursion 156
UPDATE and COLUMNS_UPDATED 158
Auditing Example 160
INSTEAD OF Triggers 163
Per-Row Triggers 164
Used with Views 167
Automatic Handling of Sequences 170
Table of Contents vii
DDL Triggers 172
Database-Level Triggers 173
Server-Level Triggers 178
Logon Triggers 180
CLR Triggers 181
Conclusion 190
5 Transactions and Concurrency 191
What Are Transactions? 192
Locking and Blocking 194
Lock Escalation 199
Isolation Levels 200
Read Uncommitted 202
Read Committed 204
Repeatable Read 205
Serializable 206
Row Versioning–Based Isolation Levels 208
Savepoints 214
Deadlocks 216
Simple Deadlock Example 217
Deadlock Caused by Missing Indexes 218
Deadlock with a Single Table 221
Conclusion 223
6 Error Handling 225
Error Handling without the TRY/CATCH Construct 225
Error Handling with the TRY/CATCH Construct 229
TRY/CATCH 229
Error-Handling Functions 231
Errors in Transactions 233
Conclusion 244
7 Temporary Tablesand Table Variables 245
Temporary Tables 246
Local Temporary Tables 246
Global Temporary Tables 257
Table Variables 259
Limitations 260
tempdb 260
Scope and Visibility 261
viii Table of Contents
Transaction Context 261
Statistics 262
Minimally Logged Inserts 265
tempdb Considerations 268
Table Expressions 269
Comparison Summary 270
Summary Exercises 271
Comparing Periods 272
Recent Orders 274
Relational Division 278
Conclusion 283
8 Cursors 285
Using Cursors 285
Cursor Overhead 287
Dealing with Each Row Individually 289
Order-Based Access 290
Custom Aggregates 291
Running Aggregations 292
Maximum Concurrent Sessions 300
Matching Problems 308
Conclusion 314
9 Dynamic SQL 315
EXEC 317
Simple EXEC Examples 317
EXEC Has No Interface 318
Concatenating Variables 321
EXEC AT 322
sp_executesql 326
The sp_executesql Interface 326
Statement Limit 330
Environmental Settings 331
Uses of Dynamic SQL 331
Dynamic Maintenance Activities 331
Storing Computations 333
Dynamic Filters 338
Dynamic PIVOT/UNPIVOT 349
Table of Contents ix
SQL Injection 364
SQL Injection: Code Constructed Dynamically at Client 364
SQL Injection: Code Constructed Dynamically at Server 365
Protecting Against SQL Injection 370
Conclusion 373
10 Working with Date and Time 375
Date and Time Data Types 375
Date and Time Manipulation 378
Date and Time Functions 378
Literals 383
Identifying Weekday 385
Handling Date-only or Time-only Data Prior to SQL Server 2008 387
Examples of Date and Time Calculations 388
Rounding Issues 393
Date- and Time-Related Querying Problems 395
Age Problems 395
Overlaps 399
Grouping by the Week 404
Working Days 405
Generating a Series of Dates 406
Conclusion 407
11 CLR User-Defned Types 409
Theoretical Introduction to UDTs 409
Domains and Relations 409
Domains and Classes 412
Complex Domains 412
Why Do We Need Complex Classes? 415
Language for Creating UDTs 416
Programming a UDT 416
UDT Requirements 417
Creating a UDT 419
Deploying the UDT Using T-SQL 425
Conclusion 437
12 Temporal Support in the Relational Model 439
Timestamped Predicates and Propositions 440
Time Points 441
Time Points Lookup Table 442
Semitemporal Problems 443
Semitemporal Constraints 443
Testing Semitemporal Constraints 445
Queries on Semitemporal Tables 446
Tables with Full Temporal Support 446
The IntervalCID UDT 448
Testing IntervalCID 460
Full Temporal Tables Using IntervalCID 464
Testing Full Temporal Constraints 467
Queries on Tables with Full Temporal Support 468
Unpack and Pack 470
Expanded and Collapsed Forms of Interval Sets 473
The UNPACK Operator 474
PACK Operator 476
Sixth Normal Form in Use 479
Horizontal and Vertical Decompositions 479
Sixth Normal Form 487
Conclusion 488
13 XML and XQuery 491
Converting Relational Data to XML and Vice Versa 491
Introduction to XML 491
Producing XML from Relational Data 495
Shredding XML to Tables 502
The XQuery Language in SQL Server 2008 505
XQuery Basics 506
Navigation 510
Iteration and Returning Values 516
XML Data Type 521
XML Support in a Relational Database 521
When Should You Use XML Instead of Relational
Representation? 523
XML Serialized Objects in a Database 525
XML as a Stored Procedure Parameter 535
Dynamic Relational Schema 536
Relational Solutions 536
Object-Oriented Solutions 537
Using the XML Data Type for Dynamic Schema 538
Conclusion 542
14 Spatial Data 543
Introduction to Spatial Data 543
Basic Spatial Data Concepts 544
Vector Data and the OGC Simple Features Type Model 544
Planar and Geographic Coordinates 546
Spatial Reference Identifers 548
Standards 549
Working on the Ellipsoid 549
Data 550
Common Forms of Data 551
Finding Spatial Data 551
Loading Spatial Data 552
Sample Spatial Data 553
Getting Started with Spatial Data 553
Creating a Table with a Spatial Column 554
Well-Known Text 554
Constructing Spatial Objects from Strings and
Inserting into a Table 555
Basic Object Interaction Tests 559
Basic Spatial Operations 562
Proximity Queries 569
The GEOGRAPHY Type 576
Spatial Data Validity 579
Data Validity Issues with Geometry Data 579
Measuring Length and Area 581
Comparing Length Measurements between GEOMETRY
and GEOGRAPHY Instances 582
Comparing Area Measurements between GEOMETRY
and GEOMETRY Instances 583
Indexing Spatial Data 584
Spatial Index Basics 584
SQL Server Spatial Indexes 585
Using Spatial Indexes 586
Geography Indexes 588
Query Plans 589
Integration with Spatial Methods 591
Using Spatial Data to Solve Problems 591
Loading Spatial Data 592
Loading Spatial Data from Text Files 594
xii Table of Contents
Finding Site Locations within Geographic Regions 599
Nearest Neighbor Searches 602
Spatial Joins 605
Processing Spatial Data 608
Extending Spatial Support with CLR Routines 614
Types on the Client 614
User Defned Aggregate: Union and Dissolve 615
Sinks and Builders: Linear Transformations 618
Conclusion 622
15 Tracking Access and Changes to Data 625
Which Technology Do I Use? 625
Approaches Using Earlier SQL Server Versions 625
Technologies Added in SQL Server 2008 626
Extended Events Implementation 628
Extended Events Object Hierarchy 628
Implementing a Scenario Using Extended Events 635
Exploring Extended Events Concepts 638
SQL Server Audit Implementation 642
Auditing Object Hierarchy 642
Implementing a Scenario Using Auditing 646
Exploring SQL Server Audit Concepts 649
Change Tracking Implementation 653
Implementing a Scenario Using Change Tracking 653
Change Tracking Management Issues 660
Microsoft Sync Framework 662
Change Data Capture Implementation 665
Implementing a Scenario Using Change Data Capture 666
Change Data Capture Management Issues 671
Conclusion 674
16 Service Broker 675
Dialog Conversations 676
Conversations 676
Reliable 677
Messages 679
DEFAULT Message Type 683
Queues 683
Beginning and Ending Dialogs 688
Table of Contents xiii
Conversation Endpoints 691
Conversation Groups 693
Sending and Receiving 695
Activation 698
Internal Activation 699
External Activation 702
Conversation Priority 706
Broker Priority Object 707
Sample Dialog 711
Poison Messages 721
Dialog Security 721
Asymmetric Key Authentication 726
Confguring Dialog Security 727
Routing and Distribution 730
Adjacent Broker Protocol 731
Service Broker Endpoints 732
Routes 737
Troubleshooting 742
Scenarios 746
Reliable SOA 746
Asynchronous Processing 747
Where Does Service Broker Fit? 748
What Service Broker Is 748
What Service Broker Isn’t 748
Service Broker and MSMQ 749
Service Broker and BizTalk 749
Service Broker and Windows Communication
Foundation 750
Conclusion 750
Appendix A: Companion to CLR Routines 751
Index 781
- Log in to post comments