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