OneSource Professional Training Solutions
OneSource Professional Training Solutions, Inc. presents
 

Designing and Implementing a SQL Server 2008 R2 Database On-Site Training

This on-site training class is also available as Public Schedule Seminar.

Designing and Implementing a SQL Server 2008 R2 Database

Course Description/Agenda


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.

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 Should Attend

IT professionals who administer and maintain SQL Server databases

Course Prerequisites

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

or

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

 Add to favorites Add to favorites
Email Email this page
 

On-Site Training
Information Request Form

Please complete the form for more information and/or a quote for this on-site class.

Name:

Email

Phone:

City and State

Company:

Number of students:
(at least 10 for consideration)

When do you want to hold the
seminar?

How long would you like for the
seminar?

Additional comments to trainer:

We value your privacy!