Course Description
This course is intended for Microsoft SQL Server database developers who are
responsible for implementing a database on SQL Server 2008 R2. In this course,
students learn the skills and best practices on how to use SQL Server 2008 R2
product features.
After completing this course, students will be able to:
- Understand the
product, its components, and basic configuration.
- Work with the data types
supported by SQL Server.
- Design and implement tables and work with schemas.
- Design and implement views and partitioned views.
- Describe the concept
of an index and determine the appropriate data type for indexes and composite
index structures.
- Identify the appropriate table structures and implement
clustered indexes and heaps.
- Describe and capture execution plans.
- Design and implement non-clustered indexes, covering indexes, and included
columns.
- Design and implement stored procedures.
- Implement table types,
table valued parameters, and the MERGE statement.
- Describe transactions,
transaction isolation levels, and application design patterns for
highly-concurrent applications.
- Design and implement T-SQL error handling
and structured exception handling.
- Design and implement scalar and
table-valued functions.
- Design and implement constraints.
- Design and
implement triggers.
- Describe and implement target use cases of SQL CLR
integration.
- Describe and implement XML data and schema in SQL Server.
- Use FOR XML and XPath queries.
- Describe and use spatial data types in
SQL Server.
- Implement and query full-text indexes.
Agenda
Lesson 1: Introduction to SQL Server and its Toolset
- Introduction to SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
- Lab : Introduction to SQL Server and its Toolset
Lesson 2: Working with Data Types
- Using Data Types
- Working with Character Data
- Converting Data Types
- Working with Specialized Data Types
- Lab : Working with Data Types
Lesson 3: Designing and Implementing Tables
- Designing Tables
- Working with Schemas
- Creating and Altering Tables
- Lab : Designing and Implementing Tables
Lesson 4: Designing and Implementing Views
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
- Lab : Designing and Implementing Views
Lesson 5: Planning for SQL Server Indexing
- Core Indexing Concepts
- Data Types and Indexes
- Single Column and Composite Indexes
- Lab : Planning for SQL Server Indexing
Lesson 6: Implementing Table Structures in SQL Server
- SQL Server Table Structures
- Working with Clustered Indexes
- Designing Effective Clustered Indexes
- Lab : Implementing Table Structures in SQL Server
Lesson 7: Reading SQL Server Execution Plans
- Execution Plan Core Concepts
- Common Execution Plan Elements
- Working with Execution Plans
- Lab : Reading SQL Server Execution Plans
Lesson 8: Improving Performance through Nonclustered Indexes
- Designing Effective Nonclustered Indexes
- Implementing Nonclustered Indexes
- Using the Database Engine Tuning Advisor
- Lab : Improving Performance through Nonclustered Indexes
Lesson 9: Designing and Implementing Stored Procedures
- Introduction to Stored Procedures
- Working With Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
- Lab : Designing and Implementing Stored Procedures
Lesson 10: Merging Data and Passing Tables
- Using the MERGE Statement
- Implementing Table Types
- Using Table Types as Parameters
- Lab : Merging Data and Passing Tables
Lesson 11: Creating Highly Concurrent SQL Server Applications
- Introduction to Transactions
- Introduction to Locks
- Management of Locking
- Transaction Isolation Levels
- Lab : Creating Highly Concurrent SQL Server Applications
Lesson 12: Handling Errors in T-SQL Code
- Designing T-SQL Error Handling
- Implementing T-SQL Error Handling
- Implementing Structured Exception Handling
- Lab : Handling Errors in T-SQL Code
Lesson 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
- Lab : Designing and Implementing User-Defined Functions
Lesson 14: Ensuring Data Integrity through Constraints
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
- Lab : Ensuring Data Integrity through Constraints
Lesson 15: Responding to Data Manipulation via Triggers
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
- Lab : Responding to Data Manipulation via Triggers
Lesson 16: Implementing Managed Code in SQL Server
- Introduction to SQL CLR Integration
- Importing and Configuring Assemblies
- Implementing SQL CLR Integration
- Lab : Implementing Managed Code in SQL Server
Lesson 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
- Lab : Storing XML Data in SQL Server
Lesson 18: Querying XML Data in SQL Server
- Using the T-SQL FOR XML Statement
- Getting Started with XQuery
- Shredding XML
- Lab : Querying XML Data in SQL Server
Lesson 19: Working with SQL Server Spatial Data
- Introduction to Spatial Data
- Working with SQL Server Spatial Data Types
- Using Spatial Data in Applications
- Lab : Working with SQL Server Spatial Data
Lesson 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
- Lab : Working with Full-Text Indexes and Queries
Audience
This course is intended for IT Professionals who want to become skilled on SQL
Server 2008 R2 product features and technologies for implementing a database. To
be successful in this course, the student should have knowledge of basic
relational database concepts and writing T-SQL queries.
Comments
Virtual 6232 Implementing a Microsoft SQL Server 2008 R2 Database Online Live training option
Imagine an industry-leading classroom training experience from wherever you have access to the Internet.
The Virtual Online Live events listed here gives you an effective and proven online virtual learning experience with the freedom to attend virtually from anywhere.
There is also an option of experiencing Online Live from one of the many national partner centers where a computer and headset will be provided for you.
This rich and engaging virtual classroom environment lets you conveniently interact with instructors and other students.
You will hear and see your instructor as they teach the course and answer your questions via voice or text.
Choose the online live virtual training option and you can access the recorded version even after the class has finished.
The onDemand labs give you access to the same client and server technologies covered in class so you can test, apply and hone your skills by accessing the labs before and even after the class.
Technical Requirements: a computer or laptop with a modern browser and high speed internet access along with one or two monitors.