Inside Microsoft® SQL Server® 2008 · T-SQL Programming by Ben-Gan, Itzik -- Read -- Imperial Library of Trantor

Index

Inside Microsoft® SQL Server® 2008: T-SQL Programming

Foreword Acknowledgments Introduction

Hardware and Software Requirements Companion Content and Sample Database Find Additional Content Online Support for These Books

Questions and Comments

1. Views

What Are Views? ORDER BY in a View Refreshing Views Modular Approach Updating Views View Options

ENCRYPTION SCHEMABINDING CHECK OPTION VIEW_METADATA

Indexed Views Conclusion

2. User-Defined Functions

Some Facts About UDFs Scalar UDFs

T-SQL Scalar UDFs Performance Issues UDFs Used in Constraints

DEFAULT Constraints CHECK Constraints PRIMARY KEY and UNIQUE Constraints

CLR Scalar UDFs

CLR Routines String Manipulation

Matching Based on Regular Expressions Replacement Based on Regular Expressions Formatting Date and Time Values

Explicit vs. Implicit Conversions

SQL Signature

T-SQL SQL Signature UDF CLR SQL Signature UDF Compare Performance of T-SQL and CLR SQL Signature UDFs

Table-Valued UDFs

Inline Table-Valued UDFs Split Array

T-SQL Split UDF CLR Split UDF Compare Performance of T-SQL and CLR Split

ORDER Option for CLR Table-Valued UDFs Multistatement Table-Valued UDFs

Per-Row UDFs Conclusion

3. Stored Procedures

Types of Stored Procedures

User-Defined Stored Procedures Special Stored Procedures System Stored Procedures Other Types of Stored Procedures

The Stored Procedure Interface

Scalar Input Parameters Table-Valued Parameters Output Parameters

Resolution Dependency Information Compilations, Recompilations, and Reuse of Execution Plans

Reuse of Execution Plans Recompilations Variable Sniffing

OPTIMIZE FOR UNKNOWN

Plan Guides

Object Plan Guides SQL Plan Guides Template Plan Guides Using a Fixed XML Plan Plan Freezing

EXECUTE AS Parameterizing Sort Order CLR Stored Procedures Conclusion

4. Triggers

AFTER Triggers

The inserted and deleted Special Tables Identifying the Number of Affected Rows Identifying the Type of Trigger Not Firing Triggers for Specific Statements Nesting and Recursion UPDATE and COLUMNS_UPDATED Auditing Example INSTEAD OF Triggers Per-Row Triggers Used with Views Automatic Handling of Sequences

DDL Triggers

Database-Level Triggers Server-Level Triggers

Logon Triggers CLR Triggers Conclusion

5. Transactions and Concurrency

What Are Transactions? Locking and Blocking Lock Escalation Isolation Levels

Read Uncommitted Read Committed Repeatable Read Serializable Row Versioning–Based Isolation Levels

Snapshot

Conflict Detection

Read Committed Snapshot

Savepoints Deadlocks

Simple Deadlock Example Deadlock Caused by Missing Indexes Deadlock with a Single Table

Conclusion

6. Error Handling

Error Handling without the TRY/CATCH Construct Error Handling with the TRY/CATCH Construct

TRY/CATCH Error-Handling Functions Errors in Transactions

Using XACT_STATE Using Savepoints Deadlocks and Update Conflicts

Conclusion

7. Temporary Tables and Table Variables

Temporary Tables

Local Temporary Tables

tempdb Scope and Visibility Transaction Context Statistics Temporary Table Name Resolution Schema Changes to Temporary Tables in Dynamic Batches Caching of Temporary Objects

Global Temporary Tables

Table Variables

Limitations tempdb Scope and Visibility Transaction Context Statistics Minimally Logged Inserts

tempdb Considerations Table Expressions Comparison Summary Summary Exercises

Comparing Periods Recent Orders Relational Division

Conclusion

8. Cursors

Using Cursors Cursor Overhead Dealing with Each Row Individually Order-Based Access

Custom Aggregates Running Aggregations Maximum Concurrent Sessions Matching Problems

Conclusion

9. Dynamic SQL

EXEC

