Contents
Part I Basic Concepts and Installation
Chapter 1 Relational Database Systems—An Introduction . . . . . . . . . . . . . . . . . . 3
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
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Chapter 2 Planning the Installation and Installing SQL Server . . . . . . . . . . . . . . . . . 21
Planning the Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Purpose of the SQL Server System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Hardware and Network Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
SQL Server Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Installation Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Installing SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Before Starting the Setup Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Starting the Setup Program and Installing Prerequisites . . . . . . . . . . . . . . . . . . . 28
Installing SQL Server Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Starting and Stopping an Instance of Database Engine . . . . . . . . . . . . . . . . . . . . . . . . . 37
Dedicated Connection to an Instance of Database Engine . . . . . . . . . . . . . . . . . . . . . . . 38
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Chapter 3 SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
SQL Server Program Group and Books Online . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Introduction to SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Connecting to a Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Registered Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Object Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Organizing and Navigating Management Studio’s Panes . . . . . . . . . . . . . . . . . . 45
Using Management Studio with Database Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Administering Database Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Managing Databases Using Object Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Authoring Activities Using SQL Server Management Studio . . . . . . . . . . . . . . . . . 57
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Part II Transact-SQL Language
Chapter 4 SQL Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
SQL’s Basic Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Literal Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Reserved Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Numeric Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Character Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Temporal Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Miscellaneous Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
DECIMAL with VARDECIMAL Storage Format . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Transact-SQL Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Scalar Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Global Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Chapter 5 Data Definition Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Creating Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Creation of a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
CREATE TABLE: A Basic Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
CREATE TABLE and Declarative Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . 99
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Creating Other Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Integrity Constraints and Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Modifying Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Altering a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Altering a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Removing Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Chapter 6 Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
SELECT Statement: A Basic Form and the WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . 126
WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Subqueries and Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Subqueries and the IN Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Subqueries and ANY and ALL Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
SELECT Statement: Other Clauses and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
SELECT Statement and IDENTITY Property . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
CASE Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
COMPUTE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Join Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Two Syntax Forms to Implement Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Natural Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Cartesian Product . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Outer Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Further Forms of Join Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Subqueries and the EXISTS Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Should You Use Joins or Subqueries? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Derived Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Common Table Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Chapter 7 Modification of a Table’s Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
INSERT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Inserting a Single Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Inserting Multiple Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Table Value Constructors and INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
UPDATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
DELETE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
TRUNCATE TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
The OUTPUT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
MERGE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Chapter 8 Stored Procedures and User-Defined Functions . . . . . . . . . . . . . . . . . . . 215
Procedural Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Block of Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
IF Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
WHILE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Miscellaneous Procedural Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Handling Events with TRY and CATCH Statements . . . . . . . . . . . . . . . . . . . . . . . 221
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Creation and Execution of Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Stored Procedures and CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Creation and Execution of User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . 233
User-Defined Functions and CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Chapter 9 System Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Introduction to the System Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
General Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Catalog Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Dynamic Management Views and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Information Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Proprietary Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
System Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
System Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Property Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Chapter 10 Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
Clustered Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Nonclustered Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
Transact-SQL and Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Creating Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Obtaining Index Fragmentation Information . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Editing Index Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Altering Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
Removing and Renaming Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Guidelines for Creating and Using Indices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Indices and Conditions in the WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Indices and Join Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
Covering Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
Indices on Computed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Virtual Computed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Persistent Computed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
Chapter 11 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
DDL Statements and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Creating a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Altering and Removing Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Editing Information Concerning Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
DML Statements and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
View Retrieval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 286
INSERT Statement and a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
UPDATE Statement and a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
DELETE Statement and a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Creating an Indexed View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Modifying the Structure of an Indexed View . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Editing Information Concerning Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . 296
Benefits of Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Chapter 12 Security System of Database Engine . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Implementing an Authentication Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Encrypting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Setting Up System Security Using DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Managing Logins Using Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
User-Schema Separation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
DDL Schema-Related Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Setting Up Database User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Default Database Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Fixed Server Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
Fixed Database Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Application Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
User-Defined Database Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
GRANT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
DENY Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
REVOKE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Managing Permissions Using Management Studio . . . . . . . . . . . . . . . . . . . . . . 333
Tracking Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Data Security and Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Chapter 13 Concurrency Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
Concurrency Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Properties of Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Transact-SQL Statements and Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Transaction Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Lock Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Lock Granularity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
Lock Escalation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Affecting Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Displaying Lock Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Deadlock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Concurrency Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Database Engine and Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Row Versioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
READ COMMITTED SNAPSHOT Isolation Level . . . . . . . . . . . . . . . . . . . . . . . . . . 363
SNAPSHOT Isolation Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Chapter 14 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Creating a DML Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Modifying a Trigger’s Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Using Deleted and Inserted Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
Application Areas for DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
AFTER Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
INSTEAD OF Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
First and Last Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Database-Level Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Server-Level Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Triggers and CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Part III SQL Server: System Administration
Chapter 15 System Environment of Database Engine . . . . . . . . . . . . . . . . . . . . . . . 389
System Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
master Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
model Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
tempdb Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
msdb Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Disk Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Properties of Data Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
Types of Data Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Parallel Processing of Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Utilities and the DBCC Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
bcp Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
sqlcmd Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
DBCC Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Chapter 16 Managing Instances and Maintaining Databases . . . . . . . . . . . . . . . . . . 405
Declarative Management Framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Key Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Using DMF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Maintenance Plan Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Chapter 17 Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Backup Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Full Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Differential Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Transaction Log Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
File or Filegroup Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
Performing Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
Backing Up Using Transact-SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Backing Up Using Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Determining Which Databases to Back Up . . . . . . . . . . . . . . . . . . . . . . . . . . . 424
Performing Database Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Automatic Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Manual Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Recovery Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
High Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Using a Standby Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Using RAID Technology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440
Failover Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441
Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442
Log Shipping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Chapter 18 Automating System Administration Tasks . . . . . . . . . . . . . . . . . . . . . . 447
Running and Configuring SQL Server Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449
Creating Jobs and Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Creating a Job and Its Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Creating a Job Schedule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Notifying Operators about the Job Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
Viewing the Job History Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Alerts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
SQL Server Agent Error Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458
Windows Application Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Defining Alerts to Handle Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464
Chapter 19 Data Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
Distributed Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466
Methods for Distributing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466
Choosing a Data Distribution Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 467
Replication—An Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
Publishers, Distributors, and Subscribers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
Publications and Articles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Replication Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Replication Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476
Managing Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478
Configuring the Distribution and Publication Servers . . . . . . . . . . . . . . . . . . . . . 478
Setting Up Publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 480
Configuring Subscription Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 480
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Chapter 20 Query Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Phases of Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486
How Query Optimization Works . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487
Query Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488
Index Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488
Join Order Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Join Processing Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Tools for Editing the Optimizer Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
SET Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
Management Studio and Graphical Execution Plans . . . . . . . . . . . . . . . . . . . . . . 499
Examples of Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
Dynamic Management Views and Query Optimizer . . . . . . . . . . . . . . . . . . . . . . 504
Optimization Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Why to Use Optimization Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Types of Optimization Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515
Chapter 21 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517
Factors that Affect Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518
Database Applications and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519
Database Engine and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521
System Resources and Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522
Monitoring Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 526
Performance Monitor: An Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 526
Monitoring the CPU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
Monitoring Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
Monitoring the Disk System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
Monitoring the Network Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532
Choosing the Right Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
SQL Server Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
Database Engine Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
SQL Server 2008 Performance Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
Performance Data Collector . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
Resource Governor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 543
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
Part IV SQL Server and Business Intelligence
Chapter 22 Business Intelligence—An Introduction . . . . . . . . . . . . . . . . . . . . . . . 549
Online Transaction Processing vs. Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . 550
Online Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
Business Intelligence Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551
Data Warehouses and Data Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552
Data Warehouse Design Using Dimensional Model . . . . . . . . . . . . . . . . . . . . . . . . . . . 554
Cubes and Their Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
How Much to Aggregate? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
Physical Storage of a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
Data Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563
Chapter 23 Microsoft Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565
BI Development Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566
Creating a New Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567
Creating a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
Creating a Data Source View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569
Analysis Services and Cubes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
Creating a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
Designing Storage Aggregation and Processing the Cube . . . . . . . . . . . . . . . . . . 573
Browsing a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577
Chapter 24 Business Intelligence and Transact-SQL . . . . . . . . . . . . . . . . . . . . . . . . 579
Introduction to SQL/OLAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 580
Window Construct . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581
Extensions of GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587
CUBE Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587
ROLLUP Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
GROUPING Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590
Grouping SETS Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
OLAP Query Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
Ranking Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
Statistical Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596
Nonstandard Analytic Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597
TOP Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597
NTILE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600
PIVOT and UNPIVOT Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
Chapter 25 Microsoft Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Introduction to Microsoft Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
Report Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
Report Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
Creating Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608
Creating Reports with the Report Server Project Wizard . . . . . . . . . . . . . . . . . . . 610
Creating Parameterized Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
Processing and Managing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 618
Accessing and Delivering Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
On-Demand Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 619
Report Subscription . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 621
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622
Chapter 26 Optimizing Techniques for Business Intelligence . . . . . . . . . . . . . . . . . . 623
Data Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624
Ways to Partition Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624
Steps for Creating Partitioned Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 625
Collocating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
SQL Server 2008 and Data Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
Guidelines for Partitioning Tables and Indices . . . . . . . . . . . . . . . . . . . . . . . . . 634
Star Schema Query Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 636
Part V Beyond Relational Data
Chapter 27 Overview of XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
World Wide Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
XML-Related Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
XML—Basic Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
XML Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 646
XML Namespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
Document Type Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648
XML Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 650
Chapter 28 SQL Server and XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
Methods of Storing XML Documents in Relational Databases . . . . . . . . . . . . . . . . . . . . . 652
Storing XML Documents in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
Storing XML Documents Using the XML Data Type . . . . . . . . . . . . . . . . . . . . . . . 654
Storing XML Documents Using Decomposition . . . . . . . . . . . . . . . . . . . . . . . . . 660
Retrieving Stored XML Documents and Fragments . . . . . . . . . . . . . . . . . . . . . . . . . . . 661
Presenting Relational Data as XML Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662
RAW Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 663
AUTO Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664
EXPLICIT Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 665
PATH Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 666
Directives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667
SQL Server XQuery Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
SQL Server 2008 and XML Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 671
Chapter 29 Introduction to Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 673
Presenting Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
Geodetic Spatial Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
Flat Spatial Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
Spatial Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
GEOMETRY Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
Differences Between the GEOMETRY and GEOGRAPHY Data Types . . . . . . . . . . . . . . 681
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 681
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
Introduction
There are a couple of reasons why SQL Server, the system that comprises Database Engine, Analysis Services, Reporting Services and Integration Services is the best choice for a broad spectrum of end users and database programmers building business applications:
c 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 system.
c 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 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 2008: A Beginner’s Guide follows three previous editions that covered SQL Server 7, 2000, and 2005. 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: users who want to learn more about the relational database component called Database Engine will find the first three parts of the book the most useful. The fourth part of the book is dedicated to business intelligence users who use either Analysis Services or relational extensions concerning BI. The last part of the book provides insight for users who want to use XML data and/or spatial data.
SQL Server 2008 New Features Described in the Book SQL Server 2008 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.
Chapter 3 The IntelliSense functionality of SQL Server Management Studio’s Query Editor is described in this chapter. IntelliSense provides a list of options that make language references easily accessible. In SQL Server 2008, frequently used Transact-SQL elements are supported.
Chapter 4 This chapter describes several new features, the most important of which is the support for the new temporal data types DATE, TIME, DATETIME2, and DATETIMEOFFSET. Two other new data types, FILESTREAM
and HIERARCHYID, are also explained. The VARDECIMAL storage format is discussed in detail, too. (This storage format was introduced with Service Pack 2 of SQL Server 2005.)
Chapter 5 The creation of user-defined table types is shown in this chapter. Their practical use in relation to tablevalued parameters can be found in Chapter 8.
Chapter 6 In this chapter you can find the syntax and examples for the enhanced form of the GROUP BY clause: GROUP BY().
Chapter 7 This chapter describes the MERGE statement as well as the row-value constructor of the INSERT statement.
The MERGE statement combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. The row-value constructor allows you to simplify the way you write INSERT statements.
Chapter 8 Table-valued parameters, which allow you to simplify the task of sending many parameters to a routine, are described in this chapter.
Chapter 12 This chapter describes Extensible Key Management and Transparent Data Encryption, which enhance the
data encryption capabilities of the previous version. Also, the new tracking mechanism called CDC (change data capture) is introduced and its practical use is shown.
Chapter 13 Enhancements in relation to lock escalation are described in this chapter.
Chapter 16 This chapter describes two tools, of which Declarative Management Framework (DMF) is new. DMF is a framework for managing instances of Database Engine. It allows you to create policies, which are then valid for the whole instance or part of it.
Chapter 17 This chapter describes how you can compress the content of a backup, and introduces several enhancements related to database mirroring.
Chapter 19 The peer-to-peer transactional replication supports the new form of conflict detection, which is explained in this chapter.
Chapter 20 The new hint called FORCESEEK and the new dynamic management view (sys.dm_exec_procedure_stats)
are described here. Also, the new form of user-friendly display for execution plans is shown.
Chapter 21 This chapter discusses, among other things, two new and very important performance tools, Performance
Data Collection and Resource Governor.
Chapter 24 SQL Server 2008 implements the ROLLUP and CUBE functions with standardized syntax, which are described here. (Both the functions existed in the previous versions, but have supported only the proprietary syntax.)
The chapter also discusses a new function called GROUPING SETS.
Chapter 26 The first part of this chapter describes parallelism for data partitioning. The second part of this chapter is
entirely new and describes star schema query optimization.
Chapter 28 At the end of this chapter, the minor enhancements in relation to XML schema and the XML data type are described.
Chapter 29 This is an entirely new chapter describing spatial data and two new data types, GEOMETRY and GEOGRAPHY.
Organization of the Book
The book has 29 chapters and is divided into five parts.
Part I, “Basic Concepts and Installation,” describes the notion of database systems in general and Database Engine (SQL Server’s relational database system) in particular, as well as the installation process. It includes the following chapters:
Chapter 1, “Relational Database Systems—An Introduction,” discusses databases in general and 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 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. The system catalog contains system tables that are used to store the information concerning database objects and their relationships. The main characteristic of system tables of Database Engine is that they cannot be accessed directly. 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 every user (especially 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 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 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 a new feature of SQL Server 2008, tracking data changes.
Chapter 13, “Concurrency Control,” describes concurrency control in depth. The beginning of the chapter discusses the two different concurrency models supported by 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 deadlocks are.
Chapter 14, “Triggers,” describes the implementation of procedural integrity constraints using triggers. Each example in this chapter concerns an integrity 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 Database Engine,” discusses some internal issues concerning Database Engine. It provides a detailed description of the Database Engine disk storage elements, system databases, and utilities.
Chapter 16, “Managing Instances and Maintaining Databases,” describes two important components of Database Engine, Declarative Management Framework and Maintenance Plan Wizard, which are used to manage server instances and databases, respectively.
Chapter 17, “Backup and Recovery,” 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. This chapters also describes how to ensure the availability of database systems and databases.
Chapter 18, “Automating System Administration Tasks,” describes the Database Engine component, 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 19, “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 20, “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 execution plans of the optimizer. The end of the chapter provides optimization hints.
Chapter 21, “Performance Tuning,” discusses performance issues and the tools for tuning 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 the tools for monitoring SQL Server.
Part IV, “SQL Server and Business Intelligence,” discusses business intelligence (BI) and all related topics. These chapters introduce Microsoft Analysis Services and Microsoft Reporting Services. SQL/OLAP and existing optimization techniques in relation to BI are described in detail too. This part includes the following chapters:
Chapter 22, “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 23, “Microsoft Analysis Services,” discusses the architecture of Analysis Services and the main component of Analysis Services, Business Intelligence Development Studio.
Chapter 24, “Business Intelligence and Transact-SQL,” explains how you can use Transact-SQL to solve business intelligence problems. This chapter discusses all SQL extensions, such as CUBE and ROLLUP operators, rank functions, the TOP n clause, and the PIVOT relational operator.
Chapter 25, “Microsoft Reporting Services,” describes the Microsoft enterprise reporting solution. This component is used to design and deploy reports. This chapter discusses the development environment that you use to design and create reports, and shows you different ways to deliver a designed and deployed report.
Chapter 26, “Optimizing Techniques for Business Intelligence,” describes two of several specific optimization techniques that can be used especially in the area of business intelligence: data partitioning and star schema join optimization.
The data partitioning technique called range partitioning is described. (This partitioning type is entirely transparent to the application.) In relation to star schema join optimization, the role of bitmap filters in the optimization of schema
joins is explained.
Part V, “Beyond Relational Data,” is dedicated to two “nonrelational” topics, XML and spatial data, because SQL Server, as a data platform, doesn’t have to handle only relational data. The following chapters are included in this part:
Chapter 27, “Overview of XML,” gives you an overview of XML documents, DTDs, and XML Schema. A running example is used to demonstrate how XML documents can be validated using either a DTD or XML Schema.
Chapter 28, “SQL Server and XML,” discusses SQLXML, Microsoft’s set of data types and functions that supports XML in SQL Server 2008, bridging the gap between XML and relational data. The beginning of the chapter introduces the standardized data type XML and explains how stored XML documents can be retrieved. After that, the presentation of relational data as XML documents is discussed in detail.
Chapter 29, “Introduction to 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.
Changes from the Previous Edition
If you are familiar with the previous edition of this book, Microsoft SQL Server 2005:
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 20 is an entirely new chapter and describes the query optimizer in depth. In the previous edition, this topic was described lightly, together with indices, in Chapter 9.) The following table gives you an outline of significant structural changes in the book (minor changes aren’t listed).
Chapter 2 The entire chapter concerning all facets of SQL Server installation (previously Chapter 17) has been moved to the beginning of the book.
Chapter 6 The description of the SELECT statement is now streamlined and described in one chapter. (The number of examples is reduced, but there are still 77 examples and 30 exercises.)
Chapter 9 The system catalog is described earlier in this edition than in the prior edition (previously Chapter 11). The reason is that the general discussion of dynamic management views (DMVs) is given in Chapter 9, while the description of specific DMVs starts in Chapter 10 and continues throughout the rest of the book.
Chapter 10 This chapter is now exclusively dedicated to indices.
Chapter 13 Chapter 13 doesn’t describe transactions only, as in the prior edition, but handles the entire topic of concurrency control. (For example, the section “Concurrency Models” explains in detail both models supported by Database Engine.)
Chapter 14 Triggers are now described after concurrency control because they use a mechanism, row versioning, that is related to the latter topic and thus described in Chapter 13.
Chapter 16 This chapter is entirely new and describes two components related to management of server instances and maintenance of databases.
Chapter 20 This chapter is entirely new and describes in detail the query optimizer of Database Engine. (This component was described in the previous book in Chapter 9 together with indices.)
Chapter 26 This chapter is partly new, and describes data partitioning and star schema join optimization.
Chapter 29 This is an entirely new chapter, with spatial data as the topic.
Transact-SQL, SQL Server’s relational database language, has several nonstandardized properties that generally are not known to people who are familiar with SQL only:
- Whereas the semicolon (;) is used in SQL to separate two SQL statements in a statement group (and you will generally get an error message if you do not include the semicolon), in Transact-SQL, use of semicolons is optional.
- Transact-SQL uses the GO statement. This nonstandardized statement is generally used to separate statement groups from each other, whereas some Transact-SQL statements (such as CREATE TABLE, CREATE INDEX, and so on) must be the only statement in the group. The extended syntax of this statement, GO n (where n = 1, 2, 3,…), means that the corresponding statement group will be executed n times.
- The USE statement, which is used very often in this book, changes the database context to the specified database. For example, the statement USE sample means that the statements that follow are related to the sample database.
Working with the Sample Databases
In contrast to its predecessor, this edition uses several sample databases:
c This book’s own sample database
c Microsoft’s AdventureWorks database
c Microsoft’s AdventureWorksDW database
An introductory book like this requires a sample database that can be easily understood by each reader. For this reason, I used a very simple concept for my own sample database: it has only four tables with several rows each. On the other hand, its logic is complex enough to demonstrate the hundreds of examples included in the text of the book. The sample database that you will use in this book represents a company with departments and employees. Each employee belongs to exactly one department, which itself has one or more employees. Jobs of employees center around projects: each employee works at the same time for one or more projects, and each project engages one or more employees.
