第1页 / 共832页
第2页 / 共832页
第3页 / 共832页
第4页 / 共832页
第5页 / 共832页
第6页 / 共832页
第7页 / 共832页
第8页 / 共832页
Inside Microsoft SQL Server 2008 T-SQL Programming.pdf
Cover
Copyright Page
Dedication Page
Table of Contents
Foreword
Acknowledgments
Introduction
Hardware and Software Requirements
Companion Content and Sample Database
Find Additional Content Online
Support for These Books
Questions and Comments
Chapter 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
Chapter 2: User-Defined Functions
Some Facts About UDFs
Scalar UDFs
T-SQL Scalar UDFs
Performance Issues
UDFs Used in Constraints
CLR Scalar UDFs
SQL Signature
Table-Valued UDFs
Inline Table-Valued UDFs
Split Array
ORDER Option for CLR Table-Valued UDFs
Multistatement Table-Valued UDFs
Per-Row UDFs
Conclusion
Chapter 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
Plan Guides
EXECUTE AS
Parameterizing Sort Order
CLR Stored Procedures
Conclusion
Chapter 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
Chapter 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
Savepoints
Deadlocks
Simple Deadlock Example
Deadlock Caused by Missing Indexes
Deadlock with a Single Table
Conclusion
Chapter 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
Conclusion
Chapter 7: Temporary Tablesand Table Variables
Temporary Tables
Local Temporary Tables
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
Chapter 8: Cursors
Using Cursors
Cursor Overhead
Dealing with Each Row Individually
Order-Based Access
Custom Aggregates
Running Aggregations
Maximum Concurrent Sessions
Matching Problems
Conclusion
Chapter 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
Dynamic PIVOT/UNPIVOT
SQL Injection
SQL Injection: Code Constructed Dynamically at Client
SQL Injection: Code Constructed Dynamically at Server
Protecting Against SQL Injection
Conclusion
Chapter 10: Working with Date and Time
Date and Time Data Types
Date and Time Manipulation
Date and Time Functions
Literals
Identifying Weekday
Handling Date-only or Time-only Data Prior to SQL Server 2008
Examples of Date and Time Calculations
Rounding Issues
Date- and Time-Related Querying Problems
Age Problems
Overlaps
Grouping by the Week
Working Days
Generating a Series of Dates
Conclusion
Chapter 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
Chapter 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
Chapter 13: XML and XQuery
Converting Relational Data to XML and Vice Versa
Introduction to XML
Producing XML from Relational Data
Shredding XML to Tables
The XQuery Language in SQL Server 2008
XQuery Basics
Navigation
Iteration and Returning Values
XML Data Type
XML Support in a Relational Database
When Should You Use XML Instead of Relational Representation?
XML Serialized Objects in a Database
XML as a Stored Procedure Parameter
Dynamic Relational Schema
Relational Solutions
Object-Oriented Solutions
Using the XML Data Type for Dynamic Schema
Conclusion
Chapter 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
Proximity Queries
The GEOGRAPHY Type
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
Loading Spatial Data from Text Files
Finding Site Locations within Geographic Regions
Nearest Neighbor Searches
Spatial Joins
Extending Spatial Support with CLR Routines
Types on the Client
User Defined Aggregate: Union and Dissolve
Sinks and Builders: Linear Transformations
Conclusion
Chapter 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 Implementation
Extended Events Object Hierarchy
Implementing a Scenario Using Extended Events
Exploring Extended Events Concepts
SQL Server Audit Implementation
Auditing Object Hierarchy
Implementing a Scenario Using Auditing
Exploring SQL Server Audit Concepts
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
Conclusion
Chapter 16: Service Broker
Dialog Conversations
Conversations
Reliable
Messages
DEFAULT Message Type
Queues
Beginning and Ending Dialogs
Conversation Endpoints
Conversation Groups
Sending and Receiving
Activation
Internal Activation
External Activation
Conversation Priority
Broker Priority Object
Sample Dialog
Poison Messages
Dialog Security
Asymmetric Key Authentication
Configuring Dialog Security
Routing and Distribution
Adjacent Broker Protocol
Service Broker Endpoints
Routes
Troubleshooting
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
Appendix 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
Index
About the Authors
Marketing Pages
Survey Page
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2010 by Itzik Ben-Gan, Dejan Sarka, Ed Katibah, Greg Low, Roger Wolter, and Isaac Kunen
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means
without the written permission of the publisher.
Library of Congress Control Number: 2009932820
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 4 3 2 1 0 9
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further information about
international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at
fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to tkinput@microsoft.com.
Microsoft, Microsoft Press, Active Directory, BizTalk, MapPoint, MS, MultiPoint, SQL Server, Visio, Visual Basic, Visual C#,
Visual Studio and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or
other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted
herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo,
person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided without any
express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will
be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.
Acquisitions Editor: Ken Jones
Developmental Editor: Denise Bankaitis
Project Editor: Denise Bankaitis
Editorial Production: Ashley Schneider, S4Carlisle Publishing Services
Technical Reviewer: Steve Kass; Technical Review services provided by Content Master, a member of CM Group, Ltd.
Cover: Tom Draper Design
Body Part No. X15-74121
To my siblings, Ina & Mickey
—Itzik
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-Defined 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
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you. To participate in a brief online survey, please visit:
www.microsoft.com/learning/booksurvey/
v
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-Defined 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 Sniffing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 Specific 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
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
