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

Implementing a Microsoft® SQL Server® 2008 R2 Database......Page 1
Module 13:
Designing and Implementing User-Defined Functions......Page 13
Module Overview......Page 14
Lesson 1:
Overview of Functions......Page 15
Types of Functions......Page 16
System Functions......Page 18
Lesson 2:
Designing and Implementing Scalar Functions......Page 19
What Is a Scalar Function?......Page 20
Creating Scalar Functions......Page 22
Deterministic and Non-deterministic Functions......Page 23
Demonstration 2A: Scalar Functions......Page 25
Lesson 3:
Designing and Implementing Table-Valued Functions......Page 26
What are Table-valued Functions?......Page 27
Inline Table-Valued Functions......Page 28
Multi-statement Table-valued Functions......Page 29
Demonstration 3A: Implementing Table-Valued Functions......Page 30
Lesson 4:
Implementation Considerations for Functions......Page 31
Performance Impacts of Scalar Functions......Page 32
Performance Impacts of Multi-statement Table-valued Functions......Page 33
Controlling Execution Context......Page 34
The EXECUTE AS Clause......Page 35
Guidelines for Creating Functions......Page 36
Demonstration 4A: Execution Context......Page 37
Lesson 5:
Alternatives to Functions......Page 38
Comparing Table-valued Functions and Stored Procedures......Page 39
Comparing Table-valued Functions and Views......Page 40
Lab 13: Designing and Implementing User-Defined Functions......Page 41
Exercise 1: Formatting Phone Numbers......Page 44
Exercise 2: Modifying an Existing Function......Page 45
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits)......Page 46
Module Review and Takeaways......Page 47
Module 14:
Ensuring Data Integrity through Constraints......Page 49
Module Overview......Page 50
Lesson 1:
Enforcing Data Integrity......Page 51
Data Integrity Across Application Layers......Page 52
Types of Data Integrity......Page 54
Options for Enforcing Data Integrity......Page 56
Lesson 2:
Implementing Domain Integrity......Page 58
Data Types......Page 59
Column Nullability......Page 61
DEFAULT Constraints......Page 62
CHECK Constraints......Page 64
Demonstration 2A: Data and Domain Integrity......Page 65
Lesson 3:
Implementing Entity and Referential Integrity......Page 66
PRIMARY KEY Constraints......Page 67
UNIQUE Constraints......Page 69
FOREIGN KEY Constraints......Page 70
Cascading Referential Integrity......Page 72
Considerations for Constraint Checking......Page 74
Demonstration 3A: Entity and Referential Integrity......Page 75
Lab 14: Ensuring Data Integrity through Constraints......Page 76
Exercise 1: Constraint Design......Page 78
Challenge Exercise 2: Test the constraints (Only if time permits)......Page 79
Module Review and Takeaways......Page 80
Module 15: Responding to Data Manipulation via Triggers......Page 81
Module Overview......Page 82
Lesson 1:
Designing DML Triggers......Page 83
What are DML Triggers?......Page 84
AFTER Triggers vs. INSTEAD OF Triggers......Page 86
Inserted and Deleted Virtual Tables......Page 88
SET NOCOUNT ON......Page 90
Trigger Performance Considerations......Page 92
Lesson 2:
Implementing DML Triggers......Page 93
AFTER INSERT Triggers......Page 94
Demonstration 2A: AFTER INSERT Triggers......Page 95
AFTER DELETE Triggers......Page 96
Demonstration 2B: AFTER DELETE Triggers......Page 97
AFTER UPDATE Triggers......Page 98
Demonstration 2C: AFTER UPDATE Triggers......Page 99
Lesson 3:
Advanced Trigger Concepts......Page 100
INSTEAD OF Triggers......Page 101
Demonstration 3A: INSTEAD OF Triggers......Page 102
How Nested Triggers Work......Page 103
Considerations for Recursive Triggers......Page 104
UPDATE Function......Page 105
Trigger Firing Order......Page 106
Alternatives to Using Triggers......Page 107
Demonstration 3B: Replacing Triggers with Computed Columns......Page 109
Lab 15: Responding to Data Manipulation via Triggers......Page 110
Exercise 1: Creating and Testing the Audit Trigger......Page 112
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits)......Page 113
Module Review and Takeaways......Page 114
Module 16:
Implementing Managed Code in SQL Server 2008 R2......Page 115
Module Overview......Page 116
Lesson 1:
Introduction to SQL CLR Integration......Page 117
Options for Extending SQL Server......Page 118
Introduction to the .NET Framework......Page 120
.NET Common Language Runtime......Page 121
Why Use Managed Code in SQL Server?......Page 123
T-SQL vs. Managed Code......Page 125
Appropriate Database Object use in Managed Code......Page 127
Demonstration 1A: Appropriate Use Cases for Managed Code and T-SQL......Page 129
Lesson 2:
Importing and Configuring Assemblies......Page 130
What is an Assembly?......Page 131
Assembly Permission Sets......Page 132
Importing an Assembly......Page 134
Demonstration 2A: Importing and Configuring an Assembly......Page 136
Lesson 3:
Implementing SQL CLR Integration......Page 137
Attribute Usage......Page 138
Scalar User-defined Functions......Page 140
Table-valued User-defined Functions......Page 142
Demonstration 3A: User-defined Functions......Page 144
Stored Procedures – T-SQL Replacement......Page 145
Stored Procedures – External Access......Page 147
Triggers......Page 148
Demonstration 3B: Stored Procedures and Triggers......Page 149
User-defined Aggregates......Page 150
User-defined Data Types......Page 152
Considerations for User-defined Data Types......Page 154
Demonstration 3C: Aggregates and User- defined Data Types......Page 156
Lab 16: Implementing Managed Code in SQL Server......Page 157
Exercise 1: Assess Proposed CLR Code......Page 159
Exercise 2: Implement a CLR Assembly......Page 160
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR User- defined Data Type ( Only if time permits)......Page 161
Module Review and Takeaways......Page 163
Module 17:
Storing XML Data in SQL Server 2008 R2......Page 165
Module Overview......Page 166
Lesson 1:
Introduction to XML and XML Schemas......Page 167
Discussion: XML Usage in SQL Server......Page 168
Core XML Concepts......Page 169
Fragments vs. Documents......Page 171
XML Namespaces......Page 173
XML Schemas......Page 175
Appropriate Usage of XML Data Storage in SQL Server......Page 176
Demonstration 1A: Introduction to XML and XML Schemas......Page 178
Lesson 2:
Storing XML Data and Schemas in SQL Server......Page 179
XML Data Type......Page 180
XML Schema Collections......Page 182
Untyped vs. Typed XML......Page 185
CONTENT vs. DOCUMENT......Page 187
Demonstration 2A: Typed vs. Untyped XML......Page 189
Lesson 3:
Implementing XML Indexes......Page 190
What are XML Indexes?......Page 191
Types of XML Indexes......Page 192
Demonstration 3A: Implementing XML Indexes......Page 194
Lab 17: Storing XML Data in SQL Server......Page 195
Exercise 1: Appropriate Usage of XML Data Storage in SQL Server......Page 197
Exercise 2: Investigate the Storage of XML Data in Variables......Page 198
Exercise 3: Investigate the use of XML Schema Collections......Page 199
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML ( Only if time permits)......Page 200
Module Review and Takeaways......Page 201
Module 18:
Querying XML Data in SQL Server......Page 203
Module Overview......Page 204
Lesson 1:
Using the T-SQL FOR XML Statement......Page 205
Introducing the FOR XML clause......Page 206
Using RAW Mode Queries......Page 207
Using Auto Mode Queries......Page 209
Using Explicit Mode Queries......Page 212
Using Path Mode Queries......Page 213
Retrieving Nested XML......Page 214
Demonstration 1A: FOR XML Queries......Page 216
Lesson 2:
Getting Started with XQuery......Page 217
What is XQuery?......Page 218
query() Method......Page 220
value() Method......Page 222
exist() Method......Page 223
modify() Method......Page 224
Demonstration 2A: XQuery Methods in a DDL Trigger......Page 226
Lesson 3:
Shredding XML......Page 227
Overview of Shredding XML data......Page 228
Stored Procedures for Managing In-Memory Node Trees......Page 229
OPENXML......Page 231
Working with XML Namespaces......Page 232
nodes() Method......Page 233
Demonstration 3A: Shredding XML......Page 235
Lab 18: Querying XML Data in SQL Server......Page 236
Exercise 1: Learn to query SQL Server data as XML......Page 239
Exercise 2: Write a stored procedure returning XML......Page 240
Challenge Exercise 3: Write a stored procedure that updates using XML ( Only if time permits)......Page 241
Module Review and Takeaways......Page 242
Module 19:
Working with SQL Server® 2008 R2 Spatial Data......Page 243
Module Overview......Page 244
Lesson 1:
Introduction to Spatial Data......Page 245
Target Applications......Page 246
Types of Spatial Data......Page 248
Planar vs. Geodetic......Page 250
OGC Object Hierarchy......Page 251
Spatial Reference Identifiers......Page 252
Demonstration 1A: Spatial Reference Systems......Page 254
Lesson 2:
Working with SQL Server Spatial Data Types......Page 255
SQL Server Spatial Data......Page 256
System vs. User SQL CLR Types......Page 258
geometry Data Type......Page 260
geography Data Type......Page 262
Spatial Data Formats......Page 264
OGC Methods and Properties......Page 266
Microsoft Extensions......Page 267
Demonstration 2A: Spatial Data Types......Page 268
Lesson 3:
Using Spatial Data in Applications......Page 269
Performance Issues in Spatial Queries......Page 270
Tessellation Process......Page 271
Spatial Indexes......Page 272
Implementing Spatial Indexes......Page 274
geometry Methods Supported by Spatial Indexes......Page 276
geography Methods Supported by Spatial Indexes......Page 277
Extending SQL Server Spatial......Page 278
Demonstration 3A: Spatial Data in Applications......Page 279
Lab 19: Working with SQL Server Spatial Data......Page 280
Exercise 1: Familiarity With Geometry Data Type......Page 282
Exercise 2: Adding Spatial Data to an Existing Table......Page 283
Challenge Exercise 3: Business Application of Spatial Data (Only if time permits)......Page 284
Module Review and Takeaways......Page 285
Module 20:
Working with Full-Text Indexes and Queries......Page 287
Module Overview......Page 288
Lesson 1:
Introduction to Full-Text Indexing......Page 289
Discussion: The Need for More Flexible User Interaction......Page 290
Why LIKE Isn’t Enough......Page 291
Fuzziness in Queries......Page 293
Demonstration 1A: Using Full-Text Queries......Page 295
Lesson 2:
Implementing Full-Text Indexes in SQL Server......Page 296
Discussion: Search-related Options......Page 297
Full-Text Search in SQL Server......Page 298
Core Components of Full-Text Search......Page 300
Language Support and Supported Word Breakers......Page 303
Implementing Full-Text Indexes......Page 304
Demonstration 2A: Implementing Full-Text Indexes......Page 306
Lesson 3:
Working with Full-Text Queries......Page 307
CONTAINS Queries......Page 308
FREETEXT Queries......Page 310
Table Functions and Ranking Results......Page 311
Thesaurus......Page 313
Stopwords and Stoplists......Page 315
SQL Server Management of Full-Text......Page 316
Demonstration 3A: Working with Full-Text Queries......Page 318
Lab 20: Working with Full-Text Indexes and Queries......Page 319
Exercise 1: Implement a full-text index......Page 321
Exercise 2: Implement a stoplist......Page 322
Challenge Exercise 3: Create a stored procedure to implement a full-text search ( Only if time permits)......Page 323
Module Review and Takeaways......Page 324
Course Review and Evaluation......Page 325
Exercise 1: Verify SQL Server Component Installation......Page 327
Exercise 3: Enable Named Pipes Protocol for Both Instances......Page 329
Exercise 4: Create Aliases for AdventureWorks and Proseware......Page 330
Challenge Exercise 5: Ensure SQL Browser is Disabled and Configure a Fixed TCP/ IP Port ( Only if time permits)......Page 331
Exercise 1: Choosing Appropriate Data Types......Page 333
Exercise 2: Writing Queries With Data Type Conversions......Page 335
Challenge Exercise 3: Designing and Creating Alias Data Types (Only if time permits)......Page 336
Exercise 1: Improve the Design of Tables......Page 339
Challenge Exercise 3: Create the Tables (Only if time permits)......Page 342
Exercise 1: Design, Implement and Test the WebStock Views......Page 345
Challenge Exercise 3: Modify the AvailableModels View (Only if time permits)......Page 347
Exercise 1: Explore existing index statistics......Page 349
Challenge Exercise 2: Design column orders for indexes (Only If time permits)......Page 352
Exercise 1: Creating Tables as Heaps......Page 355
Exercise 2: Creating Tables with Clustered Indexes......Page 356
Challenge Exercise 3: Comparing the Performance of Clustered Indexes vs. Heaps ( Only if time permits)......Page 357
Exercise 1: Actual vs. Estimated Plans......Page 359
Exercise 2: Identify Common Plan Elements......Page 362
Challenge Exercise 3: Query Cost Comparison (Only if time permits)......Page 365
Exercise 1: Nonclustered index usage review......Page 367
Exercise 2: Improving nonclustered index designs......Page 368
Exercise 3: SQL Server Profiler and Database Engine Tuning Advisor......Page 369
Challenge Exercise 4: Nonclustered index design (Only if time permits)......Page 371
Lab: Designing and Implementing Stored Procedures......Page 373
Exercise 1: Create stored procedures......Page 374
Exercise 2: Create a parameterized stored procedure......Page 375
Challenge Exercise 3: Alter the execution context of stored procedures (Only if time permits)......Page 376
Exercise 1: Create a Table Type......Page 379
Exercise 2: Use a Table Type Parameter......Page 380
Challenge Exercise 3: Use a Table Type with MERGE (Only if time permits)......Page 381
Lab: Creating Highly Concurrent SQL Server Applications......Page 383
Exercise 1: Detecting Deadlocks......Page 384
Challenge Exercise 2: Investigating Transaction Isolation Levels (Only if time permits)......Page 385
Lab: Handling Errors in T-SQL Code......Page 387
Exercise 1: Replace @@ERROR based error handling with structured exception handling......Page 388
Challenge Exercise 2: Add deadlock retry logic to the stored procedure (Only if time permits)......Page 389
Lab: Designing and Implementing User-Defined Functions......Page 391
Exercise 1: Formatting Phone Numbers......Page 392
Exercise 2: Modifying an Existing Function......Page 393
Challenge Exercise 3: Resolve a Function-related Performance Issue (Only if time permits)......Page 394
Lab: Ensuring Data Integrity through Constraints......Page 397
Exercise 1: Constraint Design......Page 398
Challenge Exercise 2: Test the constraints (Only if time permits)......Page 399
Lab: Responding to Data Manipulation via Triggers......Page 401
Exercise 1: Creating and Testing the Audit Trigger......Page 402
Challenge Exercise 2: Improve the Audit Trigger (Only if time permits)......Page 403
Exercise 1: Assess Proposed CLR Code......Page 405
Exercise 2: Implement a CLR Assembly......Page 406
Challenge Exercise 3: Implement a CLR User-defined Aggregate and CLR User-defined Data Type (Only if time permits)......Page 408
Lab: Storing XML Data in SQL Server......Page 411
Exercise 2: Investigate the Storage of XML Data in Variables......Page 412
Challenge Exercise 4: Investigate the Creation of Database Columns Based on XML ( Only if time permits)......Page 416
Exercise 1: Learn to query SQL Server data as XML......Page 419
Exercise 2: Write a stored procedure returning XML......Page 421
Challenge Exercise 3: Write a stored procedure that updates using XML (Only if Time permits)......Page 422
Exercise 1: Familiarity With Geometry Data Type......Page 425
Exercise 2: Adding Spatial Data to an Existing Table......Page 433
Challenge Exercise 3: Business Application of Spatial Data (Only if Time permits )......Page 434
Lab: Working with Full -Text Indexes and Queries......Page 437
Exercise 1: Implement a full-text index......Page 438
Exercise 2: Implement a stoplist......Page 439
Challenge Exercise 3: Create a stored procedure to implement a full-text search ( Only if time permits)......Page 440