INTRODUCTION xxv
CHAPTER 1: RDBMS BASICS: WHAT MAKES UP A SQL SERVER DATABASE? 1
An Overview of Database Objects 2
SQL Server Data Types 13
SQL Server Identifiers for Objects 19
Summary 21
CHAPTER 2: LEARNING THE TOOLS OF THE TRADE 23
Getting Help with Books Online 24
SQL Server Configuration Manager 25
SQL Server Management Studio 31
SQL Server Data Tools (formerly BIDS) 43
SQL Server Integration Services (SSIS) 44
SQL Server Reporting Services (SSRS) 45
SQL Server Analysis Services (SSAS) 45
Bulk Copy Program (BCP) 46
SQL Server Profiler 46
sqlcmd 46
PowerShell 47
Summary 47
CHAPTER 3: THE FOUNDATION STATEMENTS OF T-SQL 49
Getting Started with a Basic SELECT Statement 50
Adding Data with the INSERT Statement 76
Changing What You’ve Got with the UPDATE Statement 84
The DELETE Statement 87
Summary 88
CHAPTER 4: JOINS 91
Combining Table Data with JOINs 92
Selecting Matching Rows with INNER JOIN 93
Retrieving More Data with OUTER JOIN 102
Seeing Both Sides with FULL JOINs 115
Understanding CROSS JOINs 117
Exploring Alternative Syntax for Joins 118
Stacking Results with UNION 121
Summary 125
CHAPTER 5: CREATING AND ALTERING TABLES 129
Object Names in SQL Server 130
The CREATE Statement 134
The ALTER Statement 155
The DROP Statement 164
Using the GUI Tool 165
Summary 171
CHAPTER 6: KEYS AND CONSTRAINTS 175
Types of Constraints 176
Constraint Naming 178
Key Constraints 179
CHECK Constraints 197
DEFAULT Constraints 199
Disabling Constraints 201
Rules and Defaults — Cousins of Constraints 206
Triggers for Data Integrity 210
Choosing What to Use 210
Summary 211
CHAPTER 7: ADDING MORE TO YOUR QUERIES 213
What Is a Subquery? 214
Building Correlated Subqueries 218
Derived Tables 225
Using Common Table Expressions (CTEs) 228
Using the EXISTS Operator 232
Mixing Data Types: CAST and CONVERT 236
Synchronizing Data with the MERGE Command 239
Gathering Affected Rows with the OUTPUT Clause 243
Through the Looking Glass: Windowing Functions 246
One Chunk at a Time: Ad Hoc Query Paging 251
Performance Considerations 252
CHAPTER 8: BEING NORMAL: NORMALIZATION AND OTHER BASIC DESIGN ISSUES 257
Understanding Tables 258
Keeping Your Data “Normal” 258
Understanding Relationships 270
Diagramming Databases 279
Denormalization 291
Beyond Normalization 291
Drawing Up a Quick Example 293
Summary 302
CHAPTER 9: SQL SERVER STORAGE AND INDEX STRUCTURES 305
SQL Server Storage 306
Understanding Indexes 308
Creating, Altering, and Dropping Indexes 323
Choosing Wisely: Deciding Which Index Goes Where and When 331
Maintaining Your Indexes 345
Summary 350
CHAPTER 10: VIEWS 353
Creating Simple Views 354
Editing Views with T-SQL 367
Dropping Views 367
Creating and Editing Views in the Management Studio 367
Auditing: Displaying Existing Code 371
Protecting Code: Encrypting Views 373
About Schema Binding 374
Making Your View Look like a Table with VIEW_METADATA 375
Indexed (Materialized) Views 375
Indexing an Aggregate View 378
Summary 381
CHAPTER 11: WRITING SCRIPTS AND BATCHES 383
Understanding Script Basics 384
Grouping Statements into Batches 399
Running from the Command Prompt: sqlcmd 405
Dynamic SQL: Using the EXEC Command 409
Using Control-of-Flow Statements 415
Summary 431
CHAPTER 12: STORED PROCEDURES 435
Creating the Sproc: Basic Syntax 436
Changing Stored Procedures with ALTER 438
Dropping Sprocs 438
Parameterizing Sprocs 438
More on Dealing with Errors 448
What a Sproc Offers 468
Extended Stored Procedures (XPs) 472
A Brief Look at Recursion 472
Debugging 475
Understanding .NET Assemblies 484
When to Use Stored Procedures 485
Summary 486
CHAPTER 13: USER-DEFINED FUNCTIONS 489
What a UDF Is 489
UDFs Returning a Scalar Value 491
UDFs That Return a Table 496
Debugging User-Defined Functions 506
Using .NET in a Database World 507
Summary 507
CHAPTER 14: TRANSACTIONS AND LOCKS 509
Understanding Transactions 509
How the SQL Server Log Works 514
Understanding Locks and Concurrency 518
Setting the Isolation Level 527
Dealing with Deadlocks (aka “a 1205”) 531
Summary 534
CHAPTER 15: TRIGGERS 537
What Is a Trigger? 538
ON 540
WITH ENCRYPTION 540
FOR|AFTER 540
The FOR|AFTER versus the INSTEAD OF Clause 541
NOT FOR REPLICATION 543
AS 543
Using Triggers for Data Integrity Rules 543
Dealing with Requirements Sourced from Other Tables 544
Using Triggers to Check the Delta of an Update 545
Using Triggers for Custom Error Messages 547
Other Common Uses for Triggers 548
Other Trigger Issues 548
Triggers Can Be Nested 548
Triggers Can Be Recursive 549
Triggers Don’t Prevent Architectural Changes 549
Triggers Can Be Turned Off without Being Removed 550
Trigger Firing Order 550
INSTEAD OF Triggers 552
Performance Considerations 552
Triggers Are Reactive Rather Than Proactive 552
Triggers Don’t Have Concurrency Issues with the Process That Fires Them 553
Using IF UPDATE() and COLUMNS_UPDATED 553
Keep It Short and Sweet 556
Don’t Forget Triggers When Choosing Indexes 556
Try Not to Roll Back within Triggers 556
Dropping Triggers 556
Debugging Triggers 557
Summary 558
CHAPTER 16: A BRIEF XML PRIMER 561
XML Basics 562
What SQL Server Brings to the Party 577
A Brief Word on XSLT 610
Summary 613
CHAPTER 17: BUSINESS INTELLIGENCE FUNDAMENTALS 615
What Is Business Intelligence? 616
Those Who Forget History: The Data Warehouse 619
Dimensional Modeling: Why Be Normal? 624
ETLs 637
Making Your Data Actionable: BI Reporting Techniques 642
Summary 647
CHAPTER 18: BI STORAGE AND REPORTING RESOURCES 649
SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube 661
Building Your First Cube 663
Self-Service BI: User Tools 675
Summary 678
CHAPTER 19: REPORTING FOR DUTY, SIR! A LOOK AT REPORTING SERVICES 681
Reporting Services 101 682
Understanding the SSRS Report Lifecycle 683
Understanding the Reporting Services Architecture 684
Building Simple Report Models 686
Report Server Projects 711
Summary 717
CHAPTER 20: GETTING INTEGRATED WITH INTEGRATION SERVICES 719
Understanding the Problem 720
Using the Import/Export Wizard to Generate Basic Packages 720
Examining Package Basics 727
Executing Packages 738
A Final Word on Packages 745
Summary 745
CHAPTER 21: PLAYING ADMINISTRATOR 747
Scheduling Jobs 748
Logins and Users 760
Backup and Recovery 764
Index Maintenance 770
Policy Based Management 774
Automating Administration Tasks with PowerShell 775
Summary 779
APPENDIX: ANSWERS TO EXERCISES 783
INDEX 807
ONLINE APPENDICES
BONUS APPENDIX 1: SYSTEM FUNCTIONS 1
BONUS APPENDIX 2: VERY SIMPLE CONNECTIVITY EXAMPLES 69
