How To Take This Class

Course Overview

In this Microsoft SQL Server 2008 Training course, students will get an overview of SQL Server 2008 including its editions, components, and tools. Then students will learn the steps to install, and/or upgrade to SQL Server 2008 and configure the server. Students will also see how the principles of database design are used in creating databases. The course continues with SQL Server Management Studio and how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. Students will learn how to make databases more scalable through partitioning and how to use .NET languages like Visual C# and Visual Basic to build database objects. SQL Server 2008 includes a rich set of tools that go beyond the basics of querying and manipulating data. Students will learn how to take advantage of the user-friendly management console that integrates both authoring and administrative tasks. Students will also learn how to take advantage of SQL Server's tools for analyzing and tuning databases. In addition, students will learn about integration servers, implementing security, and Microsoft's new Business Intelligence (BI) suite.

Course Prerequisites

To get the most out of the Microsoft SQL Server 2008 course, students should have a solid understanding of relational databases and the concepts of SQL Server. No particular programming experience is required, but the course is taught from a developer's perspective.

Course Audience

This course is designed for developers who build applications for and around SQL Server 2008.

What You'll Learn

Upon successful completion of this course, students will be able to:
  • Install or upgrade from an earlier version of SQL Server.
  • Configure SQL 2008 Server to meet your needs.
  • Apply relational database design principles.
  • Create a SQL Server database and tables.
  • Understand T-SQL, the SELECT statement and the WHERE Clause.
  • Use SQL Server Management Studio (SSMS).
  • Implement security with authentication, authorization and encryption.
  • Understand the differences between views, stored procedures, triggers, and user-defined functions.
  • Understand the various types of user-defined functions and how they support reusable database code.
  • Understand why using .NET code, using either C# or Visual Basic, in the database overcomes many Transact-SQL limitations, and how SQL Server protects itself from misbehaving code.
  • Understand the complex data types in SQL Server, such as for spatial and hierarchical data, and how they support complex data operations.
  • Partition data to store data in ways that improves the efficiency of queries.
  • Understand Business Intelligence to add reporting and analysis capabilities to your databases and applications.

Course Duration

5 Days

Course Outline

  1. A Tour of SQL Server 2008
    • SQL Server 2008 Editions, Components, and Tools
    • Using SQL Server Management Studio (SSMS)
    • Working with Tables, Queries, and Views
    • Business Intelligence Services
  2. Installing SQL Server 2008
    • Preparing for Installation
    • Upgrading an Earlier Version
    • Installation Steps
    • Configuring the Server
  3. Designing and Creating a Database
    • Relational Database Design Principles
    • Implementing the Design
    • Create a SQL Server Database
    • Create SQL Server Tables
    • Creating Relationships Using a Database Diagram
  4. Data Selection
    • Understanding Transact-SQL
    • The SELECT Statement
    • The WHERE Clause
    • Using ORDER BY to Sort Data
    • The GROUP BY Clause
    • Joining Tables
  5. Modifying Data
    • Modifying DAta with Transact-SQL
    • Inserting Data
    • Updating Data
    • Understanding Transaction Isolation
  6. Working with SQL Server Management Studio
    • Getting Started with Management Studio
    • Exploring the Object Explorer
    • Working with the Query Editor
    • Using SQL Server Books Online
  7. Transact-SQL Programming
    • Overview of Transact-SQL
    • Using Built-In Functions
    • Using Control of Flow Constructs
  8. Understanding and Implementing Security
    • Security Overview
    • Authentication
    • Authorization
    • Data Encryption
    • Security Epilog
  9. Creating Views
    • What Is a View?
    • Creating Views
    • Updating Data Using a View
    • Using Computed Columns
    • Indexed Views
    • Partitioned Views
  10. Creating Stored Procedures and Triggers
    • Creating Stored Procedures
    • Creating Triggers
  11. Creating User-Defined Functions
    • User-Defined Function Overview
    • Scalar Functions
    • Inline Table-Valued Functions
    • Multi-Statement Table-Valued Functions
    • Using Functions, Views, and Stored Procedures
  12. Transactions and Error Handling
    • Transaction Concepts
    • Applications and Transactions
    • Creating Explicit Transactions
    • Using TRY/CATCH Error Handling
  13. Using .NET Code in SQL Server
    • Introduction
    • Writing SQLCLR Code
    • SQLCLR Code Modules
    • SQLCLR Security
    • T-SQL vs. .NET Code
  14. Advanced SQLCLR Code Techniques
    • Advanced SQLCLR Code Modules
    • Managing Code Modules
  15. Advanced Query Techniques
    • Full-Text Search
    • Advanced T-SQL Techniques
    • Executing Dynamic SQL
  16. Advanced Data Types
    • Introduction
    • The HierarchyID Data Type
    • Sparse Columns and Column Sets
    • FILESTREAM Storage
    • Spatial Data
  17. Implementing Partitions
    • SQL Server Partitions
    • Creating Partitioned Tables
    • Querying Partitions
    • Managing Partitions
  18. Complex Querying
    • Complex Queries
    • Ranking
    • Correlated SubQueries
    • Common Table Expressions
  19. Advanced Techniques
    • Complex Data and Structures
    • Efficient Queries
    • Working with Complex Queries
    • Maintaining Query Files
  20. Design and Deployment with Visual Studio
    • Team System for Database Professionals
    • Creating Databases and Managing Projects
    • Database and Project Management
  21. Working with XML
    • XML Data Type
    • XML Schema Collections
    • Querying XML
    • Best Practices
  22. Business Intelligence Services
    • Introduction to Business Intelligence Services
    • Using Integration Services
    • Using Analysis Services
    • Using Reporting Services