OneSource Professional Training Solutions, Inc.
presents
Designing and Implementing a SQL Server 2008 R2 Database On-Site Training
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
Email this page
|