Table Of Contents:
Foreword xv
Acknowledgments xvii
Introduction xxi

Views 1(30)

What are Views? 1(2)

Order By in a View 3(3)

Refreshing Views 6(2)

Modular Approach 8(8)

Updating Views 16(4)

View Options 20(4)

Encryption 20(1)

Schemabinding 20(2)

Check Option 22(1)

View_Metadata 23(1)

Indexed Views 24(5)

Conclusion 29(2)

User-Defined Functions 31(50)

Some Facts About UDFs 31(1)

Scalar UDFs 32(31)

T-SQL Scalar UDFs 32(2)

Performance Issues 34(6)

UDFs Used in Constraints 40(3)

CLR Scalar UDFs 43(14)

SQL Signature 57(6)

Table-Valued UDFs 63(14)

Inline Table-Valued UDFs 64(2)

Split Array 66(5)

Order Option for CLR Table-Valued UDFs 71(2)

Multistatement Table-Valued UDFs 73(4)

Per-Row UDFs 77(3)

Conclusion 80(1)

Stored Procedures 81(64)

Types of Stored Procedures 81(9)

User-Defined Stored Procedures 82(3)

Special Stored Procedures 85(2)

System Stored Procedures 87(2)

Other Types of Stored Procedures 89(1)

The Stored Procedure Interface 90(7)

Scalar Input Parameters 90(2)

Table-Valued Parameters 92(3)

Output Parameters 95(2)

Resolution 97(1)

Dependency Information 98(3)

Compilations, Recompilations, and Reuse of Execution Plans 101(28)

Reuse of Execution Plans 102(5)

Recompilations 107(4)

Variable Sniffing 111(7)

Plan Guides 118(11)

Execute As 129(1)

Parameterizing Sort Order 130(6)

CLR Stored Procedures 136(8)

Conclusion 144(1)

Triggers 145(46)

After Triggers 146(26)

The inserted and deleted Special Tables 146(1)

Identifying the Number of Affected Rows 147(4)

Identifying the Type of Trigger 151(1)

Not Firing Triggers for Specific Statements 152(4)

Nesting and Recursion 156(2)

Update and Columns_Updated 158(2)

Auditing Example 160(3)

Instead of Triggers 163(1)

Per-Row Triggers 164(3)

Used with Views 167(3)

Automatic Handling of Sequences 170(2)

DDL Triggers 172(8)

Database-Level Triggers 173(5)

Server-Level Triggers 178(2)

Logon Triggers 180(1)

CLR Triggers 181(9)

Conclusion 190(1)

Transactions and Concurrency 191(34)

What are Transactions? 192(2)

Locking and Blocking 194(5)

Lock Escalation 199(1)

Isolation Levels 200(14)

Read Uncommitted 202(2)

Read Committed 204(1)

Repeatable Read 205(1)

Serializable 206(2)

Row Versioning-Based Isolation Levels 208(6)

Savepoints 214(2)

Deadlocks 216(7)

Simple Deadlock Example 217(1)

Deadlock Caused by Missing Indexes 218(3)

Deadlock with a Single Table 221(2)

Conclusion 223(2)

Error Handling 225(20)

Error Handling without the TRY/CATCH Construct 225(4)

Error Handling with the TRY/CATCH Construct 229(15)

TRY/CATCH 229(2)

Error-Handling Functions 231(2)

Errors in Transactions 233(11)

Conclusion 244(1)

Temporary Tablesand Table Variables 245(40)

Temporary Tables 246(13)

Local Temporary Tables 246(11)

Global Temporary Tables 257(2)

Table Variables 259(9)

Limitations 260(1)

Tempdb 260(1)

Scope and Visibility 261(1)

Transaction Context 261(1)

Statistics 262(3)

Minimally Logged Inserts 265(3)

Tempdb Considerations 268(1)

Table Expressions 269(1)

Comparison Summary 270(1)

Summary Exercises 271(12)

Comparing Periods 272(2)

Recent Orders 274(4)

Relational Division 278(5)

Conclusion 283(2)

Cursors 285(30)

