Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
¦CHAPTER 1 SQL Server 2005 Overview and Installation . . . . . . . . . . . . . . . . . . . . . 1
¦CHAPTER 2 SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
¦CHAPTER 3 Database Design and Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
¦CHAPTER 4 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
¦CHAPTER 5 Defining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
¦CHAPTER 6 Creating Indexes and Database Diagramming . . . . . . . . . . . . . . . . 153
¦CHAPTER 7 Database Backups, Recovery, and Maintenance . . . . . . . . . . . . . . 185
¦CHAPTER 8 Working with the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
¦CHAPTER 9 Building a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
¦CHAPTER 10 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
¦CHAPTER 11 T-SQL Essentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
¦CHAPTER 12 Advanced T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
¦CHAPTER 13 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
¦CHAPTER 14 SQL Server 2005 Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . 451
¦APPENDIX Glossary of Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
¦INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
vii
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
¦CHAPTER 1 SQL Server 2005 Overview and Installation . . . . . . . . . . . . . . . 1
Why SQL Server 2005? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Evolution of SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Hardware Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
CPU. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Hard Disk Space . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Operating System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
A Standard Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Preparing to Install . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Services Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Looking at the Authentication Mode. . . . . . . . . . . . . . . . . . . . . . . . . . 18
The sa Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
¦CHAPTER 2 SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . 25
A Quick Overview of SSMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Examining SSMS’s Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Environment Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Source Control Node. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Text Editor Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Query Execution Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Query Results Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
viii ¦CONTENTS
Query Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Surface Area Configuration Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
¦CHAPTER 3 Database Design and Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Defining a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Databases Within SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
master . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
tempdb. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
msdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
AdventureWorks/AdventureWorksDW . . . . . . . . . . . . . . . . . . . . . . . . 58
Choosing the Database System Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
OLTP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
OLAP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Example System Choice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Gathering the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Determining the Information to Store in the Database . . . . . . . . . . . . . . . 62
Financial Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Customers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Customer Addresses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Shares . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
External and Ignored Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Building Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Using Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Creating Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
More on Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Each Entity Should Have a Unique Identifier . . . . . . . . . . . . . . . . . . . 73
Only Store Information That Directly Relates to That Entity . . . . . . . 73
Avoid Repeating Values or Columns . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Normalization Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Creating the Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Creating a Database in SQL Server Management Studio. . . . . . . . . 76
Dropping the Database in SQL Server Management Studio . . . . . . 89
Creating a Database in a Query Pane. . . . . . . . . . . . . . . . . . . . . . . . . 92
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
¦CONTENTS ix
¦CHAPTER 4 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Server Logins and Database Users. . . . . . . . . . . . . . . . . . . . . . . . . . 105
Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Fixed Server Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Database Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Application Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Before You Can Proceed with Your Solution . . . . . . . . . . . . . . . . . . . . . . 113
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
¦CHAPTER 5 Defining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
What Is a Table? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Defining a Table: SQL Server Management Studio . . . . . . . . . . . . . . . . . 121
Different Table Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Different Program Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Columns Are More Than Simple Data Repositories . . . . . . . . . . . . . . . . 126
Default Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Generating IDENTITY Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
The Use of NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Why Define a Column to Allow NULL? . . . . . . . . . . . . . . . . . . . . . . . 127
Image and Large Text Storage in SQL Server . . . . . . . . . . . . . . . . . . . . . 127
Creating a Table in SQL Server Management Studio . . . . . . . . . . . . . . . 128
Defining a Table Through the Query Editor . . . . . . . . . . . . . . . . . . . . . . . 134
Defining a Table: Using a Template . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Creating and Altering a Template . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
The ALTER TABLE Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Defining the Remaining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Setting a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Creating a Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Check Existing Data on Creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Enforce Foreign Key Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Delete Rule/Update Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Using the ALTER TABLE SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . . 150
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
x ¦CONTENTS
¦CHAPTER 6 Creating Indexes and Database Diagramming . . . . . . . . . . 153
What Is an Index? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Types of Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Uniqueness . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Determining What Makes a Good Index . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Using Low-Maintenance Columns . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Primary and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Finding Specific Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Using Covering Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Looking for a Range of Information . . . . . . . . . . . . . . . . . . . . . . . . . 158
Keeping the Data in Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Determining What Makes a Bad Index . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Using Unsuitable Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Choosing Unsuitable Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Including Too Many Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Including Too Few Records in the Table . . . . . . . . . . . . . . . . . . . . . 159
Reviewing Your Indexes for Performance . . . . . . . . . . . . . . . . . . . . . . . . 160
Creating an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Creating an Index with the Table Designer . . . . . . . . . . . . . . . . . . . 161
Indexes and Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
The CREATE INDEX Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Creating an Index in Query Editor: Template . . . . . . . . . . . . . . . . . . 166
Creating an Index in Query Editor: SQL Code . . . . . . . . . . . . . . . . . 170
Dropping an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Altering an Index in Query Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Diagramming the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Database Diagramming Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
The SQL Server Database Diagram Tool . . . . . . . . . . . . . . . . . . . . . 177
The Default Database Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
The Database Diagram Toolbar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
¦CHAPTER 7 Database Backups, Recovery, and Maintenance . . . . . . . 185
Transaction Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Backup Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
When Problems May Occur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
Taking a Database Offline . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
¦CONTENTS xi
Backing the Data Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Backing Up the Database Using T-SQL . . . . . . . . . . . . . . . . . . . . . . 196
Transaction Log Backup Using T-SQL . . . . . . . . . . . . . . . . . . . . . . . 204
Restoring a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Restoring Using SQL Server Management Studio. . . . . . . . . . . . . . 207
Restoring Using T-SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Detaching and Attaching a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Detaching and Attaching Using SQL Server
Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Detaching and Attaching Using T-SQL . . . . . . . . . . . . . . . . . . . . . . . 220
Producing SQL Script for the Database . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Maintaining Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Creating a Database Maintenance Plan . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
¦CHAPTER 8 Working with the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
The T-SQL INSERT Command Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
INSERT SQL Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Default Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Using NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
DBCC CHECKIDENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Column Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
ADD CONSTRAINT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
Dealing with Several Records at Once . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Inserting Several Records in a Query Batch . . . . . . . . . . . . . . . . . . 262
Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
Using SQL Server Management Studio to Retrieve Data . . . . . . . . . . . . 264
The SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Naming the Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
The First Searches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Varying the Output Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Limiting the Search: The Use of WHERE . . . . . . . . . . . . . . . . . . . . . . . . . 273
SET ROWCOUNT n . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
TOP n . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
TOP n PERCENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Order! Order! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
The LIKE Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
xii ¦CONTENTS
Creating Data: SELECT INTO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Who Can Add, Delete, and Select Data . . . . . . . . . . . . . . . . . . . . . . . . . . 286
Securables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Updating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
The UPDATE Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Updating Data Within Query Editor . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
BEGIN TRAN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
COMMIT TRAN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
ROLLBACK TRAN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Locking Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Updating Data: Using Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . 299
Nested Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
DELETE Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Using the DELETE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Truncating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
¦CHAPTER 9 Building a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
What Is a View? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Using Views for Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Encrypting View Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Creating a View: SQL Server Management Studio . . . . . . . . . . . . . . . . . 312
Creating a View Using a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
CREATE VIEW Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Creating a View: A Query Editor pane . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
Creating a View: SCHEMABINDING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
Indexing a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
¦CHAPTER 10 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
What Is a Stored Procedure? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
CREATE PROCEDURE Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Returning a Set of Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
Creating a Stored Procedure: Management Studio . . . . . . . . . . . . . . . . . 339
¦CONTENTS xiii
Different Methods of Executing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
No EXEC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
With EXEC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Using RETURN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Controlling the Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
IF...ELSE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
BEGIN...END. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
WHILE...BREAK Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
CASE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Bringing It All Together . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
¦CHAPTER 11 T-SQL Essentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Using More Than One Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
COUNT/COUNT_BIG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
SUM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
MAX/MIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
AVG. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
Distinct Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Date and Time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
System Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
RAISERROR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
@@ERROR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
TRY...CATCH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
xiv ¦CONTENTS
¦CHAPTER 12 Advanced T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
IN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
EXISTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
The APPLY Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
CROSS APPLY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
OUTER APPLY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
Common Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Recursive CTE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Pivoting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
PIVOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
UNPIVOT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414
Ranking Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
ROW_NUMBER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
RANK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
DENSE_RANK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
NTILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Using the MAX Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Image LOB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
¦CHAPTER 13 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
What Is a Trigger? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
The DML Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
CREATE TRIGGER Syntax for DML triggers . . . . . . . . . . . . . . . . . . . . . . . 429
Why Not Use a Constraint? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Deleted and Inserted Logical Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Creating a DML FOR Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432
Checking Specific Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Using UPDATE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Using COLUMNS_UPDATED() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
DDL_DATABASE_LEVEL_EVENTS. . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Dropping a DDL trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
EVENTDATA() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
¦CONTENTS xv
¦CHAPTER 14 SQL Server 2005 Reporting Services . . . . . . . . . . . . . . . . . . . . 451
What Is Reporting Services? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
Reporting Services Architecture from 5000 Feet . . . . . . . . . . . . . . . . . . 452
Reporting Services Architecture: A Closer Look . . . . . . . . . . . . . . . . . . . 453
The Application Layer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
The Server Layer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456
The Data Layer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
Building Your First Report Using Report Wizard . . . . . . . . . . . . . . . . . . . 458
Building a Report from Scratch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462
Data-Related Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Report Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Reports Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
¦APPENDIX Glossary of Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Цена: 200руб.