MOC 6232B DMOC Implementing A Sql Server 2008 R2 Database LabManual

Lab Instructions......Page 1
Module 1:
Lab Instructions: Introduction to SQL Server 2008 R2 and its Toolset......Page 3
Lab 1: Introduction to SQL Server and its Toolset......Page 4
Exercise 1: Verify SQL Server Component Installation......Page 6
Exercise 2: Alter Service Accounts for New Instance......Page 7
Exercise 3: Enable Named Pipes Protocol for Both Instances......Page 8
Exercise 4: Create Aliases for AdventureWorks and Proseware......Page 9
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/ IP Port ( Only if time permits)......Page 10
Module 2:
Lab Instructions: Working with Data Types......Page 11
Lab 2: Working with Data Types......Page 12
Exercise 1: Choosing Appropriate Data Types......Page 16
Exercise 2: Writing Queries With Data Type Conversions......Page 17
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits)......Page 18
Module 3:
Lab Instructions: Designing and Implementing Tables......Page 19
Lab 3: Designing and Implementing Tables......Page 20
Exercise 1: Improve the Design of Tables......Page 24
Exercise 2: Create a Schema......Page 25
Challenge Exercise 3: Create the Tables (Only if time permits)......Page 26
Module 4:
Lab Instructions: Designing and Implementing Views......Page 27
Lab 4: Designing and Implementing Views......Page 28
Exercise 1: Design, Implement and Test the WebStock Views......Page 31
Exercise 2: Design and Implement the Contacts View......Page 32
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits)......Page 33
Module 5:
Lab Instructions: Planning for SQL Server 2008 R2 Indexing......Page 34
Lab 5: Planning for SQL Server Indexing......Page 35
Exercise 1: Explore existing index statistics......Page 38
Challenge Exercise 2: Design column orders for indexes (Only if time permits)......Page 40
Module 6:
Lab Instructions: Implementing Table Structures in SQL Server 2008 R2......Page 41
Lab 6: Implementing Table Structures in SQL Server......Page 42
Exercise 1: Creating Tables as Heaps......Page 45
Exercise 2: Creating Tables with Clustered Indexes......Page 46
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps ( Only if time permits)......Page 47
Module 7:
Lab Instructions: Reading SQL Server 2008 R2 Execution Plans......Page 49
Lab 7: Reading SQL Server Execution Plans......Page 50
Exercise 1: Actual vs. Estimated Plans......Page 52
Exercise 2: Identify Common Plan Elements......Page 54
Challenge Exercise 3: Query Cost Comparison (Only if time permits)......Page 56
Module 8:
Lab Instructions: Improving Performance through Nonclustered Indexes......Page 57
Lab 8: Improving Performance through Nonclustered Indexes......Page 58
Exercise 1: Nonclustered index usage review......Page 61
Exercise 2: Improving nonclustered index designs......Page 62
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor......Page 63
Challenge Exercise 4: Nonclustered index design (Only if time permits)......Page 64
Module 9:
Lab Instructions: Designing and Implementing Stored Procedures......Page 65
Lab 9: Designing and Implementing Stored Procedures......Page 66
Exercise 1: Create stored procedures......Page 69
Exercise 2: Create a parameterized stored procedure......Page 70
Challenge Exercise 3: Alter the execution context of stored procedures ( Only if time permits)......Page 71
Module 10:
Lab Instructions: Merging Data and Passing Tables......Page 72
Lab 10: Passing Tables and Merging Data......Page 73
Exercise 1: Create a Table Type......Page 75
Exercise 2: Use a Table Type Parameter......Page 76
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits)......Page 77
Module 11:
Lab Instructions: Creating Highly Concurrent SQL Server 2008 R2 Applications......Page 78
Lab 11: Creating Highly Concurrent SQL Server Applications......Page 79
Exercise 1: Detecting Deadlocks......Page 81
Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits)......Page 82
Module 12:
Lab Instructions: Handling Errors in T-SQL Code......Page 83
Lab 12: Handling Errors in T-SQL Code......Page 84
Exercise 1: Replace @@ERROR based error handling with structured exception handling......Page 86
Challenge Exercise 2: Add deadlock retry logic to the stored procedure ( Only if time permits)......Page 87
Module 13:
Lab Instructions: Designing and Implementing User-Defined Functions......Page 88
Lab 13: Designing and Implementing User-Defined Functions......Page 89
Exercise 1: Formatting Phone Numbers......Page 92
Exercise 2: Modifying an Existing Function......Page 93
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits)......Page 94
Module 14:
Lab Instructions: Ensuring Data Integrity through Constraints......Page 95
Lab 14: Ensuring Data Integrity through Constraints......Page 96
Exercise 1: Constraint Design......Page 98
Challenge Exercise 2: Test the constraints (Only if time permits)......Page 99
Module 15:
Lab Instructions: Responding to Data Manipulation via Triggers......Page 100
Lab 15: Responding to Data Manipulation via Triggers......Page 101
Exercise 1: Creating and Testing the Audit Trigger......Page 103
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits)......Page 104
Module 16:
Lab Instructions: Implementing Managed Code in SQL Server 2008 R2......Page 105
Lab 16: Implementing Managed Code in SQL Server......Page 106
Exercise 1: Assess Proposed CLR Code......Page 108
Exercise 2: Implement a CLR Assembly......Page 109
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR User- defined Data Type ( Only if time permits)......Page 110
Module 17:
Lab Instructions: Storing XML Data in SQL Server 2008 R2......Page 112
Lab 17: Storing XML Data in SQL Server......Page 113
Exercise 1: Appropriate Usage of XML Data Storage in SQL Server......Page 115
Exercise 2: Investigate the Storage of XML Data in Variables......Page 116
Exercise 3: Investigate the use of XML Schema Collections......Page 117
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML ( Only if time permits)......Page 118
Module 18:
Lab Instructions: Querying XML Data in SQL Server......Page 119
Lab 18: Querying XML Data in SQL Server......Page 120
Exercise 1: Learn to query SQL Server data as XML......Page 123
Exercise 2: Write a stored procedure returning XML......Page 124
Challenge Exercise 3: Write a stored procedure that updates using XML ( Only if time permits)......Page 125
Module 19: Lab Instructions: Working with SQL Server® 2008 R2 Spatial Data......Page 126
Lab 19: Working with SQL Server Spatial Data......Page 127
Exercise 1: Familiarity With Geometry Data Type......Page 129
Exercise 2: Adding Spatial Data to an Existing Table......Page 130
Challenge Exercise 3: Business Application of Spatial Data (Only if time permits)......Page 131
Module 20:
Lab Instructions: Working with Full-Text Indexes and Queries......Page 132
Lab 20: Working with Full-Text Indexes and Queries......Page 133
Exercise 1: Implement a full-text index......Page 135
Exercise 2: Implement a stoplist......Page 136
Challenge Exercise 3: Create a stored procedure to implement a full-text search ( Only if time permits)......Page 137
Module 1:
Lab Answer Key: Introduction to SQL Server 2008 R2 and its Toolset......Page 138
Exercise 1: Verify SQL Server Component Installation......Page 139
Exercise 3: Enable Named Pipes Protocol for Both Instances......Page 141
Exercise 4: Create Aliases for AdventureWorks and Proseware......Page 142
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/ IP Port ( Only if time permits)......Page 143
Module 2:
Lab Answer Key: Working with Data Types......Page 145
Exercise 1: Choosing Appropriate Data Types......Page 146
Exercise 2: Writing Queries With Data Type Conversions......Page 148
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits)......Page 149
Module 3:
Lab Answer Key: Designing and Implementing Tables......Page 151
Exercise 1: Improve the Design of Tables......Page 152
Challenge Exercise 3: Create the Tables (Only if time permits)......Page 155
Module 4:
Lab Answer Key: Designing and Implementing Views......Page 157
Exercise 1: Design, Implement and Test the WebStock Views......Page 158
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits)......Page 160
Module 5:
Lab Answer Key: Planning for SQL Server 2008 R2 Indexing......Page 162
Exercise 1: Explore existing index statistics......Page 163
Challenge Exercise 2: Design column orders for indexes (Only if time permits)......Page 166
Module 6:
Lab Answer Key: Implementing Table Structures in SQL Server 2008 R2......Page 168
Exercise 1: Creating Tables as Heaps......Page 169
Exercise 2: Creating Tables with Clustered Indexes......Page 170
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps ( Only if time permits)......Page 171
Module 7:
Lab Answer Key: Reading SQL Server 2008 R2 Execution Plans......Page 173
Exercise 1: Actual vs. Estimated Plans......Page 174
Exercise 2: Identify Common Plan Elements......Page 177
Challenge Exercise 3: Query Cost Comparison (Only if time permits)......Page 180
Module 8:
Lab Answer Key: Improving Performance through Nonclustered Indexes......Page 181
Exercise 1: Nonclustered index usage review......Page 182
Exercise 2: Improving nonclustered index designs......Page 183
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor......Page 184
Challenge Exercise 4: Nonclustered index design (Only if time permits)......Page 186
Module 9:
Lab Answer Key: Designing and Implementing Stored Procedures......Page 188
Exercise 1: Create stored procedures......Page 189
Challenge Exercise 3: Alter the execution context of stored procedures (Only if time permits)......Page 191
Module 10:
Lab Answer Key: Merging Data and Passing Tables......Page 194
Exercise 1: Create a Table Type......Page 195
Exercise 2: Use a Table Type Parameter......Page 196
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits)......Page 197
Module 11:
Lab Answer Key: Creating Highly Concurrent SQL Server 2008 R2 Applications......Page 199
Lab Setup......Page 200
Exercise 1: Detecting Deadlocks......Page 201
Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits)......Page 202
Module 12:
Lab Answer Key: Handling Errors in T-SQL Code......Page 203
Lab Setup......Page 204
Exercise 1: Replace @@ERROR based error handling with structured exception handling......Page 205
Challenge Exercise 2: Add deadlock retry logic to the stored procedure (Only if time permits)......Page 206
Module 13:
Lab Answer Key: Designing and Implementing User-Defined Functions......Page 208
Lab Setup......Page 209
Exercise 1: Formatting Phone Numbers......Page 210
Exercise 2: Modifying an Existing Function......Page 211
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits)......Page 212
Module 14:
Lab Answer Key: Ensuring Data Integrity through Constraints......Page 215
Exercise 1: Constraint Design......Page 216
Challenge Exercise 2: Test the constraints (Only if time permits)......Page 218
Module 15:
Lab Answer Key: Responding to Data Manipulation via Triggers......Page 220
Lab: Responding to Data Manipulation via Triggers......Page 221
Exercise 1: Creating and Testing the Audit Trigger......Page 222
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits)......Page 223
Module 16:
Lab Answer Key: Implementing Managed Code in SQL Server 2008 R2......Page 225
Exercise 1: Assess Proposed CLR Code......Page 226
Exercise 2: Implement a CLR Assembly......Page 227
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR User- defined Data Type ( Only if time permits)......Page 229
Module 17:
Lab Answer Key: Storing XML Data in SQL Server 2008 R2......Page 231
Lab Setup......Page 232
Exercise 2: Investigate the Storage of XML Data in Variables......Page 233
Exercise 3: Investigate the use of XML Schema Collections......Page 235
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML ( Only if time permits)......Page 237
Module 18:
Lab Answer Key: Querying XML Data in SQL Server......Page 240
Exercise 1: Learn to query SQL Server data as XML......Page 241
Exercise 2: Write a stored procedure returning XML......Page 243
Challenge Exercise 3: Write a stored procedure that updates using XML (Only if time permits)......Page 244
Module 19:
Lab Answer Key: Working with SQL Server® 2008 R2 Spatial Data......Page 246
Exercise 1: Familiarity With Geometry Data Type......Page 247
Exercise 2: Adding Spatial Data to an Existing Table......Page 255
Challenge Exercise 3: Business Application of Spatial Data (Only if time permits)......Page 256
Module 20:
Lab Answer Key: Working with Full-Text Indexes and Queries......Page 258
Exercise 1: Implement a full-text index......Page 259
Exercise 2: Implement a stoplist......Page 261
Challenge Exercise 3: Create a stored procedure to implement a full-text search ( Only if time permits)......Page 262