Contents
Foreword
Preface
About the Sample Databases
Results Abridged
On Formality
Acknowledgments
Chapter 1: Introductory Transact-SQL
Choosing a SQL Editor
Creating a Database
Creating Tables
Inserting Data
Updating Data
Deleting Data
Querying Data
Column Lists
SELECTing Variables and Expressions
Functions
Converting Data Types
CASE
Aggregate Columns
Filtering Data
Joins
Outer Joins
Other Types of Joins
Subqueries
Grouping Data
HAVING
Ordering Data
Column Aliases
Table Aliases
Managing Transactions
Summary
Chapter 2: Transact-SQL Data Type Nuances
Dates
Y2K and Other Date Problems
Date Functions
Dates and Simple Arithmetic
Determining Time Gaps
Building Calendars
Strings
Concatenation
Char vs. Varchar
SET ANSI_PADDING
String Functions
CHARINDEX()
SOUNDEX()
A Better SOUNDEX()
DIFFERENCE()
Xp_sprintf
Xp_sscanf
Masks
Executing Strings
Unicode
Numerics
Floating Point Fun
Division by Zero
Funny Money
Formatting Numeric Data
BLOBs
Caveats
Retrieving BLOB Data
Updating BLOB Data
BLOB Updates and the Transaction Log
Bits
UNIQUEIDENTIFIER
Cursor Variables
Timestamps
Summary
Chapter 3: Missing Values
NULL and Expressions
NULL and Functions
NULL and ANSI SQL
NULL and Stored Procedures
NULL If you Must
Chapter 4: DDL Insights
Create Table
Some Thoughts on Referential Integrity
Foreign Keys
ANSI Referential Actions
The NULL Exception
Unique Index Requirement
No TRUNCATE TABLE
Default Constraints
Dropping Objects
Create Index
No Bit or BLOB Indexes
No Computed Column Indexes
PAD_INDEX
DROP_EXISTING
TEMPORARY OBJECTS
No More Unusable Temporary Objects
Can’t Create Objects in Other Databases
Temporary Stored Procedures
Increased Temporary Table Name Length
Global Temporary Status Tables
Object Naming and Dependencies
Changing the Database Context Temporarily
Temporary Table Indexes
Be Wary of Unusable Views
Object Dependencies
Summary
Chapter 5: DML Insights
INSERT
DEFAULT and NULL
SET IDENTITY_INSERT
INSERT...DEFAULT VALUES
INSERT...SELECT
INSERT...EXEC
Extended Procedures
INSERT and Errors
Using INSERT to Remove Duplicate Rows
INSERT and Clustered Indexes
BULK INSERT
BULK INSERT and Triggers
BULK INSERT and Constraints
BULK INSERT and Identity Columns
UPDATE
The Halloween Problem
UPDATE and CASE
Using UPDATE to Check Constraints
Limiting the Number of Rows Affected by an UPDATE
Swapping Column Values with UPDATE
UPDATE and Cursors
DELETE
DELETE and Cursors
TRUNCATE TABLE
Detecting DML Errors
Summary
Chapter 6: The Mighty SELECT Statement
Simple SELECTs
Computational and Derived Fields
Select Top
Derived Tables
Joins
Outer Joins and Join Order
Predicates
BETWEEN
LIKE
EXISTS
NULLs
EXISTS and IN
Joins
Result Set Emptiness
EXISTS Outside WHERE and HAVING
IN
Optimizing IN
ANY and ALL
Subqueries
WHERE and Subqueries
Correlated Subqueries
Relational Division
Aggregate Functions
GROUP BY and HAVING
Pivot Tables
CUBE and ROLLUP
HAVING
UNION
ORDER BY
Summary
Chapter 7: Views
Restrictions
DML Restrictions
ANSI SQL Schema VIEWs
Getting a VIEW’s Source Code
Updatable VIEWs
WITH CHECK OPTION
Derived Tables
Dynamic VIEWs
Partitioning Data Using Views
Summary
Chapter 8: Statistical Functions
The Case for CASE
Efficiency Concerns
Variance and Standard Deviation
Medians
The Identity Column Technique
The CASE Technique
Vector Medians
Duplicate Values
Clipping
Returning the Top n Rows
SET ROWCOUNT
Rankings
Modes
Histograms
Stratified Histograms
Cumulative and Sliding Aggregates
Sliding Aggregates
Extremes
Determining Extreme Attributes
Summary
Chapter 9: Runs and Sequences
Sequences
Time Series Fluctuation
Sampling Every nth Value
Regions
Relative Condition Regions
Constraining Region Sizes
Region Boundaries
Runs
Regions
Region Boundaries
Constrained Regions
Intervals
Partitioned Intervals
Summary
Chapter 10: Arrays
Arrays as Big Strings
Modifying Array Elements
Arrays as Tables
Sorting
Transposing Dimensions
Ensuring Array Integrity
Reshaping the Array
Comparing Arrays
Summary
Chapter 11: Sets
Unions
Differences
Intersections
Subsets
Returning Every nth Row
Summary
Chapter 12: Hierarchies
Simple Hierarchies
Multilevel Hierarchies
Indenting a Hierarchy
Another Approach
Listing Leaf Nodes
Indented Lists
Summary
Chapter 13: Cursors
On Cursors and ISAMs
Types of Cursors
Forward-Only Cursors
Dynamic Cursors
Static Cursors
Keyset Cursors
Appropriate Cursor Use
Dynamic Queries
Row-Oriented Operations
Scrollable Forms
T-SQL Cursor Syntax
DECLARE CURSOR
Global vs. Local Cursors
OPEN
FETCH
CLOSE
DEALLOCATE
Configuring Cursors
Asynchronous Cursors
ANSI/ISO Automatic Cursor Closing
Defaulting to Global or Local Cursors
Updating Cursors
Cursor Variables
Cursor Stored Procedures
Optimizing Cursor Performance
Summary
Chapter 14: Transactions
Transactions Defined
The ACID Test
Atomicity
Consistency
Isolation
Durability
How SQL Server Transactions Work
Transactions and Nonlogged Operations
Transactions and Triggers
Types of Transactions
Automatic Transactions
Implicit Transactions
User-Defined Transactions
Distributed Transactions
Avoiding Transactions Altogether
Commands That Minimize Logging
Read-Only and Single-User Databases
Automatic Transaction Management
Transaction Isolation Levels
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Transaction Commands and Syntax
Nested Transactions
SAVE TRAN and Save Points
Avoid Accidental ROLLBACKs
Invalid T-SQL Syntax in Transactions
Debugging Transactions
Optimizing Transactional Code
Summary
Chapter 15: Stored Procedures and Triggers
Stored Procedure Advantages
Internals
Creation
User Execution
Compilation
Server Execution
Creating Stored Procedures
Creation Tips
Temporary Procedures
System Procedures
Extended Procedures
Faux Procedures
Executing Stored Procedures
INSERT and EXEC
Environmental Concerns
Parameters
Output Parameters
Result Codes
Listing Procedure Parameters
General Parameter Notes
Important Automatic Variables
Flow Control Language
Errors
@@ERROR
xp_logevent
Nesting
Recursion
Autostart Procedures
Encryption
Triggers
Debugging Procedures
Summary
Chapter 16: Transact-SQL Performance Tuning
General Performance Guidelines
Database Design Performance Tips
Index Performance Tips
SELECT Performance Tips
INSERT Performance Tips
Bulk Copy Performance Tips
DELETE and UPDATE Performance Tips
Cursor Performance Tips
Stored Procedure Performance Tips
SARGs
Denormalization
Basic Guidelines
Basic Techniques
Contrived Columns
Redundant Data
Summary Tables
Vertical Partitioning
Horizontal Partitioning
The Query Optimizer
Join Optimizations
Nested Loops
Merge Joins
Hash Joins
Index Optimizations
Index Joins
Index Merging and Intersection
Data Warehouse Optimizations
Semijoins
Grouping Optimizations
Predicate Clause Optimizations
The Index Tuning Wizard
Profiler
Perfmon
User Counters
Perfmon-Related DBCC Commands
Summary
Chapter 17: Administrative Transact-SQL
GUI Administration
System Stored Procedures
Administrative Transact-SQL Commands
Administrative System Functions
Administrative Automatic Variables
Where’s the Beef?
Status Routines
sp_active_processes
sp_pss
sp_find_root_blocker
sp_lock_verbose
Catalog Procedures
sp_table
sp_dir
sp_object
Maintenance Routines
sp_update_stats_all
sp_updateusage_all
sp_rebuildindexes_all
sp_dbbackup
sp_copyfile
sp_make_portable
INIT_SERVER.SQL
sp_readtextfile
Scripting Routines
Summary
Chapter 18: Full-Text Search
Full-Text Predicates
The CONTAINS() Predicate
The FREETEXT() Predicate
Rowset Functions
The CONTAINSTABLE() Rowset Function
The FREETEXTTABLE() Rowset Function
Summary
Chapter 19: OLE Automation
sp_exporttable
sp_importtable
sp_getsQLregistry
Summary
Chapter 20: Undocumented T-SQL
Defining Undocumented
Undocumented DBCC Commands
DBCC ADDEXTENDEDPROC(procname,DLL)
DBCC ADDINSTANCE(object,instance)
DBCC BCPTABLOCK(dbid, tabid, setflag)
DBCC BUFFER(dbid[,objid][,numberofbuffers][,printopt {0 | 1 | 2}])
DBCC BYTES(startingaddress,length)
DBCC CALLFULLTEXT(funcid[,catid][,objid])
DBCC DBCONTROL(dbname,option)
DBCC DBINFO(dbname)
DBCC DBRECOVER(dbname)
DBCC DBTABLE(dbid)
DBCC DELETEINSTANCE(object,instance)
DBCC DES(dbid,objid)
DBCC DETACHDB(dbname)
DBCC DROPCLEANBUFFERS
DBCC DROPEXTENDEDPROC(procname)
DBCC ERRORLOG
DBCC EXTENTINFO(dbname, tablename, indid)
DBCC FLUSHPROCINDB(dbid)
DBCC FREEPROCCACHE
DBCC IND(dbid, objid[,printopt {0 | 1 | 2}])
DBCC LOCKOBJECTSCHEMA (objname)
DBCC LOG(dbid)
DBCC PAGE (dbid|dbname, filenum, pagenum [,printopt][,cacheopt])
DBCC PRTIPAGE(dbid, objid, indexid[, printopt {0 | 1 | 2}])
DBCC RESOURCE
DBCC SETINSTANCE(object,counter,instance,val)
DBCC TAB(dbid,objid[,printopt {0 | 1 | 2}}])
DBCC UPGRADEDB(dbname)
Undocumented Functions and Variables
ENCRYPT(string)
GET_SID(username)
OBJECT_ID(..,’local’)
PWDCOMPARE(str,pwd,oldenc)
PWDENCRYPT(str)
TSEQUAL(ts1,ts2)
@@MICROSOFTVERSION
Undocumented Trace Flags
Undocumented Procedures
Summary
Chapter 21: Potpourri
Obscure Functions
Status Functions
Property Functions
Identifier Functions
Index Functions
Data Functions
Unusual String Functions
Data Scrubbing
Removing Duplicates
Iteration Tables
Summary
Appendix: Suggested Resources
Books
Internet Resources
Index