Simple EXEC Examples EXEC Has No Interface Concatenating Variables EXEC AT

sp_executesql

The sp_executesql Interface Statement Limit

Environmental Settings Uses of Dynamic SQL

Dynamic Maintenance Activities Storing Computations Dynamic Filters

Static Solution Using IS NULL Static Solution Using COALESCE Using the RECOMPILE Statement Option Using an Inline Table UDF Static Solution Based on Multiple Procedures Solution Based on Dynamic SQL

Dynamic PIVOT/UNPIVOT

Dynamic PIVOT Generalizing Dynamic Pivoting Dynamic UNPIVOT

SQL Injection

SQL Injection: Code Constructed Dynamically at Client SQL Injection: Code Constructed Dynamically at Server Protecting Against SQL Injection

Conclusion

10. Working with Date and Time

Date and Time Data Types Date and Time Manipulation

Date and Time Functions

Functions Supported Prior to SQL Server 2008 Functions Introduced in SQL Server 2008

Literals Identifying Weekday Handling Date-only or Time-only Data Prior to SQL Server 2008 Examples of Date and Time Calculations

First or Last Day of a Period Previous or Next Weekday First or Last Weekday

Rounding Issues

Date- and Time-Related Querying Problems

Age Problems Overlaps

Identifying Overlaps Grouping Overlaps

Grouping by the Week Working Days Generating a Series of Dates

Conclusion

11. CLR User-Defined Types

Theoretical Introduction to UDTs

Domains and Relations Domains and Classes Complex Domains Why Do We Need Complex Classes? Language for Creating UDTs

Programming a UDT

UDT Requirements Creating a UDT Deploying the UDT Using T-SQL

Conclusion

12. Temporal Support in the Relational Model

Timestamped Predicates and Propositions

Time Points Time Points Lookup Table

Semitemporal Problems

Semitemporal Constraints Testing Semitemporal Constraints Queries on Semitemporal Tables

Tables with Full Temporal Support

The IntervalCID UDT Testing IntervalCID Full Temporal Tables Using IntervalCID Testing Full Temporal Constraints Queries on Tables with Full Temporal Support

Unpack and Pack

Expanded and Collapsed Forms of Interval Sets The UNPACK Operator PACK Operator

Sixth Normal Form in Use

Horizontal and Vertical Decompositions Sixth Normal Form

Conclusion

13. XML and XQuery

Converting Relational Data to XML and Vice Versa

Introduction to XML Producing XML from Relational Data

FOR XML RAW FOR XML AUTO FOR XML EXPLICIT FOR XML PATH

Shredding XML to Tables

The XQuery Language in SQL Server 2008

XQuery Basics

Data Types Functions

Navigation

Predicates Examples of Navigation

Iteration and Returning Values

XML Data Type

XML Support in a Relational Database

Is the Relational Model Obsolete?

When Should You Use XML Instead of Relational Representation? XML Serialized Objects in a Database

The value and query XML Data Type Methods XML Indexes The modify and exists XML Data Type Methods The nodes XML Data Type Method Reading the Execution Plan

XML as a Stored Procedure Parameter

Dynamic Relational Schema

Relational Solutions Object-Oriented Solutions Using the XML Data Type for Dynamic Schema

Conclusion

14. Spatial Data

Introduction to Spatial Data Basic Spatial Data Concepts

Vector Data and the OGC Simple Features Type Model Planar and Geographic Coordinates Spatial Reference Identifiers Standards Working on the Ellipsoid

Data

Common Forms of Data Finding Spatial Data Loading Spatial Data Sample Spatial Data

Getting Started with Spatial Data

Creating a Table with a Spatial Column Well-Known Text Constructing Spatial Objects from Strings and Inserting into a Table Basic Object Interaction Tests Basic Spatial Operations

Intersecting Spatial Objects Union of Spatial Objects Generalization of Spatial Objects

Proximity Queries

Distance between Spatial Objects Spatial Buffers Comparing Spatial Buffers and Distance-Based Calculations to Test Proximity

The GEOGRAPHY Type

The Geography Type and SRIDs The Geography Type and Coordinate Ordering Coordinate Systems and Units of Measure The Extent of Geography Objects

