Contents
Database Systems: An Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
Variety of User Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Physical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Logical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6
Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Relational Database Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Working with the Book’s Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
SQL: A Relational Database Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Normal Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Entity-Relationship Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Syntax Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18
SQL Server Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Planning Phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
General Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23
Planning the Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27
Installing SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Introduction to SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . 42
Connecting to a Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
Registered Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Object Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Organizing and Navigating SQL Server Management Studio’s Panes . . . . . . . . . . 46
Using SQL Server Management Studio with the Database Engine . . . . . . . . . . . . 47
Administering Database Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Managing Databases Using Object Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Authoring Activities Using SQL Server Management Studio . . . . . . . . . . . . . . . . 60
Query Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Solution Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
SQL Server Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
SQL’s Basic Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Literal Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73
Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Reserved Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .74
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Numeric Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Character Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Temporal Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Miscellaneous Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Storage Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Transact-SQL Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .82
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .83
Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .83
Scalar Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Global Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91
NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Creating Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Creation of a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
CREATE TABLE: A Basic Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .101
CREATE TABLE and Declarative Integrity Constraints . . . . . . . . . . . . . . . . . . 104
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
Creating Other Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Integrity Constraints and Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Modifying Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
Altering a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
Altering a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Removing Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .130
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
SELECT Statement: Its Clauses and Functions . . . . . . . . . . . . . . . . . . . . . . . 136
WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .159
ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .160
SELECT Statement and IDENTITY Property . . . . . . . . . . . . . . . . . . . . . . . . .163
CREATE SEQUENCE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
CASE Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Subqueries and Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . .175
Subqueries and the IN Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Subqueries and ANY and ALL Operators . . . . . . . . . . . . . . . . . . . . . . . . . . .177
Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Join Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .180
Two Syntax Forms to Implement Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . .180
Natural Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .181
Cartesian Product . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Outer Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .188
Further Forms of Join Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Subqueries and the EXISTS Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . .194
Should You Use Joins or Subqueries? . . . . . . . . . . . . . . . . . . . . . . . . . . . . .195
Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Derived Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Common Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
INSERT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .210
Inserting a Single Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .210
Inserting Multiple Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Table Value Constructors and INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . .214
UPDATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .215
DELETE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Other T-SQL Modification Statements and Clauses . . . . . . . . . . . . . . . . . . . 219
TRUNCATE TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
MERGE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
The OUTPUT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .221
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Procedural Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Block of Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .228
IF Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
WHILE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Miscellaneous Procedural Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Exception Handling with TRY, CATCH, and THROW . . . . . . . . . . . . . . . . . . 233
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .236
Creation and Execution of Stored Procedures . . . . . . . . . . . . . . . . . . . . . . .237
Stored Procedures and CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Creation and Execution of User-Defined Functions . . . . . . . . . . . . . . . . . . . 248
Changing the Structure of UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
User-Defined Functions and CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Introduction to the System Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .260
General Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .262
Catalog Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .262
Dynamic Management Views and Functions . . . . . . . . . . . . . . . . . . . . . . . .265
Information Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Proprietary Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
System Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
System Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Property Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .270
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Clustered Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .276
Nonclustered Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Transact-SQL and Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .278
Creating Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Obtaining Index Fragmentation Information . . . . . . . . . . . . . . . . . . . . . . . .282
Editing Index Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .283
Altering Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Removing and Renaming Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .286
Guidelines for Creating and Using Indices . . . . . . . . . . . . . . . . . . . . . . . . . .287
Indices and Conditions in the WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . 287
Indices and the Join Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .288
Covering Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Special Types of Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .289
Virtual Computed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .290
Persistent Computed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .291
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .292
DDL Statements and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .294
Creating a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .294
Altering and Removing Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Editing Information Concerning Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
DML Statements and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .299
View Retrieval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
INSERT Statement and a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
UPDATE Statement and a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .303
DELETE Statement and a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .306
Creating an Indexed View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .307
Modifying the Structure of an Indexed View . . . . . . . . . . . . . . . . . . . . . . . 309
Editing Information Concerning Indexed Views . . . . . . . . . . . . . . . . . . . . . 310
Benefits of Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .317
Implementing an Authentication Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . .318
Encrypting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Setting Up the Database Engine Security . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
User-Schema Separation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
DDL Schema-Related Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .328
Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .330
Managing Database Security Using Management Studio . . . . . . . . . . . . . . . .331
Managing Database Security Using Transact-SQL Statements . . . . . . . . . . . .332
Default Database Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .333
Fixed Server Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Fixed Database Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Application Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
User-Defined Server Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .339
User-Defined Database Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .340
Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
GRANT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .342
DENY Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .346
REVOKE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .347
Managing Permissions Using Management Studio . . . . . . . . . . . . . . . . . . . . 348
Managing Authorization and Authentication of Contained Databases . . . . . . .349
Change Tracking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Data Security and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .354
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Concurrency Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .360
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
Properties of Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .362
Transact-SQL Statements and Transactions . . . . . . . . . . . . . . . . . . . . . . . . 363
Transaction Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .366
Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .367
Lock Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .368
Lock Granularity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
Lock Escalation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
Affecting Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372
Displaying Lock Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
Deadlock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .374
Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Concurrency Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
The Database Engine and Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . 376
Row Versioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
READ COMMITTED SNAPSHOT Isolation Level . . . . . . . . . . . . . . . . . . . . . . 379
SNAPSHOT Isolation Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Creating a DML Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Modifying a Trigger’s Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .385
Using deleted and inserted Virtual Tables . . . . . . . . . . . . . . . . . . . . . . . . . 386
Application Areas for DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .387
AFTER Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .387
INSTEAD OF Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
First and Last Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .392
DDL Triggers and Their Application Areas . . . . . . . . . . . . . . . . . . . . . . . . . 393
Database-Level Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Server-Level Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Triggers and CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .400
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .401
System Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
master Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
model Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .407
tempdb Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .407
msdb Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
Disk Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
Properties of Data Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .409
Types of Data Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .412
Parallel Processing of Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .414
Utilities and the DBCC Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
bcp Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
sqlcmd Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
sqlservr Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
DBCC Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .419
Policy-Based Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .421
Key Terms and Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .421
Using Policy-Based Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .425
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .425
Reasons for Data Loss . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
Introduction to Backup Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
Full Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .429
Differential Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .430
Transaction Log Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
File or Filegroup Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Performing Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .432
Backing Up Using Transact-SQL Statements . . . . . . . . . . . . . . . . . . . . . . . 432
Backing Up Using Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Determining Which Databases to Back Up . . . . . . . . . . . . . . . . . . . . . . . . .439
Performing Database Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .440
Automatic Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .441
Manual Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .441
Recovery Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .450
System Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Using a Standby Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .454
Using RAID Technology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .455
Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .457
Failover Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
Log Shipping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
High-Availability and Disaster Recovery (HADR) . . . . . . . . . . . . . . . . . . . . .458
Maintenance Plan Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .460
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .463
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
Starting SQL Server Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .469
Creating Jobs and Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Creating a Job and Its Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Creating a Job Schedule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Notifying Operators About the Job Status . . . . . . . . . . . . . . . . . . . . . . . . . 475
Viewing the Job History Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .475
Alerts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .477
Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .477
SQL Server Agent Error Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .479
Windows Application Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .479
Defining Alerts to Handle Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .480
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .484
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .485
Distributed Data and Methods for Distributing . . . . . . . . . . . . . . . . . . . . . .488
SQL Server Replication: An Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . .490
Publishers, Distributors, and Subscribers . . . . . . . . . . . . . . . . . . . . . . . . . 490
Publications and Articles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .492
The Distribution Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .493
Agents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
Replication Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .495
Replication Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .499
Managing Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .502
Configuring the Distribution and Publication Servers . . . . . . . . . . . . . . . . . 502
Setting Up Publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .504
Configuring Subscription Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .504
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506
Phases of Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508
How Query Optimization Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .509
Query Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .510
Index Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510
Join Order Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .514
Join Processing Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .514
Plan Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516
Tools for Editing the Optimizer Strategy . . . . . . . . . . . . . . . . . . . . . . . . . .517
SET Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518
Management Studio and Graphical Execution Plans . . . . . . . . . . . . . . . . . .522
Examples of Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .523
Dynamic Management Views and Query Optimizer . . . . . . . . . . . . . . . . . . 528
Optimization Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
Why Use Optimization Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
Types of Optimization Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 540
Factors That Affect Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .542
Database Applications and Performance . . . . . . . . . . . . . . . . . . . . . . . . . 543
The Database Engine and Performance . . . . . . . . . . . . . . . . . . . . . . . . . .545
System Resources and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
Monitoring Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .550
Performance Monitor: An Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . .550
Monitoring the CPU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552
Monitoring Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554
Monitoring the Disk System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556
Monitoring the Network Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
Choosing the Right Tool for Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . 560
SQL Server Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 560
Database Engine Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .561
Other Performance Tools of SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . .569
Performance Data Collector . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569
Resource Governor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577
Online Transaction Processing vs. Business Intelligence . . . . . . . . . . . . . . 582
Online Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
Business Intelligence Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583
Data Warehouses and Data Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .584
Data Warehouse Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .587
Cubes and Their Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590
Aggregation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
Physical Storage of a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .593
Data Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596
SSAS Terminology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .598
Developing a Multidimensional Cube Using BIDS . . . . . . . . . . . . . . . . . . . 600
Create a BI Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Identify Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
Specify Data Source Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .603
Create a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .607
Design Storage Aggregation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .608
Process the Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 610
Browse the Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611
Retrieving and Delivering Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .613
Querying Data Using PowerPivot for Excel . . . . . . . . . . . . . . . . . . . . . . . .615
Querying Data Using Multidimensional Expressions . . . . . . . . . . . . . . . . . .621
Security of SQL Server Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . 623
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625
Window Construct . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .628
Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .630
Ordering and Framing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
Extensions of GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
CUBE Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
ROLLUP Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638
Grouping Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
Grouping Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
OLAP Query Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
Ranking Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
Statistical Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
Standard and Nonstandard Analytic Functions . . . . . . . . . . . . . . . . . . . . . .647
TOP Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
OFFSET/FETCH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
NTILE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
Pivoting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .653
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657
Introduction to Data Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660
SQL Server Reporting Services Architecture . . . . . . . . . . . . . . . . . . . . . . .661
Reporting Services Windows Service . . . . . . . . . . . . . . . . . . . . . . . . . . . .662
The Report Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .663
Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663
Configuration of SQL Server Reporting Services . . . . . . . . . . . . . . . . . . . .664
Creating Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .665
Creating Reports with the Report Server Project Wizard . . . . . . . . . . . . . . 667
Creating Parameterized Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
Managing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .678
On-Demand Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 678
Report Subscription . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 678
Report Delivery Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 681
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 682
Data Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .684
How the Database Engine Partitions Data . . . . . . . . . . . . . . . . . . . . . . . . 685
Steps for Creating Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . .685
Partitioning Techniques for Increasing System Performance . . . . . . . . . . . .692
Guidelines for Partitioning Tables and Indices . . . . . . . . . . . . . . . . . . . . . .693
Star Join Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 694
Columnstore Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 696
Managing Columnstore Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .697
Advantages and Limitations of Columnstore Indices . . . . . . . . . . . . . . . . . 699
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 700
XML: Basic Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 706
Requirements of a Well-Formed XML Document . . . . . . . . . . . . . . . . . . . .706
XML Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .708
XML Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709
XML Namespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 710
XML and World Wide Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 711
XML-Related Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .711
Schema Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
Document Type Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 712
XML Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .714
Storing XML Documents in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . 715
Storing XML Documents Using the XML Data Type . . . . . . . . . . . . . . . . . .717
Storing XML Documents Using Decomposition . . . . . . . . . . . . . . . . . . . . .723
Presenting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .724
Presenting XML Documents as Relational Data . . . . . . . . . . . . . . . . . . . . 725
Presenting Relational Data as XML Documents . . . . . . . . . . . . . . . . . . . . 725
Querying Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 732
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .734
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .736
Models for Representing Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . .737
GEOMETRY Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 737
GEOGRAPHY Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739
GEOMETRY vs. GEOGRAPHY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .739
External Data Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 740
Working with Spatial Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .741
Working with the GEOMETRY Data Type . . . . . . . . . . . . . . . . . . . . . . . . 741
Working with the GEOGRAPHY Data Type . . . . . . . . . . . . . . . . . . . . . . . 745
Working with Spatial Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .745
Displaying Information Concerning Spatial Data . . . . . . . . . . . . . . . . . . . 748
New Spatial Data Features in SQL Server 2012 . . . . . . . . . . . . . . . . . . . .750
New Subtypes of Circular Arcs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 750
New Spatial Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .752
New System Stored Procedures Concerning Spatial Data . . . . . . . . . . . . .752
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 756
Tokens, Word Breakers, and Stop Lists . . . . . . . . . . . . . . . . . . . . . . . . . 757
Operations on Tokens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 758
Relevance Score . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 760
How SQL Server FTS Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 760
Indexing Full-Text Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 761
Indexing Full-Text Data Using Transact-SQL . . . . . . . . . . . . . . . . . . . . . .761
Index Full-Text Data Using SQL Server Management Studio . . . . . . . . . . .765
Querying Full-Text Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 768
FREETEXT Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .769
CONTAINS Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 770
FREETEXTTABLE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 772
CONTAINSTABLE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773
Troubleshooting Full-Text Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .775
New Features in SQL Server 2012 FTS . . . . . . . . . . . . . . . . . . . . . . . . . .777
Customizing a Proximity Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .777
Searching Extended Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 778
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 781
Introduction
There are a couple of reasons why SQL Server, the system that comprises the Database Engine, Analysis Services, Reporting Services, Integration Services, and SQLXML, is the best choice for a broad spectrum of end users and database programmers building business applications:
CC SQL Server is certainly the best system for Windows operating systems, because of its tight integration (and low pricing). Because the number of installed Windows systems is enormous and still increasing rapidly, SQL Server is a widely used database system.
CC The Database Engine, as the relational database system component, is the easiest database system to use. In addition to the well-known user interface, Microsoft offers several different tools to help you create database objects, tune your database applications, and manage system administration tasks.
Generally, SQL Server isn’t only a relational database system. It is a platform that not only manages structured, semistructured, and unstructured data but also offers comprehensive, integrated operational and analysis software that enables organizations to reliably manage mission-critical information.
Goals of the Book
Microsoft SQL Server 2012: A Beginner’s Guide follows four previous editions that covered SQL Server 7, 2000, 2005, and 2008. Generally, all SQL Server users who want to get a good understanding of this database system and to work successfully with it will find this book very helpful. If you are a new SQL Server user but understand SQL, read the section “Differences Between SQL and Transact-SQL Syntax” later in this introduction.
This book addresses users of all components of the SQL Server system. For this reason, it is divided into several parts: The first three parts are most useful to users who want to learn more about Microsoft’s relational database component called the Database Engine. The fourth part of the book is dedicated to business intelligence (BI) users who use either Analysis Services or relational extensions concerning BI.
The last part of the book provides insight for users who want to learn features beyond the relational data, such as XML technologies, spatial data, and how to search data in documents.
SQL Server 2012 has a lot of new features, and almost all of them are discussed in this book. For each feature, at least one running example is provided to enable you to understand that feature better. The following table lists the chapters that describe new features and provides a brief summary of the new features introduced in each chapter. (The table also contains features from SQL Server 2008 Release 2.)
Chapter 2 The installation process of SQL Server 2012 in general and the use of Upgrade Advisor in particular are described in this chapter. (Upgrade Advisor analyzes all components of previous releases that are installed and identifies issues to fix before you upgrade to SQL Server 2012.)
Chapter 3 Management Studio Debugger has been enhanced in SQL Server 2012. The new debugger features described in this chapter are the specification of a breakpoint condition, breakpoint hit count, breakpoint filter, and breakpoint action, as well as the use of the QuickWatch window.
Chapter 5 This chapter describes contained databases in general and partially contained databases, a new feature of SQL Server 2012, in particular. (For an example of how to create such databases, see Example 5.20.)
Chapter 6 This chapter introduces two new clauses of the SELECT statement: OFFSET and FETCH. It also introduces sequences and their creation in the section “CREATE SEQUENCE Statement.”
Chapter 8 Exception handling of the Database Engine in SQL Server 2012 is enhanced with the new statement called THROW (see Example 8.4). The use of the OFFSET and FETCH clauses for server-side paging is shown in Example 8.5. The extension of the EXECUTE statement with the RESULT SETS option is shown in Example 8.11.
Chapter 9 The section “Dynamic Management Views and Functions” describes two new views: sys.dm_exec_describe_first_result_set and sys.dm_db_uncontained_entites (see Example 9.4).
Chapter 12 This chapter introduces the CREATE SERVER ROLE statement, which is used to create user-defined server roles. Also, the management of authorization and authentication of contained databases (see Chapter 5) is described.
Chapter 16 This chapter describes one of the most important new features in SQL Server 2012: high availability and disaster recovery (HADR). HADR overcomes the drawbacks of database mirroring and allows you to maximize availability for your databases.
Chapter 22 This chapter introduces the new and powerful tool for querying analytical data: PowerPivot for Excel. This tool allows you to analyze data using the most popular Microsoft tool for such purpose, Microsoft Excel. PowerPivot for Excel was introduced for the first time in SQL Server 2008 R2.
Chapter 23 This chapter describes new window functions. First, the window frame with its clauses (CURRENT ROW, UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) is explained using an example. After that, the differences between the ROWS and RANGE clauses are listed. The new functions, LEAD and LAG are explained, too.
Chapter 24 Shared datasets, which were introduced for the first time in SQL Server 2008 R2, are discussed in this chapter.
Chapter 25 The final part of this chapter, which is entirely new material, describes columnstore indices.
Chapter 27 The last section of this chapter, “New Spatial Data Features in SQL Server 2012,” describes three new subtypes of circular arcs (compound strings, compound curves, and curve polygons), a new spatial index, and two new system stored procedures concerning spatial data.
Chapter 28 The last section of this chapter, “New Features in SQL Server 2012 FTS,” introduces two enhancements to full-text search: customizing a proximity search and searching extended properties.
Organization of the Book
The book has 28 chapters and is divided into five parts.
Part I, “Basic Concepts and Installation,” describes the notion of database systems and explains how to install SQL Server 2012 and its components. It includes the following chapters:
Chapter 1, “Relational Database Systems: An Introduction,” discusses databases in general and the Database Engine in particular. The notion of normal forms and the sample database are presented here. The chapter also introduces the syntax conventions that are used in the rest of the book.
Chapter 2, “Planning the Installation and Installing SQL Server,” describes the first system administration task: the installation of the overall system. Although the installation of SQL Server is a straightforward task, there are certain steps that warrant explanation.
Chapter 3, “SQL Server Management Studio,” describes the component called SQL Server Management Studio. This component is presented early in the book in case you want to create database objects and query data without knowledge of SQL.
Part II, “Transact-SQL Language,” is intended for end users and application programmers of the Database Engine. It comprises the following chapters:
Chapter 4, “SQL Components,” describes the fundamentals of the most important part of a relational database system: a database language. For all such systems, there is only one language that counts: SQL. In this chapter, all components of SQL Server’s own database language, called Transact-SQL, are described. You can also find the basic language concepts and data types in this chapter. Finally, system functions and operators of Transact-SQL are described.
Chapter 5, “Data Definition Language,” describes all data definition language (DDL) statements of Transact-SQL. The DDL statements are presented in three groups, depending on their purpose. The first group contains all forms of the CREATE statement, which is used to create database objects. The second group contains all forms of the ALTER statement, which is used to modify the structure of some database objects. The third group contains all forms of the DROP statement, which is used to remove different database objects.
Chapter 6, “Queries,” discusses the most important Transact-SQL statement: SELECT. This chapter introduces you to database data retrieval and describes the use of simple and complex queries. Each SELECT clause is separately defined and explained with reference to the sample database.
Chapter 7, “Modification of a Table’s Contents,” discusses the four Transact- SQL statements used for updating data: INSERT, UPDATE, DELETE, and MERGE. Each of these statements is explained through numerous examples.
Chapter 8, “Stored Procedures and User-Defined Functions,” describes procedural extensions, which can be used to create powerful programs called stored procedures and user-defined functions (UDFs), programs that are stored on the server and can be reused. Because Transact-SQL is a complete computational language, all procedural extensions are inseparable parts of the language. Some stored procedures are written by users; others are provided by Microsoft and are referred to as system stored procedures. The implementation of stored procedures and UDFs using the Common Language Runtime (CLR) is also discussed in this chapter.
Chapter 9, “System Catalog,” describes one of the most important parts of a database system: system tables and views. The system catalog contains tables that are used to store the information concerning database objects and their relationships. The main characteristic of system tables of the Database Engine is that they cannot be accessed directly. The Database Engine supports several interfaces that you can use to query the system catalog.
Chapter 10, “Indices,” covers the first and most powerful method that database application programmers can use to tune their applications to get better system response and therefore better performance. This chapter describes the role of indices and gives you guidelines for how to create and use them. The end of the chapter introduces the special types of indices supported by the Database Engine.
Chapter 11, “Views,” explains how you create views, discusses the practical use of views (using numerous examples), and explains a special form of views called indexed views.
Chapter 12, “Security System of the Database Engine,” provides answers to all your questions concerning security of data in the database. It addresses questions about authorization (which user has been granted legitimate access to the database system) and authentication (which access privileges are valid for a particular user).
Three Transact-SQL statements are discussed in this chapter, GRANT, DENY,and REVOKE, which provide the access privileges of database objects against unauthorized access. The end of the chapter explains how data changes can be
tracked using the Database Engine.
Chapter 13, “Concurrency Control,” describes concurrency control in depth. The beginning of the chapter discusses the two different concurrency models supported by the Database Engine. All Transact-SQL statements related to transactions are also explained. Locking as a method to solve concurrency control problems is discussed further. At the end of the chapter, you will learn what isolation levels and row versions are.
Chapter 14, “Triggers,” describes the implementation of business logic using triggers. Each example in this chapter concerns a problem that you may face in your everyday life as a database application programmer. The implementation of managed code for triggers using CLR is also shown in the chapter.
Part III, “SQL Server: System Administration,” describes all objectives of Database Engine system administration. It comprises the following chapters:
Chapter 15, “System Environment of the Database Engine,” discusses some internal issues concerning the Database Engine. It provides a detailed description of the Database Engine disk storage elements, system databases, and utilities.
Chapter 16, “Backup, Recovery, and System Availability,” provides an overview of the fault-tolerance methods used to implement a backup strategy using either SQL Server Management Studio or corresponding Transact-SQL statements.
The first part of the chapter specifies the different methods used to implement a backup strategy. The second part of the chapter discusses the restoration of databases. The final part of the chapter describes in detail the following options available for system availability: failover clustering, database mirroring, log shipping, and high availability and disaster recovery (HADR).
Chapter 17, “Automating System Administration Tasks,” describes the Database Engine component called SQL Server Agent that enables you to automate certain system administration jobs, such as backing up data and using the scheduling and alert features to notify operators. This chapter also explains how to create jobs, operators, and alerts.
Chapter 18, “Data Replication,” provides an introduction to data replication, including concepts such as the publisher and subscriber. It introduces the different models of replication, and serves as a tutorial for how to configure publications and subscriptions using the existing wizards.
Chapter 19, “Query Optimizer,” describes the role and the work of the query optimizer. It explains in detail all the Database Engine tools (the SET statement, SQL Server Management Studio, and various dynamic management views)
that can be used to edit the optimizer strategy. The end of the chapter provides optimization hints.
Chapter 20, “Performance Tuning,” discusses performance issues and the tools for tuning the Database Engine that are relevant to daily administration of the system. After introductory notes concerning the measurements of performance, this chapter describes the factors that affect performance and presents tools for monitoring SQL Server.
Part IV, “SQL Server and Business Intelligence,” discusses business intelligence (BI) and all related topics. The chapters in this part of the book introduce Microsoft Analysis Services and Microsoft Reporting Services. SQL/OLAP and existing optimization techniques concerning relational data storage are described in detail, too.
This part includes the following chapters:
Chapter 21, “Business Intelligence: An Introduction,” introduces the notion of data warehousing. The first part of the chapter explains the differences between online transaction processing and data warehousing. The data store for a data warehousing process can be either a data warehouse or a data mart. Both types of data stores are discussed, and their differences are listed in the second part of the chapter. The data warehouse design is explained at the end of the chapter.
Chapter 22, “SQL Server Analysis Services,” discusses the architecture of Analysis Services and the main component of Analysis Services, Business Intelligence Development Studio (BIDS). The development of a cube using BIDS is shown using two examples. At the end of the chapter, several ways to retrieve and deliver data to users are shown.
Chapter 23, “Business Intelligence and Transact-SQL,” explains how you can use Transact-SQL to solve business intelligence problems. This chapter discusses the window construct, with its partitioning, ordering and framing, CUBE and ROLLUP operators, rank functions, the TOP n clause, and the PIVOT relational operator.
Chapter 24, “SQL Server Reporting Services,” describes the Microsoft enterprise reporting solution. This component is used to design and deploy reports. The chapter discusses the development environment that you use to design and create reports, and shows you different ways to deliver a deployed report.
Chapter 25, “Optimizing Techniques for Relational Online Analytical Processing,” describes three of the several specific optimization techniques that can be used especially in the area of business intelligence: data partitioning, star
join optimization, and columnstore indices. The data partitioning technique called range partitioning is described. In relation to star join optimization, the role of bitmap filters in the optimization of joins is explained. The final part of the chapter explains the use of columnstore indices. You will see how to create such an index and use it to increase the performance of a specific group of analytical queries.
Part V, “Beyond Relational Data,” is dedicated to three “nonrelational” topics, XML, spatial data, and full-text search, because SQL Server, as a data platform, doesn’t have to handle only relational data. The following chapters are included in this part:
Chapter 26, “SQL Server and XML,” discusses SQLXML, Microsoft’s set of data types and functions that supports XML in SQL Server, bridging the gap between XML and relational data. The beginning of the chapter introduces the standardized data type called XML and explains how stored XML documents can be retrieved. After that, the presentation of relational data as XML documents is discussed in detail. At the end of the chapter you will find a description of the methods that can be used to query XML data.
Chapter 27, “Spatial Data,” discusses spatial data and two different data types (GEOMETRY and GEOGRAPHY) that can be used to create such data. Several different standardized functions in relation to spatial data are also shown.
Chapter 28, “SQL Server Full-Text Search,” first discusses general concepts related to full-text search. The second part describes the general steps that are required to create a full-text index and then demonstrates how to apply those steps first using Transact-SQL and then using SQL Server Management Studio. The rest of the chapter is dedicated to full-text queries. It describes two predicates and two row functions that can be used for full-text search. For these predicates and functions, several examples are provided to show how you can solve specific problems in relation to extended operations on documents.
Almost all chapters include at their end numerous exercises that you can use to improve your knowledge concerning the chapter’s content. All solutions to the given exercises can be found both at McGraw-Hill Professional’s web site (mhprofessional.com/computingdownload) and at my own home page (fh-rosenheim.de/~petkovic) .
Changes from the Previous Edition
If you are familiar with the previous edition of this book, Microsoft SQL Server 2008:
A Beginner’s Guide, you should be aware that I have made significant changes in this edition. To make the book easier to use, I separated some topics and described them in totally new chapters. (For instance, Chapter 28 is an entirely new chapter and describes full-text search in depth.) The following table gives you an outline of significant structural changes in the book (minor changes aren’t listed).
Chapter 4 An entirely new section, “Storage Options,” describes two different storage options available as of SQL Server 2008: FILESTREAM and sparse columns. The FILESTREAM storage option supports the management of large objects, which are stored in the NTFS file system, while sparse columns help to minimize data storage space. (These columns provide an optimized way to store column values that are predominantly NULL.)
Chapter 7 The Transact-SQL data modification statements TRUNCATE TABLE and MERGE are now described together, in the final section of the chapter, “Other T-SQL Modification Statements and Clauses.”
Chapter 10 All existing special types of indices are listed in the final section of the chapter, “Special Types of Indices.” Some types are described in this chapter, while for the other types a cross reference is provided to the chapter in which their description can be found.
Chapter 15 The Declarative Management Framework, which was covered in Chapter 16 of the previous edition of the book, has been renamed Policy-Based Management and its coverage has been moved to this chapter.
Chapter 16, “Managing Instances and Maintaining Databases,” from the prior edition has been eliminated from this edition and its material that is relevant to SQL Server 2012 has been redistributed to other chapters. Consequently, Chapters 17 through 26 of the prior edition are now numbered Chapters 16 through 25, respectively, in this edition. The new chapter numbers are reflected in the left column of this table.)
Chapter 16 Coverage of the Maintenance Plan Wizard has been moved from Chapter 16 of the previous edition and placed in this chapter (which was Chapter 17 in the prior edition).
Chapter 18 The structure of the chapter has been significantly changed. Methods for distributing data are now streamlined and discussed at the beginning of the chapter.
Chapter 19 This chapter includes a new section called “Plan Caching.” The section has been enhanced with a new example that shows how you can influence the execution of queries.
Chapter 20 For each section concerning monitoring system resources (CPU, I/O, and network), several examples concerning dynamic management views have been added.
Chapter 22 This chapter has been significantly revised from the previous edition (in which it was Chapter 23). A new main section has been added, “Retrieving and Delivering Data,” which introduces PowerPivot for Excel and describes the Multidimensional Expressions (MDX) language. Also, there is a new section concerning security of SQL Server Analysis Services.
Chapter 23 A new section called “Ordering and Framing” replaces the old one (“Ordering”).
Chapter 24 A new main section called “Managing Reports” describes how reports can be delivered.
Chapter 25 In addition to the new topic “Columnstore Index,” the section “Star Join Optimization” has been enhanced with several examples.
Chapter 26 Chapter 27, “Overview of XML,” and Chapter 28, “SQL Server and XML,” from the prior edition were streamlined and merged into this single chapter, retaining the title “SQL Server and XML.” Two new main sections have been added, which describe all features concerning presentation and retrieval of data.
Chapter 27 This chapter, which was Chapter 29 in the previous edition, has been rewritten from scratch to provide more extensive coverage of spatial data.
Chapter 28 This is a new chapter in this edition, addressing an entirely new topic: SQL Server Full-Text search.
