![Microsoft Transact-SQL: The Definitive Guide](http://prodimage.images-bn.com/pimages/9780763784164_p0_v1_s192x300.jpg)
Microsoft Transact-SQL: The Definitive Guide
by Jeffrey Garbus
Feature
- A comprehensive guide to the T-SQL language
- An approach to troubleshooting T-SQL queries
- Includes case studies and examples demonstrating how to write or rewrite T-SQL
- Provides instruction on identifying and fixing legacy T-SQL code
- Geared towards the most recent release of SQL Server 2008
Overview
Feature
- A comprehensive guide to the T-SQL language
- An approach to troubleshooting T-SQL queries
- Includes case studies and examples demonstrating how to write or rewrite T-SQL
- Provides instruction on identifying and fixing legacy T-SQL code
- Geared towards the most recent release of SQL Server 2008
Product Details
- ISBN-13:
- 9780763784164
- Publisher:
- Jones & Bartlett Learning
- Publication date:
- 05/21/2010
- Edition description:
- 1E
- Pages:
- 516
- Sales rank:
- 1,027,777
- Product dimensions:
- 7.40(w) x 9.10(h) x 1.10(d)
Table of Contents
About the Authors
Foreword
Part 1 All About Programming 1
Chapter 1 Introduction 3
SQL Variations 3
1 of 5 3
2 of 5 4
3 of 5 4
4 of 5 4
5 of 5 5
Where We Go from Here 6
Chapter 2 SQL Basics 9
SELECT Statement and Logical Query Processing 10
Joins 13
Cross Join 13
Inner Join 15
Outer Join 17
Subqueries 20
Table Expressions 23
UNION Operator 25
UPDATE Statement 27
TOP Option 31
CASE Expression 34
CUBE, ROLLUP, and GROUPING SETS Options 35
SELECT INTO Statement 41
Dynamic SQL 42
Summary 45
Additional Reading 45
Exercise 46
Exercise Solution 46
Chapter 3 Tables 49
Table Creation 49
Real and Implied Limitations 51
Data Types 52
Exact Numerics 52
Approximate Numerics 53
Date and Time 54
Character/Unicode Strings 55
Other Data Types 56
Working with CLR User-Defined Types 56
Data Type Selection 57
Identity Columns 58
ROWGUIDCOL 60
Computed Columns 60
Some General Tips for Creating Tables 61
Temporary Tables 62
Creating Temporary Tables 62
"Permanent" Temporary Tables 63
Table Variables 63
Summary 63
Exercise 64
Exercise Solution 64
Chapter 4 Views 65
Why Use a View? 65
The View in General 66
With Check Option 67
With Encryption 68
With Schemabinding 68
Limitations and Data Modification 68
Indexed Views 70
Requirements for an Indexed View 70
Partitioned Views and Horizontal Partitioning 72
Modifying Views 74
View Info 74
Summary 75
Exercise 75
Exercise Solution 75
Chapter 5 Built-In Functions and SET Options 77
Transact-SQL 77
Scalar Functions 77
Mathematical Functions 78
String Functions 79
Date Functions 81
System Functions 83
Function Examples 86
SET Options 90
SET Examples 94
More about SET Options 95
Summary 97
Exercise 97
Exercise Solution 97
Chapter 6 Programming Basics 99
Batches 99
Comments 100
Local Variables 101
Notes on Local Variables 102
PRINT Statement 103
Notes on the PRINT Statement 103
RAISERROR Statement 104
RAISERROR Options 104
Standardizing Error Message Syntax 106
Error Handling with TRY...CATCH 107
Notes on TRY...CATCH 108
Statement Blocks: BEGIN...END 109
Conditional Execution: IF...ELSE 110
IF EXISTS 111
Notes on IF...ELSE 112
Repeated Execution: WHILE 112
Control Transfer: GOTO 113
Event Handling: WAITFOR 113
Notes on WAITFOR 114
Exiting Batches with RETURN 115
Summary 115
Exercise 115
Exercise Solution 116
Chapter 7 Transactions 117
ACID Properties 118
Transactions and Batch Flow 122
Nested Transactions 122
Transactions and Stored Procedures 123
Locking 126
Granularity 127
Identifying Locks 128
Transaction Isolation Levels 129
Deadlocks 130
What Happens at Update Time 133
Summary 134
Chapter 8 Cursors 135
Cursors 136
Cursor Types 137
Cursor Steps 137
Transact-SQL Cursor Types 137
Dynamic Cursors 137
Static Cursors 138
Keyset Cursors 138
T-SQL Cursor Usage 138
Declaring Cursors 139
ANSI-92 Syntax 139
T-SQL Syntax 139
Opening Cursors 141
Notes on Open Cursors 141
Fetching Rows 141
Navigation Options 142
Fetch Examples 142
Notes on Fetch 143
Simple Cursor Example 144
Modification with Cursors 144
Notes on Cursor Modification 145
Closing the Cursor 145
Deallocating the Cursor 146
Scope of Cursors and Name Conflicts 146
Cursor Variables 146
Cursor Options 147
Cursor Lock Granularity 147
Cursors and Locking 147
A Sample Cursor Program 148
Notes on Cursors 149
Cursor Pros and Cons 149
Cursor Concurrency 150
Acquiring Locks 150
Using Locks 151
Optimizing Cursors 151
Summary 152
Chapter 9 Stored Procedures 153
Creating Stored Procedures 154
User Interfaces 156
Table-Valued Parameters 159
Parameter Sniffing 161
Error Handling 162
Transactions in Stored Procedures 165
Summary 170
Additional Reading 170
Exercise 170
Exercise Solution 171
Chapter 10 User-Defined Functions 175
UDF Components 176
Types of Functions 177
Table-Valued Functions 178
Summary 180
Exercise 180
Exercise Solution 180
Chapter 11 Triggers 181
The Trigger Mechanism 182
Trigger Creation 183
Removing Triggers 184
Modifying Triggers 185
Deleted and Inserted Tables 185
What Happens on Delete 186
What Happens on Insert 188
Handling Multirow Inserts/Updates 189
If Update Test Syntax 190
Checking Columns for Modification 191
Decoding a Bitmask 192
Insert Trigger Example 193
Conditional Insert Trigger Example 194
Update Trigger Example 195
Trigger Limitations 196
Triggers during Transactions 197
Using Savepoints in Triggers 198
Nested Triggers 199
Additional Notes on Triggers 200
Summary 201
Exercise 202
Exercise Solution 202
Chapter 12 Common Table Expressions 203
CTE Basics 203
CTE and Recursion 205
Multiple Anchor Members 206
Multiple Recursive Members 207
Recursion Limit 212
Uses for Common Table Expressions 213
Traversing a Hierarchy 214
Date Ranges 214
Parsing CSV Values 215
Beyond 32767 216
Summary 219
Part 2 Focus on Performance 221
Chapter 13 Understanding Graphical Query Plans 223
What Is an Execution Plan? 223
Retrieving Query Plans 224
Graphical Execution Plan Sections 226
Reading the Graphical Execution Plan 228
Analyzing Graphical Execution Plans 229
Common Operators in Graphical Plans 234
Clustered Index Scan/Nonclustered Index Scan/Table Scan 234
Clustered Index Seek/Index Seek 235
(Bookmark) Key Lookup/RID Lookup 237
Nested Loop Join 240
Merge Join 242
Hash Match 244
Sort 248
Stream Aggregate 250
Compute Scalar 251
Summary 252
Additional Reading 252
Chapter 14 Indexes 253
How Does the Index Work? 254
Covered Queries 255
Index Classification 256
Clustered Indexes 256
Nonclustered Indexes 257
Composite Index 259
Index Architecture 259
Index Structure and Access 260
Clustered versus Nonclustered Index Architecture 261
Clustered Index Architecture 262
Nonclustered Index Architecture 263
Data Storage and Data Access 264
Data Storage 264
Data Access 264
Indexing Strategy 265
When Not to Index 270
When to Use an Index 272
Index Creation and Maintenance 274
CREATE INDEX Command 274
DROP INDEX Command 275
FILLFACTOR Option 276
Database Reorganization 277
DBCC Command 278
Database Defragmentation 279
SQL Server Index Optimization 282
Auto Create Statistics 283
Index Selection 284
Column Selectivity and Joins 284
Operators and Clauses 285
INCLUDE Columns 287
Filtered Indexes 288
SYS.INDEX and SYS. SYSINDEXES System Tables 288
Constraints 292
Data Integrity 293
Domain Integrity 293
Entity Integrity 294
Referential Integrity 296
User-Defined Integrity 296
Constraint Types 297
PRIMARY KEY Constraints 297
UNIQUE Constraints 298
CHECK Constraints 299
FOREIGN KEY Constraints 300
NOT NULL Constraint 301
ER Diagrams with Key Constraints 302
Summary 303
Chapter 15 Join Optimization 303
Does Join Order Matter 306
Nested Loops Join (aka Nested Iteration Join) 308
Merge Joins 310
Hash Joins 310
In-Memory Hash Join 311
Grace Hash Join 312
Recursive Hash Join 312
Forcing a Join Order 313
Influencing the Joins 314
Things to Note While Giving Hints 316
Self-Joins 317
Matching Data Self-Join 318
Parent-Child Self-Join 320
Outer Joins 323
Summary 324
Exercise 324
Exercise Solution 325
Chapter 16 Subquery Optimization 327
Subquery Basics 330
Optimizer Rewrites 332
Optimizing Subqueries in the SELECT Clause 334
Optimizing Subqueries in the WHERE Clause 337
Scalar Subqueries 340
Summary 342
Exercise 342
Exercise Solution 343
Chapter 17 Hardware 345
CPU 348
CPU Precision 349
CPU Speed 350
CPU Cache 350
Operating System Effects on Processor Use 351
Before You Buy 352
Once the System Is in Place 352
Memory 357
Memory Size 357
Memory Speed 358
SQL Server's Memory Use 358
Before You Buy 361
Once the System Is in Place 361
Front-Side Bus 366
Before You Buy 367
Once the System Is in Place 367
Before You Buy 370
Once the System Is in Place 370
I/O 372
Solid State Drives (SSDs) and SQL Server 375
SANs, NAS, and Other Storage Types 375
How SQL Server Uses Files 376
Before You Buy 378
Once the System Is in Place 378
The Virtualization Question 384
Summary 385
Part 3 Advanced SQL Techniques 387
Chapter 18 Set-Oriented Programming versus Procedural Code 389
A Bit of History 389
Classic Structured Programming 390
Begin-End 390
If-Then-Else 391
While-Do 393
Merge Statement 396
Summary 398
Chapter 19 Dynamic SQL 399
Types of Dynamic SQL 399
T-SQL Dynamic SQL 399
.NET Application Dynamic SQL 402
Security Concerns 403
Table-Level Access Requirement 403
Using Impersonation with EXECUTE AS 404
SQL Injection 405
Unchecked User Inputs 405
Preventing SQL Injection 406
Using Certificates and EXECUTE AS to Circumvent SQL Injection 406
Putting Dynamic SQL to Work 408
Cache Bloat from Dynamic SQL 409
Using T-SQL Stored Procedures 410
Using .NET Application Dynamic SQL 416
Administration and Maintenance 418
Summary 419
Chapter 20 Grouping Data 421
CUBE Option 426
ROLLUP Option 426
GROUPING Function 428
GROUPING SETS 429
GROUPING_ID Function 432
Customer Reviews
Average Review: