SolidQ Australia Pty Ltd > COURSE-DBDEVIMPLEMENTATION-BOOTCAMP

Microsoft SQL Server Development and Implementation Bootcamp

5-Day (COURSE-DB-DEVIMPLEMENTATION-BOOTCAMP)

Description

This 5 day course covers implementing a Microsoft SQL Server 2008 R2 Database. It covers core database development and implementation skills required for SQL Server. While based on SQL Server 2008 R2, this course is equally useful for attendees working with either SQL Server 2008 or SQL Server 2005.

Written and delivered by industry experts, this course is targeted at practical skills and features a large number of hands-on labs. While not targeted specifically at students aiming for certification, it would be an excellent resource for students aiming for Exam 70-433: TS: Microsoft SQL Server 2008, Database Development and for Exam 70-451: PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008.

Attendees will work through an extensive set of detailed and practical hands-on labs that reinforce the material presented.

For details on booking courses or seminars, please visit our booking page.

Audience

The presentations and labs have been designed specifically to give professional Developers and DBAs—those who work for an ISV, SI, or enterprise customer—the information they need to deliver SQL Server solutions on the Microsoft data platform.

This course is also relevant and useful for those interested to understand and evaluate Microsoft’s SQL Server offerings.

NOTE: This course is also very useful for those currently working with the SQL Server 2005 series of technologies. Differences between the releases are identified.

Prerequisites

Attendees should have a basic understanding of SQL Server and relational database technology.

Course Objectives

Through interactive presentations and concrete hands-on labs, attendees will learn:

Understand how to use SQL Server Tools
Understand SQL Server Data Types
Understand how to Design and Implement Tables, constraints, triggers, views, stored procedures and functions
Understand how to Pass Lists using Table Types and MERGE
Understand SQL Server Execution Plans
Understand how to Design and Implement Indexes
Understand how to Design and Implement Managed Code in SQL Server
Understand SQL Server Spatial Data
Understand XML in SQL Server
Understand how to Maintain SQL Server Application Concurrency
Understand how to Handle Exceptions in SQL Server Code
Understand Data-Tier Applications
Understand how to achieve Scale-out with SQL Server Service Broker
Understand Full-Text Indexes and Queries
Understand how to use SQL Server Column-based Encryption
Understand Filestream Data

Technologies

The workshop will empower developers to exploit the BI functionality in the following technologies:

SQL Server 2008 Database Services

While based on SQL Server 2008 R2 versions of SQL Server, this course is highly relevant for those working on SQL Server 2005 versions as well.

Course Outline

Day 1

Course Introduction

Module 1 Understanding SQL Server and Working with the SQL Server Tools

Introduction to SQL Server
Connecting to SQL Server
Working with SQL Server tools
LAB: Working with SQL Server Tools

Module 2 – SQL Server Data Types

Numeric Data Types
Character-based Data Types
Dates, Times and Specialized Data Types
LAB: SQL Server Data Types

Module 3 – Design and Implementation of Tables

Designing Tables
Implementing Tables
Table Structures
LAB: Design and Implementation of Tables

Module 4 – Design and Implementation of Constraints and Triggers

Check, Unique and Default Constraints
Primary and Foreign Key Constraints
DML Triggers
LAB: Design and Implementation of Constraints and Triggers

Day 2

Module 5 – Design and Implementation of Views

Designing Views
Implementing Views
Indexed Views
LAB: Design and Implementation of Views

Module 6 – Design and Implementation of Stored Procedures

Designing Stored Procedures
Passing Parameters to Stored Procedures
Implementing Stored Procedures
LAB:Design and Implementation of Stored Procedures

Module 7 – Design and Implementation of Functions

Scalar Functions
Table-Valued Functions
Performance Considerations with Functions
LAB: Design and Implementation of Functions

Module 8 – Passing Lists using Table Types and MERGE

Options for Passing Lists and Arrays to Stored Procedures and Functions
Table Type Parameters
MERGE Statement in T-SQL
LAB: Passing Lists using Table Types and MERGE

Day 3

Module 9 – SQL Server Execution Plans

Working with Execution Plans
Common Execution Plan Elements
Comparing Code Across Batches
LAB: SQL Server Execution Plans

Module 10 – Design and Implementation of Indexes

Clustered Indexes and Heaps
Non-clustered Indexes
Best Practices for Index Design
LAB: Design and Implementation of Indexes

Module 11 – Design and Implementation of Managed Code in SQL Server

SQL CLR Integration
Use Cases for CLR Integration
Implementing Managed Code in SQL Server
LAB:Design and Implementation of Managed Code in SQL Server

Module 12 – SQL Server Spatial Data

GEOMETRY and GEOGRAPHY Data Types
Implementing Spatial Data
Indexing Spatial Data for Performance
LAB: SQL Server Spatial Data

Day 4

Module 13 – XML in SQL Server

FOR XML and OPENXML
XML Data Type
XPath and XQuery
LAB: XML in SQL Server

Module 14 – Maintaining SQL Server Application Concurrency

Transactions
Isolation Levels and Locking
Deadlocks
LAB: Maintaining SQL Server Application Concurrency

Module 15 – Handling Exceptions in SQL Server Code

Traditional T-SQL Error Handling
Structured Exception Handling
SQL CLR Error Handling
LAB: Handling Exceptions in SQL Server Code

Module 16 –Data-Tier Applications

Creating and Deploying Data-Tier Applications
Upgrading Data-Tier Applications
Managing Data-Tier Applications Using SQL Server Utility
LAB: Data-Tier Applications

Day 5

Module 17 – Achieving Scale-out with SQL Server Service Broker

Core Service Broker Concepts
Service Broker Objects
Implementing Service Broker
LAB: Achieving Scale-out with SQL Server Service Broker

Module 18 – Full-Text Indexes and Queries

Querying Full-Text Indexing
Full-Text Index Implementation
Managing Full-Text Indexes
LAB: Full-Text Indexes and Queries

Module 19 – SQL Server Column-based Encryption

Introduction to Encryption
Encryption Hierarchies with Keys and Certificates
Implementing Column-based Encryption
LAB: SQL Server Column-based Encryption

Module 20 – Filestream Data

Storing BLOBS in Databases and Filesystems
SQL Server Filestream
Implementing Filestream
LAB: Filestream Data