Using Cursors 285(2)

Cursor Overhead 287(2)

Dealing with Each Row Individually 289(1)

Order-Based Access 290(24)

Custom Aggregates 291(1)

Running Aggregations 292(8)

Maximum Concurrent Sessions 300(8)

Matching Problems 308(6)

Conclusion 314(1)

Dynamic SQL 315(60)

EXEC 317(9)

Simple EXEC Examples 317(1)

EXEC Has No Interface 318(3)

Concatenating Variables 321(1)

EXEC AT 322(4)

Sp_executesql 326(5)

The sp_executesql Interface 326(4)

Statement Limit 330(1)

Environmental Settings 331(1)

Uses of Dynamic SQL 331(33)

Dynamic Maintenance Activities 331(2)

Storing Computations 333(5)

Dynamic Filters 338(11)

Dynamic PIVOT/UNPIVOT 349(15)

SQL Injection 364(9)

SQL Injection: Code Constructed Dynamically at Client 364(1)

SQL Injection: Code Constructed Dynamically at Server 365(5)

Protecting Against SQL Injection 370(3)

Conclusion 373(2)

Working with Date and Time 375(34)

Date and Time Data Types 375(3)

Date and Time Manipulation 378(17)

Date and Time Functions 378(5)

Literals 383(2)

Identifying Weekday 385(2)

Handling Date-only or Time-only Data Prior to SQL Server 2008 387(1)

Examples of Date and Time Calculations 388(5)

Rounding Issues 393(2)

Date- and Time-Related Querying Problems 395(12)

Age Problems 395(4)

Overlaps 399(5)

Grouping by the Week 404(1)

Working Days 405(1)

Generating a Series of Dates 406(1)

Conclusion 407(2)

CLR User-Defined Types 409(30)

Theoretical Introduction to UDTs 409(7)

Domains and Relations 409(3)

Domains and Classes 412(1)

Complex Domains 412(3)

Why Do We Need Complex Classes? 415(1)

Language for Creating UDTs 416(1)

Programming a UDT 416(21)

UDT Requirements 417(2)

Creating a UDT 419(6)

Deploying the UDT Using T-SQL 425(12)

Conclusion 437(2)

Temporal Support in the Relational Model 439(52)

Timestamped Predicates and Propositions 440(3)

Time Points 441(1)

Time Points Lookup Table 442(1)

Semitemporal Problems 443(3)

Semitemporal Constraints 443(2)

Testing Semitemporal Constraints 445(1)

Queries on Semitemporal Tables 446(1)

Tables with Full Temporal Support 446(24)

The IntervalCID UDT 448(12)

Testing IntervalCID 460(4)

Full Temporal Tables Using IntervalCID 464(3)

Testing Full Temporal Constraints 467(1)

Queries on Tables with Full Temporal Support 468(2)

Unpack and Pack 470(9)

Expanded and Collapsed Forms of Interval Sets 473(1)

The UNPACK Operator 474(2)

PACK Operator 476(3)

Sixth Normal Form in Use 479(9)

Horizontal and Vertical Decompositions 479(8)

Sixth Normal Form 487(1)

Conclusion 488(3)

XML and XQuery 491(52)

Converting Relational Data to XML and Vice Versa 491(14)

Introduction to XML 491(4)

Producing XML from Relational Data 495(7)

Shredding XML to Tables 502(3)

The XQuery Language in SQL Server 2008 505(16)

XQuery Basics 506(4)

Navigation 510(6)

Iteration and Returning Values 516(5)

XML Data Type 521(15)

XML Support in a Relational Database 521(2)

When Should You Use XML Instead of Relational Representation? 523(2)

XML Serialized Objects in a Database 525(10)

XML as a Stored Procedure Parameter 535(1)

Dynamic Relational Schema 536(6)

Relational Solutions 536(1)

Object-Oriented Solutions 537(1)

Using the XML Data Type for Dynamic Scheme 538(4)

Conclusion 542(1)

Spatial Data 543(82)

Introduction to Spatial Data 543(1)

Basic Spatial Data Concepts 544(6)

