MOC 6232B DMOC Implementing A Sql Server 2008 R2 Database TrainerHandbook Vol1

Implementing a Microsoft® SQL Server® 2008 R2 Database......Page 1
Course Objectives......Page 13
Course Outline......Page 14
Course Materials......Page 16
Course Hardware Level......Page 17
Module 1:
Introduction to SQL Server 2008 R2 and its Toolset......Page 19
Module Overview......Page 20
Lesson 1:
Introduction to the SQL Server Platform......Page 21
SQL Server Architecture......Page 22
SQL Server Components......Page 24
SQL Server Instances......Page 26
SQL Server Editions......Page 28
SQL Server Versions......Page 30
Lesson 2:
Working with SQL Server Tools......Page 32
Connecting from Clients and Applications......Page 33
Software Layers for Connections......Page 34
SQL Server Management Studio......Page 36
Demonstration 2A: SQL Server Management Studio......Page 37
Business Intelligence Development Studio......Page 40
Demonstration 2B: Business Intelligence Development Studio......Page 41
Books Online......Page 43
Demonstration 2C: Books Online......Page 44
Lesson 3:
Configuring SQL Server Services......Page 46
SQL Server Configuration Manager......Page 47
SQL Server Services......Page 48
Network Ports and Listeners......Page 49
Creating Server Aliases......Page 50
Other SQL Server Tools......Page 51
Demonstration 3A: SQL Server Profiler......Page 52
Lab 1: Introduction to SQL Server and its Toolset......Page 54
Exercise 1: Verify SQL Server Component Installation......Page 56
Exercise 2: Alter Service Accounts for New Instance......Page 57
Exercise 3: Enable Named Pipes Protocol for Both Instances......Page 58
Exercise 4: Create Aliases for AdventureWorks and Proseware......Page 59
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/ IP Port ( Only if time permits)......Page 60
Module Review and Takeaways......Page 61
Module 2:
Working with Data Types......Page 63
Module Overview......Page 64
Lesson 1:
Using Data Types......Page 65
Introducing Data Types......Page 66
Exact Numeric Data Types......Page 68
Working with IDENTITY......Page 70
Approximate Numeric Data Types......Page 72
Date and Time Data Types......Page 74
Unique Identifiers......Page 76
NULL or NOT NULL Columns......Page 78
Demonstration 1A: Working with Numeric Data Types......Page 80
Lesson 2:
Working with Character Data......Page 81
Understanding Unicode......Page 82
Character Data Types......Page 84
Understanding Collations......Page 86
Demonstration 2A: Working with Character Data......Page 88
Lesson 3:
Converting Data Types......Page 89
Using CAST......Page 90
Using CONVERT......Page 91
Implicit Data Conversion......Page 92
Common Conversion Issues......Page 93
Demonstration 3A: Common Conversion Issues......Page 95
Lesson 4:
Specialized Data Types......Page 96
timestamp and rowversion......Page 97
Alias Data Types......Page 99
Other Data Types......Page 100
Demonstration 4A: rowversion Data Type......Page 101
Lab 2: Working with Data Types......Page 102
Exercise 1: Choosing Appropriate Data Types......Page 106
Exercise 2: Writing Queries With Data Type Conversions......Page 107
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits)......Page 108
Module Review and Takeaways......Page 109
Module 3:
Designing and Implementing Tables......Page 111
Module Overview......Page 112
Lesson 1:
Designing Tables......Page 113
What is a Table?......Page 114
Normalizing Data......Page 116
Common Normalization Forms......Page 117
Demonstration 1A: Normalization......Page 119
Primary Keys......Page 120
Foreign Keys......Page 122
Working with System Tables......Page 124
Lesson 2:
Working with Schemas......Page 125
What is a Schema?......Page 126
Object Name Resolution......Page 128
Creating Schemas......Page 129
Demonstration 2A: Schemas......Page 130
Lesson 3: Creating and Altering Tables......Page 131
Creating Tables......Page 132
Dropping Tables......Page 134
Altering Tables......Page 135
Demonstration 3A: Working with Tables......Page 136
Temporary Tables......Page 137
Demonstration 3B: Temporary Tables......Page 139
Computed Columns......Page 140
Demonstration 3C: Computed Columns......Page 141
Lab 3: Designing and Implementing Tables......Page 142
Exercise 1: Improve the Design of Tables......Page 146
Exercise 2: Create a Schema......Page 147
Challenge Exercise 3: Create the Tables (Only if time permits)......Page 148
Module Review and Takeaways......Page 149
Module 4:
Designing and Implementing Views......Page 151
Module Overview......Page 152
Lesson 1:
Introduction to Views......Page 153
What is a View?......Page 154
Types of Views......Page 156
Advantages of Views......Page 158
Working with System Views......Page 159
Dynamic Management Views......Page 161
Demonstration 1A: System and Dynamic Management Views......Page 162
Lesson 2:
Creating and Managing Views......Page 163
Creating Views......Page 164
Dropping Views......Page 165
Altering Views......Page 166
Ownership Chains and Views......Page 167
Sources of Information About Views......Page 168
Updatable Views......Page 169
Obfuscating View Definitions......Page 170
Demonstration 2A: Implementing Views......Page 171
Lesson 3:
Performance Considerations for Views......Page 172
Views and Dynamic Resolution......Page 173
Nested View Considerations......Page 174
Partitioned Views......Page 175
Demonstration 3A: Views and Performance......Page 176
Lab 4: Designing and Implementing Views......Page 177
Exercise 1: Design, Implement and Test the WebStock Views......Page 180
Exercise 2: Design and Implement the Contacts View......Page 181
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits)......Page 182
Module Review and Takeaways......Page 183
Module 5:
Planning for SQL Server 2008 R2 Indexing......Page 185
Module Overview......Page 186
Lesson 1: Core Indexing Concepts......Page 187
How SQL Server Accesses Data......Page 188
The Need for Indexes......Page 189
Index Structures......Page 190
Selectivity, Density and Index Depth......Page 191
Index Fragmentation......Page 192
Demonstration 1A: Viewing Index Fragmentation......Page 194
Lesson 2:
Data Types and Indexes......Page 195
Numeric Index Data......Page 196
Character Index Data......Page 197
Date-Related Index Data......Page 198
GUID Index Data......Page 199
BIT Index Data......Page 200
Indexing Computed Columns......Page 201
Lesson 3:
Single Column and Composite Indexes......Page 203
Single Column vs. Composite Indexes......Page 204
Ascending vs. Descending Indexes......Page 205
Index Statistics......Page 206
Demonstration 3A: Viewing Index Statistics......Page 207
Lab 5: Planning for SQL Server Indexing......Page 208
Exercise 1: Explore existing index statistics......Page 211
Challenge Exercise 2: Design column orders for indexes (Only if time permits)......Page 213
Module Review and Takeaways......Page 214
Module 6:
Implementing Table Structures in SQL Server 2008 R2......Page 215
Module Overview......Page 216
Lesson 1:
SQL Server Table Structures......Page 217
What is a Heap?......Page 218
Operations on Heaps......Page 219
Forwarding Pointers......Page 220
What is a Clustered Index?......Page 222
Operations on Clustered Indexes......Page 223
Unique vs. Non-Unique Clustered Indexes......Page 225
Demonstration 1A: Rebuilding Heaps......Page 226
Lesson 2:
Working with Clustered Indexes......Page 227
Creating Clustered Indexes......Page 228
Dropping a Clustered Index......Page 230
Altering a Clustered Index......Page 231
Incorporating Free Space in Indexes......Page 232
Demonstration 2A: Clustered Indexes......Page 233
Lesson 3:
Designing Effective Clustered Indexes......Page 234
Characteristics of Good Clustering Keys......Page 235
Appropriate Data Types for Clustering Keys......Page 236
Creating Indexed Views......Page 237
Indexed View Considerations......Page 238
Demonstration 3A: Indexed Views......Page 239
Lab 6: Implementing Table Structures in SQL Server......Page 240
Exercise 1: Creating Tables as Heaps......Page 243
Exercise 2: Creating Tables with Clustered Indexes......Page 244
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps ( Only if time permits)......Page 245
Module Review and Takeaways......Page 247
Module 7:
Reading SQL Server 2008 R2 Execution Plans......Page 249
Module Overview......Page 250
Lesson 1:
Execution Plan Core Concepts......Page 251
Why Execution Plans Matter......Page 252
Query Execution Phases......Page 253
What is an Execution Plan?......Page 255
Actual vs. Estimated Execution Plans......Page 256
What is an Execution Context?......Page 258
Execution Plan Formats......Page 259
Demonstration 1A: Viewing Execution Plans in SSMS......Page 261
Lesson 2:
Common Execution Plan Elements......Page 262
Table and Clustered Index Scans and Seeks......Page 263
Nested Loops and Lookups......Page 264
Merge and Hash Joins......Page 266
Aggregations......Page 268
Filter and Sort......Page 269
Data Modification......Page 270
Demonstration 2A: Common Execution Plan Elements......Page 271
Lesson 3:
Working with Execution Plans......Page 272
Methods for Capturing Plans......Page 273
Demonstration 3A: Capturing Plans in Activity Monitor......Page 274
Re-Executing Queries......Page 275
Execution Plan Related DMVs......Page 277
Demonstration 3B: Viewing Cached Plans......Page 278
Lab 7: Reading SQL Server Execution Plans......Page 279
Exercise 1: Actual vs. Estimated Plans......Page 281
Exercise 2: Identify Common Plan Elements......Page 283
Challenge Exercise 3: Query Cost Comparison (Only if time permits)......Page 285
Module Review and Takeaways......Page 286
Module 8:
Improving Performance through Nonclustered Indexes......Page 287
Module Overview......Page 288
Lesson 1:
Designing Effective Nonclustered Indexes......Page 289
What is a Nonclustered Index?......Page 290
Nonclustered Indexes Over Heaps......Page 291
Nonclustered Indexes Over Clustered Indexes......Page 292
Methods for Obtaining Index Information......Page 293
Demonstration 1A: Obtaining Index Information......Page 295
Lesson 2:
Implementing Nonclustered Indexes......Page 296
Creating Nonclustered Indexes......Page 297
Performance Impact of Lookups in Nested Loops......Page 299
INCLUDE Clause......Page 300
Dropping or Altering Nonclustered Indexes......Page 301
Filtered Indexes......Page 302
Demonstration 2A: Nonclustered Indexes......Page 303
Lesson 3:
Using the Database Engine Tuning Advisor......Page 304
SQL Server Profiler......Page 305
Demonstration 3A: SQL Server Profiler......Page 307
Database Engine Tuning Advisor......Page 308
Demonstration 3B: Database Engine Tuning Advisor......Page 310
Lab 8: Improving Performance through Nonclustered Indexes......Page 311
Exercise 1: Nonclustered index usage review......Page 314
Exercise 2: Improving nonclustered index designs......Page 315
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor......Page 316
Challenge Exercise 4: Nonclustered index design (Only if time permits)......Page 317
Module Review and Takeaways......Page 318
Module 9:
Designing and Implementing Stored Procedures......Page 319
Module Overview......Page 320
Lesson 1:
Introduction to Stored Procedures......Page 321
What is a Stored Procedure?......Page 322
Benefits of Stored Procedures......Page 323
Working with System Stored Procedures......Page 325
Statements not Permitted......Page 327
Demonstration 1A: Working with System Stored Procedures and Extended Stored Procedures......Page 328
Lesson 2:
Working with Stored Procedures......Page 329
Creating a Stored Procedure......Page 330
Executing Stored Procedures......Page 332
Altering a Stored Procedure......Page 334
Dropping a Stored Procedure......Page 335
Stored Procedure Dependencies......Page 336
Guidelines for Creating Stored Procedures......Page 337
Obfuscating Stored Procedure Definitions......Page 339
Demonstration 2A: Stored Procedures......Page 340
Lesson 3:
Implementing Parameterized Stored Procedures......Page 341
Working with Parameterized Stored Procedures......Page 342
Using Input Parameters......Page 344
Using Output Parameters......Page 346
Parameter Sniffing and Performance......Page 348
Demonstration 3A: Stored Procedure Parameters......Page 350
Lesson 4:
Controlling Execution Context......Page 351
Controlling Execution Context......Page 352
The EXECUTE AS Clause......Page 354
Viewing Execution Context......Page 355
Demonstration 4A: Viewing Execution Context......Page 356
Lab 9: Designing and Implementing Stored Procedures......Page 357
Exercise 1: Create stored procedures......Page 360
Exercise 2: Create a parameterized stored procedure......Page 361
Challenge Exercise 3: Alter the execution context of stored procedures ( Only if time permits)......Page 362
Module Review and Takeaways......Page 363
Module 10:
Merging Data and Passing Tables......Page 365
Module Overview......Page 366
Lesson 1:
Using the MERGE Statement......Page 367
MERGE Statement......Page 368
WHEN MATCHED......Page 370
WHEN NOT MATCHED BY TARGET......Page 372
WHEN NOT MATCHED BY SOURCE......Page 373
OUTPUT Clause and $action......Page 374
MERGE Determinism and Performance......Page 376
Demonstration 1A: MERGE Statement......Page 377
Lesson 2:
Implementing Table Types......Page 378
Reducing Round-Trip Overhead......Page 379
Options for Passing Lists as Parameters......Page 380
Demonstration 2A: Passing Delimited Lists......Page 381
Introduction to the TABLE Type......Page 382
Populating TABLE Types with Row Constructors......Page 384
Demonstration 2B: TABLE Types and Row Constructors......Page 385
Lesson 3:
Using TABLE Types As Parameters......Page 386
TABLE Input Parameters for Stored Procedures......Page 387
Using Row Constructors to Populate Parameters......Page 388
Demonstration 3A: Passing Tables to Stored Procedures......Page 389
Lab 10: Passing Tables and Merging Data......Page 390
Exercise 1: Create a Table Type......Page 392
Exercise 2: Use a Table Type Parameter......Page 393
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits)......Page 394
Module Review and Takeaways......Page 395
Module 11:
Creating Highly Concurrent SQL Server 2008 R2 Applications......Page 397
Module Overview......Page 398
Lesson 1:
Introduction to Transactions......Page 399
What are Transactions?......Page 400
Auto Commit Transactions......Page 402
Explicit Transactions......Page 404
Implicit Transactions......Page 406
Transaction Recovery......Page 408
Considerations for using Transactions......Page 410
Demonstration 1A: Transactions......Page 412
Lesson 2:
Introduction to Locks......Page 413
Methods of Concurrency Control......Page 414
What are Locks?......Page 415
Blocking vs. Locking......Page 416
What Concurrency Problems are Prevented by Locking?......Page 417
Lockable Resources......Page 419
Types of Locks......Page 420
Lock Compatibility......Page 422
Lesson 3:
Management of Locking......Page 424
Locking Timeout......Page 425
Lock Escalation......Page 426
What are Deadlocks?......Page 428
Locking-related Table Hints......Page 430
Methods to View Locking Information......Page 431
Demonstration 3A: Viewing Locking Information......Page 433
Lesson 4:
Transaction Isolation Levels......Page 434
SQL Server Transaction Isolation Levels......Page 435
Read Committed Snapshot......Page 437
Isolation-related Table Hints......Page 439
Lab 11: Creating Highly Concurrent SQL Server Applications......Page 440
Exercise 1: Detecting Deadlocks......Page 442
Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits)......Page 443
Module Review and Takeaways......Page 444
Module 12:
Handling Errors in T-SQL Code......Page 445
Module Overview......Page 446
Lesson 1:
Understanding T-SQL Error Handling......Page 447
Where T-SQL Errors Occur......Page 448
Types of Errors......Page 450
What's in an Error?......Page 452
Error Severity......Page 454
Demonstration 1A: Error Types and Severity......Page 456
Lesson 2:
Implementing T-SQL Error Handling......Page 457
Raising Errors......Page 458
Using @@Error......Page 459
Errors and Transactions......Page 461
Transaction Nesting Errors......Page 463
Raising Custom Errors......Page 464
Creating Alerts When Errors Occur......Page 465
Demonstration 2A: T-SQL Error Handling......Page 466
Lesson 3:
Implementing Structured Exception Handling......Page 467
TRY CATCH Block Programming......Page 468
Error Handling Functions......Page 469
Catchable vs. Non-catchable Errors......Page 470
TRY CATCH and Transactions......Page 471
Errors in Managed Code......Page 473
Demonstration 3A: Deadlock Retry......Page 474
Lab 12: Handling Errors in T-SQL Code......Page 475
Exercise 1: Replace @@ERROR based error handling with structured exception handling......Page 477
Challenge Exercise 2: Add deadlock retry logic to the stored procedure ( Only if time permits)......Page 478
Module Review and Takeaways......Page 479