Spatial Data Validity

Data Validity Issues with Geometry Data

Measuring Length and Area

Comparing Length Measurements between GEOMETRY and GEOGRAPHY Instances Comparing Area Measurements between GEOMETRY and GEOMETRY Instances

Indexing Spatial Data

Spatial Index Basics SQL Server Spatial Indexes Using Spatial Indexes Geography Indexes Query Plans Integration with Spatial Methods

Using Spatial Data to Solve Problems

Loading Spatial Data

Shape2SQL MapPoint 2009 Add-In for SQL Server

Loading Spatial Data from Text Files

Common Workflow Patterns

Using the GEOMETRY MakeValid Method Forcing Polygon Ring Reorientation

Moving Geometry Data to Geography Data Using MakeValidGeographyFromGeometry

Finding Site Locations within Geographic Regions

Find High Schools within 2 KM of Interstate 5 in King County, Washington State Find Schools Within 4 KM of the Intersection of Interstate 5 and Interstate 405 in King County, Washington State

Nearest Neighbor Searches

Find the Nearest GeoNames Data around a Point Location Find ZIP Codes around a Point Location

Spatial Joins Processing Spatial Data

Processing the Highways Table in the Sample_USA Database The Workflow

Extending Spatial Support with CLR Routines

Types on the Client User Defined Aggregate: Union and Dissolve Sinks and Builders: Linear Transformations

Conclusion

15. Tracking Access and Changes to Data

Which Technology Do I Use?

Approaches Using Earlier SQL Server Versions Technologies Added in SQL Server 2008

Extended Events SQL Server Audit Change Tracking Change Data Capture

Extended Events Implementation

Extended Events Object Hierarchy

Modules Packages Events Predicates Actions Maps Targets

Implementing a Scenario Using Extended Events Exploring Extended Events Concepts

Asynchronous File Target Viewing Existing Sessions Managing Existing Event Sessions

SQL Server Audit Implementation

Auditing Object Hierarchy

Audit Server Audit Specification Database Audit Specification

Implementing a Scenario Using Auditing Exploring SQL Server Audit Concepts

Viewing Details of Audits Indirect and Parameterized Access Auditing to the Security Event Log Management Issues for SQL Server Audit

Change Tracking Implementation

Implementing a Scenario Using Change Tracking Change Tracking Management Issues Microsoft Sync Framework

Change Data Capture Implementation

Implementing a Scenario Using Change Data Capture Change Data Capture Management Issues

Schema Changes Performance Security Effects on the Source System Removing the CDC Database

Conclusion

16. Service Broker

Dialog Conversations

Conversations Reliable

Ordered Asynchronous

Messages

Contracts

DEFAULT Message Type Queues

Services

Beginning and Ending Dialogs Conversation Endpoints Conversation Groups Sending and Receiving

Activation

Internal Activation

Activation Procedures Activation Security Context Using Activation

External Activation

QUEUE_ACTIVATION Event External Activation Service

Conversation Priority

Broker Priority Object

Examples

Sample Dialog Poison Messages Dialog Security

Asymmetric Key Authentication Configuring Dialog Security

Routing and Distribution

Adjacent Broker Protocol Service Broker Endpoints

Configuring Adjacent Broker Protocol Connections

Windows Authentication Certificate Authentication

Routes

Broker Instance Incoming Routes Wildcards and Special Addresses Load Balancing Routes and Database Mirroring Forwarding

Troubleshooting

Conversation Population Explosion No Reply Poison Message Queue Disable The ssbdiagnose Utility

Scenarios

Reliable SOA Asynchronous Processing

Where Does Service Broker Fit?

What Service Broker Is What Service Broker Isn’t Service Broker and MSMQ Service Broker and BizTalk Service Broker and Windows Communication Foundation

Conclusion

A. Companion to CLR Routines

Create the CLRUtilities Database: SQL Server Development: Visual Studio

Create a Project Develop Code

Deployment and Testing: Visual Studio and SQL Server

Build and Deploy the Solution Test the Solution

B. About the Authors Index

← Prev
Back
Next →

← Prev
Back
Next →