Vector Data and the OGC Simple Features Type Model 544(2)

Planar and Geographic Coordinates 546(2)

Spatial Reference Identifiers 548(1)

Standards 549(1)

Working on the Ellipsoid 549(1)

Data 550(3)

Common Forms of Data 551(1)

Finding Spatial Data 551(1)

Loading Spatial Data 552(1)

Sample Spatial Data 553(1)

Getting Started with Spatial Data 553(26)

Creating a Table with a Spatial Column 554(1)

Well-Known Text 554(1)

Constructing Spatial Objects from Strings and Inserting into a Table 555(4)

Basic Object Interaction Tests 559(3)

Basic Spatial Operations 562(7)

Proximity Queries 569(7)

The Geography Type 576(3)

Spatial Data Validity 579(2)

Data Validity Issues with Geometry Data 579(2)

Measuring Length and Area 581(3)

Comparing Length Measurements between Geometry and Geography Instances 582(1)

Comparing Area Measurements between Geometry and Geography Instances 583(1)

Indexing Spatial Data 584(7)

Spatial Index Basics 584(1)

SQL Server Spatial Indexes 585(1)

Using Spatial Indexes 586(2)

Geography Indexes 588(1)

Query Plans 589(2)

Integration with Spatial Methods 591(1)

Using Spatial Data to Solve Problems 591(23)

Loading Spatial Data 592(2)

Loading Spatial Data from Text Files 594(5)

Finding Site Locations within Geographic Regions 599(3)

Nearest Neighbor Searches 602(3)

Spatial Joins 605(3)

Processing Spatial Data 608(6)

Extending Spatial Support with CLR Routines 614(8)

Types on the Client 614(1)

User Defined Aggregate: Union and Dissolve 615(3)

Sinks and Builders: Linear Transformations 618(4)

Conclusion 622(3)

Tracking Access and Changes to Data 625(50)

Which Technology Do I Use? 625(3)

Approaches Using Earlier SQL Server Versions 625(1)

Technologies Added in SQL Server 2008 626(2)

Extended Events Implementation 628(14)

Extended Events Object Hierarchy 628(7)

Implementing a Scenario Using Extended Events 635(3)

Exploring Extended Events Concepts 638(4)

SQL Server Audit Implementation 642(11)

Auditing Object Hierarchy 642(4)

Implementing a Scenario Using Auditing 646(3)

Exploring SQL Server Audit Concepts 649(4)

Change Tracking Implementation 653(12)

Implementing a Scenario Using Change Tracking 653(7)

Change Tracking Management Issues 660(2)

Microsoft Sync Framework 662(3)

Change Data Capture Implementation 665(9)

Implementing a Scenario Using Change Data Capture 666(5)

Change Data Capture Management Issues 671(3)

Conclusion 674(1)

Service Broker 675(76)

Dialog Conversations 676(22)

Conversations 676(1)

Reliable 677(2)

Messages 679(4)

Default Message Type 683(1)

Queues 683(5)

Beginning and Ending Dialogs 688(3)

Conversation Endpoints 691(2)

Conversation Groups 693(2)

Sending and Receiving 695(3)

Activation 698(8)

Internal Activation 699(3)

External Activation 702(4)

Conversation Priority 706(5)

Broker Priority Object 707(4)

Sample Dialog 711(10)

Poison Messages 721(1)

Dialog Security 721(9)

Asymmetric Key Authentication 726(1)

Configuring Dialog Security 727(3)

Routing and Distribution 730(12)

Adjacent Broker Protocol 731(1)

Service Broker Endpoints 732(5)

Routes 737(5)

Troubleshooting 742(4)

Scenarios 746(2)

Reliable SOA 746(1)

Asynchronous Processing 747(1)

Where Does Service Broker Fit? 748(2)

What Service Broker Is 748(1)

What Service Broker Isn't 748(1)

Service Broker and MSMQ 749(1)

Service Broker and BizTalk 749(1)

Service Broker and Windows Communication Foundation 750(1)

Conclusion 750(1)
Appendix A: Companion to CLR Routines 751(30)
Index 781