In this course, you will gain the knowledge and skills to design and implement a Microsoft SQL Server 2008 database. You will learn how to use SQL Server 2008 product features and tools related to developing and implementing a database.

This course incorporates material from the Official Microsoft Learning Product 6232: Implementing a Microsoft SQL Server 2008 R2 Database.

Global Knowledge Exclusive!

Continue developing your skills after class with 24/7 access to our live remote labs for six months, providing you with valuable extra time for hands-on practice.

What You'll Learn

• Create databases and database files
• Create data types and tables
• Use XML-related features in Microsoft SQL Server 2008
• Plan, create, and optimize indexes
• Implement data integrity in Microsoft SQL Server 2008 databases by using constraints
• Implement data integrity in Microsoft SQL Server 2008 databases by using triggers
• Implement views
• Implement stored procedures
• Implement functions
• Implement managed code in the database
• Manage transactions and locks
• Use Service Broker to build a messaging-based solution

Who Needs to Attend

IT professionals who administer and maintain SQL Server databases

Prerequisites

• Working knowledge of relational databases (database design skills)
• Core Windows Server skills
• Basic programming language

or

• Writing Queries Using Microsoft SQL Server 2008 Transact-SQL (M2778)

Follow-On Courses

There are no follow-ons for this course.

Certification Programs and Certificate Tracks

This course is part of the following programs or tracks:

• MCITP: Database Administrator 2008

Course Outline

1. Introduction to SQL Server and Its Toolset

• SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services

2. Working with Data Types

• Using Data Types
• Working with Character Data
• Converting Data Types
• Working with Specialized Data Types

3. Designing and Implementing Tables

• Designing Tables
• Working with Schemas
• Creating and Altering Tables

4. Designing and Implementing Views

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views

5. Planning for SQL Server Indexing

• Core Indexing Concepts
• Data Types and Indexes
• Single Column and Composite Indexes

6. Implementing Table Structures in SQL Server

• SQL Server Table Structures
• Working with Clustered Indexes
• Designing Effective Clustered Indexes

7. Reading SQL Server Execution Plans

• Execution Plan Core Concepts
• Common Execution Plan Elements
• Working with Execution Plans

8. Improving Performance through Nonclustered Indexes

• Designing Effective Nonclustered Indexes
• Implementing Nonclustered Indexes
• Using the Database Engine Tuning Advisor

9. Designing and Implementing Stored Procedures

• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context

10. Merging Data and Passing Tables

• Using the MERGE Statement
• Implementing Table Types
• Using Table Types as Parameters

11. Creating Highly Concurrent SQL Server Applications

• Transactions
• Locks
• Management of Locking
• Transaction Isolation Levels

12. Handling Errors in T-SQL Code

• Designing T-SQL Error Handling
• Implementing T-SQL Error Handling
• Implementing Structured Exception Handling

13. Designing and Implementing User-Defined Functions

• Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Implementation Considerations for Functions
• Alternatives To Functions

14. Ensuring Data Integrity through Constraints

• Enforcing Data Integrity
• Implementing Domain Integrity
• Implementing Entity and Referential Integrity

15. Responding to Data Manipulation via Triggers

• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts

16. Implementing Managed Code in SQL Server

• SQL CLR Integration
• Importing and Configuring Assemblies
• Implementing SQL CLR Integration

17. Storing XML Data in SQL Server

• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type

18. Querying XML Data in SQL Server

• Using the T-SQL FOR XML Statement
• Getting Started with XQuery
• Shredding XML

19. Working with SQL Server Spatial Data

• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications

20. Working with Full-Text Indexes and Queries

• Introduction to Full-Text Indexing
• Implementing Full-Text Indexes in SQL Server
• Working with Full-Text Queries

Labs

Lab 1: Introduction to SQL Server and Its Toolset

• Verifying SQL Server Component Installation
• Altering Service Accounts for New Instance
• Enabling Named Pipes Protocol for Both Instances
• Creating Aliases for AdventureWorks and Proseware
• Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port

Lab 2: Working with Data Types

• Choosing Appropriate Data Types
• Writing Queries with Data Type Conversions
• Designing and Creating Alias Data Types

Lab 3: Designing and Implementing Tables

• Improving the Design of Tables
• Creating a Schema
• Creating the Tables

Lab 4: Designing and Implementing Views

• Designing, Implementing, and Testing the WebStock Views
• Designing and Implementing the Contacts View
• Modifying the AvailableModels View

Lab 5: Planning for SQL Server Indexing

• Exploring Existing Index Statistics
• Designing Column Orders for Indexes

Lab 6: Implementing Table Structures in SQL Server

• Creating Tables as Heaps
• Creating Tables with Clustered Indexes
• Comparing the Performance of Clustered Indexes vs. Heaps

Lab 7: Reading SQL Server Execution Plans

• Execution Plan Core Concepts
• Common Execution Plan Elements
• Working with Execution Plans

Lab 8: Improving Performance through Nonclustered Indexes

• Reviewing Nonclustered Index Usage
• Improving Nonclustered Index Designs
• Using SQL Server Profiler and Database Engine Tuning Advisor
• Nonclustered Index Design

Lab 9: Designing and Implementing Stored Procedures

• Creating Stored Procedures
• Creating a Parameterized Stored Procedure
• Altering the Execution Context of Stored Procedures

Lab 10: Merging Data and Passing Tables

• Creating a Table Type
• Using a Table Type Parameter
• Using a Table Type with MERGE

Lab 11: Creating Highly Concurrent SQL Server Applications

• Detecting Deadlocks
• Investigating Transaction Isolation Levels

Lab 12: Handling Errors in T-SQL Code

• Error Handling with Structured Exception Handling
• Adding Deadlock Retry Logic to the Stored Procedure

Lab 13: Designing and Implementing User-Defined Functions

• Formatting Phone Numbers
• Modifying an Existing Function
• Resolving a Function-Related Performance Issue

Lab 14: Ensuring Data Integrity through Constraints

• Designing Constraint
• Testing the Constraints

Lab 15: Responding to Data Manipulation via Triggers

• Creating the Audit Trigger
• Improving the Audit Trigger

Lab 16: Implementing Managed Code in SQL Server

• Assessing Proposed CLR Code
• Implementing a CLR Assembly
• Implementing a CLR User-Defined Aggregate and User-Defined Type

Lab 17: Storing XML Data in SQL Server

• Appropriate Usage of XML Data Storage in SQL Server
• Investigating the Storage of XML Data in Variables
• Investigating the Use of XML Schema Collections
• Investigating the Creation of Database Columns Based on XML

Lab 18: Querying XML Data in SQL Server

• Learning to Query SQL Server Data as XML
• Writing a Stored Procedure Returning XML
• Writing a Stored Procedure that Updates Using XML

Lab 19: Working with SQL Server Spatial Data

• Familiarity with Geometry Data Type
• Adding Spatial Data to an Existing Table
• Business Application of Spatial Data

Lab 20: Working with Full-Text Indexes and Queries

• Implementing a Full-Text Index
• Implementing a Stoplist
• Creating a Stored Procedure to Implement a Full-Text Search