This is a coder's book. It's intended to help developers build applications that make use of Transact-SQL. It's
not about database administration or design. It's not about end-user or GUI application development. It's not
even about server or database performance tuning. It's about developing the best Transact-SQL code
possible, regardless of the application.
When I began writing this book, I had these design goals in mind:
• Be very generous with code samples—don't just tell readers how to do something, show them.
• Include complete code samples within the chapter texts so that the book can be read through without
requiring a computer or CD-ROM.
• Use modern coding techniques, with specific emphases on ANSI compliance and current version
features and enhancements.
• Construct chapters so that they're self-contained—so that they rely as little as possible on objects
created in other chapters.
• Provide real-world code samples that have intrinsic value apart from thebook.
• Avoid rehashing what's already covered extensively in the SQL Server Books Online.
• Highlight aspects of Transact-SQL that differentiate it from other SQL dialects; don't just write another
ANSI SQL book.
• Avoid excessive screenshots and other types of filler mechanisms often seen in computer books.
• Proceed from the simple to the complex within each chapter and throughout the book.
• Provide an easygoing, relaxed commentary with a de-emphasis on formality. Be the reader's
indulgent, amiable tutor. Attempt to communicate in writing the way that people speak.
You'll have to judge for yourself whether these goals have been met, but my hope is that, regardless of the
degree of success, the effort will at least be evident.
Contents
Foreword...........................................................................................................................................i
Preface........................................................................................................................................... iii
About the Sample Databases ................................................................................................. iii
Results Abridged ....................................................................................................................... iii
On Formality.............................................................................................................................. iii
Acknowledgments ......................................................................................................................iv
Contents.........................................................................................................................................v
Chapter 1. Introductory Transact-SQL.........................................................................................1
Choosing a SQL Editor...............................................................................................................1
Creating a Database...................................................................................................................2
Creating Tables ...........................................................................................................................3
Inserting Data..............................................................................................................................4
Updating Data.............................................................................................................................5
Deleting Data ...............................................................................................................................5
Querying Data.............................................................................................................................6
Filtering Data...............................................................................................................................9
Grouping Data ...........................................................................................................................14
Ordering Data ............................................................................................................................16
Column Aliases.........................................................................................................................16
Table Aliases.............................................................................................................................17
Managing Transactions............................................................................................................17
Summary ...................................................................................................................................18
Chapter 2. Transact-SQL Data Type Nuances ........................................................................19
Dates ..........................................................................................................................................19
Strings ........................................................................................................................................28
Numerics....................................................................................................................................46
BLOBs........................................................................................................................................50
Bits..............................................................................................................................................55
UNIQUEIDENTIFIER...............................................................................................................57
Cursor Variables.......................................................................................................................58
Timestamps...............................................................................................................................62
Summary ...................................................................................................................................64
Chapter 3. Missing Values...........................................................................................................65
NULL and Functions .................................................................................................................66
NULL and ANSI SQL................................................................................................................67
NULL and Stored Procedures .................................................................................................68
NULL if you Must.......................................................................................................................69
Chapter 4. DDL Insights...............................................................................................................71
CREATE TABLE........................................................................................................................71
Dropping Objects.......................................................................................................................74
CREATE INDEX........................................................................................................................75
TEMPORARY OBJECTS.........................................................................................................76
Object Naming and Dependencies.........................................................................................77
Summary ...................................................................................................................................78
Chapter 5. DML Insights ..............................................................................................................81
INSERT......................................................................................................................................81
UPDATE ....................................................................................................................................91
DELETE...................................................................................................................................100
Detecting DML Errors .............................................................................................................103
Summary .................................................................................................................................103
Chapter 6. The Mighty SELECT Statement............................................................................105
Simple SELECTs.....................................................................................................................105
Computational and Derived Fields .......................................................................................105
SELECT TOP...........................................................................................................................106
Derived Tables.........................................................................................................................108
Joins .........................................................................................................................................111
Predicates................................................................................................................................113
Subqueries ...............................................................................................................................123
Aggregate Functions...............................................................................................................129
GROUP BY and HAVING ......................................................................................................131
UNION......................................................................................................................................137
ORDER BY..............................................................................................................................139
Summary .................................................................................................................................141
Chapter 7. Views .........................................................................................................................143
Restrictions..............................................................................................................................143
ANSI SQL Schema VIEWs....................................................................................................144
Getting a VIEW's Source Code.............................................................................................145
Updatable VIEWs....................................................................................................................146
WITH CHECK OPTION..........................................................................................................146
Derived Tables.........................................................................................................................146
Dynamic VIEWs.......................................................................................................................147
Partitioning Data Using Views...............................................................................................148
Summary .................................................................................................................................150
Chapter 8. Statistical Functions ................................................................................................151
The Case for CASE ................................................................................................................151
Efficiency Concerns ................................................................................................................152
Variance and Standard Deviation.........................................................................................153
Medians ...................................................................................................................................153
Clipping ....................................................................................................................................160
Returning the Top n Rows.....................................................................................................161
Rankings..................................................................................................................................164
Modes.......................................................................................................................................166
Histograms ...............................................................................................................................167
Cumulative and Sliding Aggregates .....................................................................................168
Extremes..................................................................................................................................170
Summary .................................................................................................................................172
Chapter 9. Runs and Sequences .............................................................................................173
Sequences ...............................................................................................................................173
Runs .........................................................................................................................................178
Intervals ...................................................................................................................................180
Summary .................................................................................................................................182
Chapter 10. Arrays......................................................................................................................185
Arrays as Big Strings ..............................................................................................................185
Arrays as Tables......................................................................................................................190
Summary .................................................................................................................................198
Chapter 11. Sets .........................................................................................................................199
Unions ......................................................................................................................................199
Differences ...............................................................................................................................201
Intersections............................................................................................................................202
Subsets ....................................................................................................................................204
Summary .................................................................................................................................207
Chapter 12. Hierarchies .............................................................................................................209
Simple Hierarchies..................................................................................................................209
Multilevel Hierarchies..............................................................................................................210
Indented lists...........................................................................................................................215
Summary .................................................................................................................................216
Chapter 13. Cursors ...................................................................................................................217
On Cursors and ISAMs ..........................................................................................................217
Types of Cursors .....................................................................................................................218
Appropriate Cursor Use .........................................................................................................222
T-SQL Cursor Syntax .............................................................................................................226
Configuring Cursors................................................................................................................234
Updating Cursors ....................................................................................................................238
Cursor Variables......................................................................................................................239
Cursor Stored Procedures .....................................................................................................240
Optimizing Cursor Performance............................................................................................240
Summary .................................................................................................................................242
Chapter 14. Transactions...........................................................................................................243
Transactions Defined..............................................................................................................243
How SQL Server Transactions Work ...................................................................................244
Types of Transactions ............................................................................................................244
Avoiding Transactions Altogether.........................................................................................246
Automatic Transaction Management ...................................................................................246
Transaction Isolation Levels..................................................................................................248
Transaction Commands and Syntax....................................................................................251
Debugging Transactions ........................................................................................................256
Optimizing Transactional Code.............................................................................................257
Summary .................................................................................................................................258
Chapter 15. Stored Procedures and Triggers.........................................................................259
Stored Procedure Advantages..............................................................................................260
Internals ...................................................................................................................................260
Creating Stored Procedures..................................................................................................261
Executing Stored Procedures ...............................................................................................269
Environmental Concerns........................................................................................................270
Parameters..............................................................................................................................272
Important Automatic Variables..............................................................................................275
Flow Control Language ..........................................................................................................276
Errors........................................................................................................................................277
Nesting.....................................................................................................................................279
Recursion.................................................................................................................................280
Autostart Procedures..............................................................................................................281
Encryption................................................................................................................................281
Triggers....................................................................................................................................281
Debugging Procedures...........................................................................................................284
Summary .................................................................................................................................285
Chapter 16. Transact-SQL Performance Tuning ...................................................................287
General Performance Guidelines .........................................................................................287
Database Design Performance Tips ....................................................................................287
Index Performance Tips .........................................................................................................288
SELECT Performance Tips ...................................................................................................290
INSERT Performance Tips ....................................................................................................291
Bulk Copy Performance Tips.................................................................................................291
DELETE and UPDATE Performance Tips ..........................................................................292
Cursor Performance Tips.......................................................................................................292
Stored Procedure Performance Tips ...................................................................................293
SARGs .....................................................................................................................................296
Denormalization......................................................................................................................311
The Query Optimizer ..............................................................................................................325
The Index Tuning Wizard.......................................................................................................333
Profiler......................................................................................................................................334
Perfmon ...................................................................................................................................335
Summary .................................................................................................................................337
Chapter 17. Administrative Transact-SQL ..............................................................................339
GUI Administration.................................................................................................................339
System Stored Procedures....................................................................................................339
Administrative Transact-SQL Commands...........................................................................339
Administrative System Functions .........................................................................................339
Administrative Automatic Variables......................................................................................340
Where's the Beef?...................................................................................................................341
Summary .................................................................................................................................392
Chapter 18. Full-Text Search ....................................................................................................395
Full-Text Predicates................................................................................................................399
Rowset Functions....................................................................................................................402
Summary .................................................................................................................................405
Chapter 19. Ole Automation......................................................................................................407
sp-exporttable ..........................................................................................................................407
sp-importtable ..........................................................................................................................411
sp-getsQLregistry...................................................................................................................415
Summary .................................................................................................................................417
Chapter 20. Undocumented T-SQL .........................................................................................419
Defining Undocumented.........................................................................................................419
Undocumented DBCC Commands ......................................................................................419
Undocumented Functions and Variables ............................................................................430
Undocumented Trace Flags ..................................................................................................433
Undocumented Procedures...................................................................................................434
Summary .................................................................................................................................438
Chapter 21. Potpourri .................................................................................................................439
Obscure Functions..................................................................................................................439
Data Scrubbing.......................................................................................................................448
Iteration Tables........................................................................................................................451
Summary .................................................................................................................................452
Appendix A. Suggested Resources .........................................................................................453
Books .......................................................................................................................................453
Internet Resources..................................................................................................................453
Download this book click here
Another Database books click here
Continue Reading ...
.jpg)
Posts
