CHAPTER 1 AN OVERVIEW OF MICROSOFT SQL SERVER 7 1
Microsoft SQL Server Version 7 2
Features In Microsoft SQL Server 7 4
Speed 4
Tuning 5
Platforms 5
Upgrades 5
Data Transformations 6
Improvements From Microsoft SQL Server 6.5 6
New Support For Windows 95 And Windows 98 Servers 6
Dynamic Database Size Management 7
Wizards 8
Locking Enhancements 9
Transact SQL Enhancements 10
New Programming Interfaces 11
COM 12
OLE DB 12
ADO 12
Replication Enhancements 13
SQL Server Agent 13
Full-Text Search 14
Improved Support For Data Warehousing 15
Tools 15
Enterprise Manager 16
Query Analyzer 17
SQL Profiler 18
Index Tuning Wizard 19
Data Transformation Services(DTS) 20
osql 21
SQL Scheduler 21
The Database Diagram Tool 22
Chapter Summary 23
Review Questions 25
Hands-On Projects 27
CHAPTER 2 SQL DATABASE ARCHITECTURE 31
The Relational Data Model 32
The Architecture Of A Relational Database System 32
Further Definition Of The Relational Model 34
Structures Within A Relational Database 34
Data Integrity 37
Data Manipulation 39
SQL Server Database Architecture 41
Logical Database Components 43
Physical Database Components 46
Chapter Summary 51
Review Questions 52
Hands-On Projects 55
CHAPTER 3 DEVELOPING A LOGICAL DATABASE DESIGN 59
The Database Life Cycle 60
Gathering Database Requirements 61
Logical Database Design 62
Entity-Relationship Modeling 63
Basic Objects 63
Characteristics Of Relationships 65
Primary,Alternate,And Foreign Keys 68
Nonkey Attributes 71
An ER Model Of Northwind(Part 1) 72
Data Normalization 75
First Normal Form 75
Second Normal Form 76
Third Normal Form 78
Boyce-Codd Normal Form 78
Completing The Normalization Process 79
An ER Model Of Northwind(Part 2) 81
Business Rules 83
Insert/Update Rules 84
Data Domains 86
Other Business Rules 87
Chapter Summary 88
Review Questions 90
Hands-On Projects 94
CHAPTER 4 DERIVING THE PHYSICAL DESIGN 101
Physical Design In The Database Life Cycle 102
Database Design Considerations 104
Database Diagrams 105
Preliminary Physical Design 107
Table Definitions 108
Column Definitions 109
Column Attributes 109
Physical Design For Northwind(Part 1) 111
Logical Data Integrity 113
Primary Keys And Unique Constraints 114
Foreign Keys 116
Check Constraint 118
User-Defined Integrity 118
Physical Design For Northwind(Part 2) 119
Performance Expectations 121
Indexes For Performance 122
Denormalization 123
Table Definitions 124
Security 126
Physical Design For Northwind(Part 3) 126
Impact Of Database Design 127
Chapter Summary 128
Review Questions 130
Hands-On Projects 134
CHAPTER 5 TABLES 137
Table Types 138
Column Data Types 139
NULL/NOT NULL Columns 141
Identity Columns 142
Inserting Explicit Values Into An Identity Column 143
Checking And Correcting Identity Column Values 143
Row Global Unique Identifier Column 143
Defaults 144
Default Object 144
Default Definition 145
Rules 147
Column-Level And Table-Level Constraints 148
CHECK Constraints 149
PRIMARY KEY Constraint 150
UNIQUE Constraint 151
FOREIGN KEY Constraint 153
Creating Tables 154
Altering Tables 156
Adding A Column 156
Dropping A Column 157
Modifying A Column Definition 158
Adding/Dropping The Row Global Unique Identifier Property(ROWGUIDCOL) 159
Adding A Constraint 159
Dropping A Constraint 160
Enabling And Disabling A FOREIGN KEY And CHECK Constraint 160
Enabling And Disabling A Trigger 161
Dropping Tables 161
System Tables 161
Chapter Summary 162
Review Questions 164
Hands-On Projects 167
CHAPTER 6 INDEXES 171
Index Basics 172
Single-Column Index And Composite(Multicolumn)Index 173
Index Intersection And Covered Indexes 173
Clustered Index 174
Nonclustered Index 175
Unique Index 177
Fill Factors 178
PAD_INDEX 180
Creating An Index 180
CREATE INDEX Command 181
Create Index Wizard 183
DBCC DBREINDEX 185
Dropping An Index 186
Viewing An Index 187
Statistics 189
Updating Statistics 191
Creating Statistics 192
Dropping Statistics 193
DBCC SHOW_STATISTICS 193
Index Tuning 194
Index Tuning Wizard 195
Index Analysis Through The Query Analyzer 201
Impact On Total Database Size 201
Impact On Query Performance 202
Impact On DML Performance 203
Chapter Summary 203
Review Questions 205
Hands-On Projects 209
CHAPTER 7 RETRIEVING AND MODIFYING DATA 211
Retrieving Data With SELECT 212
Simple SELECT Statements 212
The WHERE Clause 215
Eliminating Duplicate Rows With DISTINCT 216
Sorting Query Output With ORDER BY 217
Creating Tables With SELECT...INTO 218
Using SELECT To Perform Joins 221
UNION Queries 228
Subqueries 230
Using INSERT To Add Data 235
Using INSERT To Add Individual Rows 235
Using INSERT...SELECT To Insert Multiple Rows 238
Using INSERT...EXECUTE To Insert Multiple Rows 238
Removing Rows With DELETE 240
Removing All Rows With TRUNCATE TABLE 242
Using UPDATE To Modify Data 244
Using UPDATE With Nested Subqueries 245
Using UPDATE...FROM 246
Query Tools And Interfaces 246
Query Tools 246
Programming Interfaces 250
Chapter Summary 251
Review Questions 252
Hands-On Projects 258
CHAPTER 8 TRANSACT-SQL SCRIPTING AND COMMAND BATCHES 265
Putting The Pieces Together 266
Batches And Scripts 266
Local Variables 269
Declaring Local Variables 269
Using Local Variables 269
Scope Of Local Variable Names 270
Dynamic Batch Execution 270
Transact-SQL Comments 271
Control-Of-Flow Language 273
Statements:The Basic Unit For Control-Of-Flow 274
Statement Blocks:Using BEGIN...END 274
IF...ELSE 275
WHILE/BREAK/CONTINUE 276
GOTO 278
RETURN 279
WAITFOR 280
Cursors 280
The Life Cycle Of A Cursor 282
Types Of Cursors 286
Modifying Data With WHERE CURRENT OF 289
Grandma s Secret:Making Better Batches 291
Chapter Summary 293
Review Questions 295
Hands-On Projects 299
CHAPTER 9 SUMMARIZING AND TRANSFORMING DATA 309
Going Beyond Rows And Columns 310
Manipulating Data With Scalar Functions 310
Configuration Functions 310
Cursor Functions 316
Date And Time Functions 318
Mathematical Functions 320
Metadata Functions 322
Security Functions 326
String Functions 329
System Functions 338
System Statistical Functions 348
Text And Image Functions 349
Summarizing Data With Aggregate Functions 352
Scalar Aggregation 353
Vector Aggregation And GROUP BY 354
Other Data Summarization Methods 357
Viewing Only The TOP Query Results 357
Summarizing Multiple Grouping Levels With CUBE 360
ROLLUP——A Hierarchical Subset Of CUBE 362
Chapter Summary 366
Review Questions 368
Hands-On Projects 372
CHAPTER 10 VIEWS 379
Database Views 380
Use Of A View 381
Focus The Data For Users And As A Security Mechanism 381
Shield The Complexity Of Data Manipulation 382
Organize Data Import And Export 383
Working With Partitioned Data Across Multiple Tables 383
Creating A View 384
Restrictions On Views 385
Create View Command 385
Create View Wizard 388
Altering A View 390
Dropping A View 392
Querying Data Through A View 392
Modifying Data Through A View 392
System Views 394
Nested Views And Performance 397
Chapter Summary 398
Review Questions 399
Hands-On Projects 402
CHAPTER 11 TRANSACTIONS AND LOCKING 405
Defining A Transaction 406
Definition 406
ACID Properties 406
Isolation Level 407
Database Locking 412
Locking Scope 412
Types Of Locking 413
Observing Database Locks 414
Dynamic Locking 416
Overriding Default Locking Behavior 416
Deadlocking 419
Observing Deadlock Behavior 420
Avoiding Deadlocks 421
Programming Transactions 423
Implicit,Explicit,And Autocommit Transactions 423
Naming Transactions 424
Nesting 425
Savepoints Within A Transaction 426
Transaction Behavior Within Triggers 427
Distributed Transactions 427
Cursors And Optimistic Locking 428
Chapter Summary 430
Review Questions 432
Hands-On Projects 434
CHAPTER 12 STORED PROCEDURES 439
Stored Procedures 440
Why Use A Stored Procedure? 440
Types Of Stored Procedures 441
System Stored Procedures 441
Extended Stored Procedures 442
Local(User)Stored Procedures 442
Remote Stored Procedures 443
Temporary Stored Procedures 443
Deferred Name Resolution And Compilation Of Stored Procedures 443
Parsing 443
Resolution 444
Optimization 444
Compilation 445
Creating A Stored Procedure 445
Grouping Stored Procedures 447
CREATE PROCEDURE Statement 447
Create Stored Procedure Wizard 450
Nesting Stored Procedures 451
Stored Procedures Parameters 453
Input Parameters 454
Output Parameters 455
Returning Data From Stored Procedures 457
Altering A Stored Procedure 459
Dropping A Stored Procedure 461
Executing A Stored Procedure 461
Enforcing Business Rules With Stored Procedures 464
The RAISERROR Statement 465
Chapter Summary 467
Review Questions 468
Hands-On Projects 471
CHAPTER 13 TRIGGERS 475
What s A Trigger? 476
Types Of Triggers 476
Special Tables:Deleted Table And Inserted Table 477
Creating A Trigger 478
CREATE TRIGGER Statement 480
Altering A Trigger 482
Dropping A Trigger 484
Checking For Changes To A Column 484
IF UPDATE(<column_name>) 484
IF COLUMNS_UPDATED() 485
Triggers And Transactions 487
Nested Triggers 488
Recursive Triggers 489
TRIGGER_NESTLEVEL()Function 489
Enforcing Business Rules And Data Integrity With Triggers 491
Cascading Updates And Cascading Deletes 493
Coding Triggers 494
Chapter Summary 495
Review Questions 497
Hands-On Projects 501
CHAPTER 14 CREATING THE PHYSICAL DATABASE 505
Sizing SQL Server Database Components 506
Database File 506
Primary Data File 507
Secondary Data File 507
Transaction Log File 508
Database Filegroups 508
Primary Filegroup 509
User-Defined Filegroup 510
Creating A Database 510
CREATE DATABASE Statement 511
Create Database Wizard 514
Modifying A Database 516
Adding Data Files 516
Adding Transaction Log Files 517
Removing A Data Or Transaction Log File 517
Modifying A Data Or Transaction Log File 517
Adding A Filegroup 518
Removing A Filegroup 518
Modifying A Filegroup Property(READONLY,READWRITE,DEFAULT) 519
Dropping A Database 519
Expanding A Database 520
Shrinking A Database 520
Automatically Shrinking A Databse 520
DBCC SHRINKDATABASE Statement 521
DBCC SHRINKFILE Statement 521
Database Options 522
Attaching And Detaching A Database 525
sp_detach_db 525
sp_attach_db 526
Renaming A Database 527
Database Owner 528
System Databases 528
master Database 529
model Database 529
msdb Database 529
tempdb Database 529
Storage Options And RAID Levels 529
Chapter Summary 531
Review Questions 533
Hands-On Projects 536
CHAPTER 15 REMOTE DATA SOURCES 541
Remote Data Sources 542
Remote Server Architecture 542
Configuring A Remote Server 543
Executing Remote Procedures 544
Linked Server Architecture 544
Seamless SELECT 546
FREQUENT OPENQUERY 548
Occasional OPENROWSET 549
Leaving Locality 550
Undeniable Updates 552
Configuring Linked Servers 553
SQL Server Data Sources 554
MS Access Data Sources 557
Oracle Data Sources 558
Other Data Sources 560
Additional Configuration Settings 560
Chapter Summary 563
Review Questions 564
Hands-On Projects 567
CHAPTER 16 DATA TRANSFERS 569
Importing And Exporting 570
Deciding Between Transfer Tools 570
Bulk Copy 572
Specifying Connection Information 573
Working With The Host File 574
Bulk Loads 580
Processing Options 582
Optimizing Performance 582
BULK INSERT 584
Data Transformation Services 585
Import And Export Wizard 585
DTS Designer 587
Chapter Summary 600
Review Questions 601
Hands-On Projects 604
CHAPTER 17 IMPLEMENTING FULL-TEXT SEARCH 607
Full-Text Architecture 608
Full-Text Catalog Administration 612
Configuring A New Catalog 614
Using The Full-Text Catalog Wizard 616
Maintaining The Catalog 618
Querying 623
CONTAINS 624
FREETEXT 627
CONTAINSTABLE 627
FREETEXTTABLE 629
Chapter Summary 629
Review Questions 630
Hands-On Projects 635
CHAPTER 18 MAINTAINING A DATABASE 639
Database Consistency 640
Storage Primer 640
DBCC Commands 642
Efficiency-Contiguous Storage 648
DBCC SHOWCONTIG 649
DBCC Reindex 653
Identity Columns 654
Distribution Statistics 656
Database Maintenance Planning Wizard 664
Chapter Summary 665
Review Questions 666
Hands-On Projects 669
CHAPTER 19 DIAGNOSING PROBLEMS AND IMPROVING QUERY PERFORMANCE 675
Query Plans 676
Simple Text Query Plans 676
Comprehensive Text Query Plans 678
Graphical Query Plans 678
Index Tuning Wizard 682
Workload Files 682
Running The Index Tuning Wizard 684
SQL Profiler 686
Tracing An Application 686
Table Scans 688
Finding Deadlocks 689
Procedure Usage Profile 690
Performance Monitor 691
Access Methods 693
Databases 694
General Statistics 695
Locks 695
SQL Statistics 696
User Settable Statistics 697
Chapter Summary 697
Review Questions 698
Hands-On Projects 701
APPENDIX A DESIGN ON SQL SERVER 7 EXAM OBJECTIVES 707
APPENDIX B MCSE REQUIREMENTS 711
GLOSSARY 713
INDEX 731