Introduction
This five day course focuses on using the SQL Server database to develop
applications. It discusses basic
as well as some of the advanced features of SQL Server: creating tables, views,
functions and stored procedures, asynchronous communication between servers with
service broker, the
integration of the .Net framework … We also focus on solving the common questions that developers have when working with SQL
Server: how do I design indexes, when should I denormalize data, how about
dealing with transactions and locking… We also discuss the new data types and
development techniques which are added to SQL Server 2008.
Learning Goals
Nearly any business application is based on a relational database to store
and deliver data. In this course, we see how to develop a database in SQL Server 2008. But databases have become more than ‘just a bunch of tables’. In this
course, we also look at advanced database features, such as asynchronous
database communications and .Net integration. We also investigate issues such as
index design, denormalisation etc.
Target audience
This course is intended for IT Professionals wanting to become skilled on SQL
Server 2008 product features and technologies for implementing a database. Parts
of this course are using .Net programming languages (C# and VB.Net) as well as
XML. This course assumes the participants already have a basic knowledge of
these technologies, as well as a basic understanding of relational databases in
general. Prior knowledge of SQL Server 2005 or 2008 is not required.
Course Outline
Module 1: Creating Databases and Database Files
Introduction to the development environment, creating log files and data
files, working with multiple files and filegroups, database properties.
Module 2: Creating Data Types and Tables
Discussion of the different data types, creating alias types, creating tables,
creating partition functions and partition schemas, partitioning tables, new
Date and Time data types.
Module 3: Using XML
Columns, variables and parameters of type XML, querying XML with XPath and
XQuery, XML related functions query, value, exist, nodes and modify.
Module 4: Creating and Tuning Indexes
Clustered versus nonclustered indexes, creating, inspecting and altering
indexes, primary xml indexes, path, property and value xml indexes, working with
filtered indexes.
Module 5: Implementing Data Integrity
Primary constraints, unique constraints, check constraints, foreign key
constraints, triggers, creating and enforcing xml schema collections.
Module 6: Implementing Views
Creating views, indexed views, partitioned views, constraints on views,
schema-binding.
Module 7: Implementing Stored Procedures and Functions
Creating stored procedures, parameters and stored procedures, creating scalar
functions, creating multi-statement table valued functions, creating in-line
table valued functions, constraints on functions.
Module 8: Implementing Managed Code in the Database
Creating stored procedures, functions, triggers, aggregation functions and
types in .Net, guidelines on choosing .Net versus T-SQL, new managed code
functionality in SQL Server 2008.
Module 9: Using Service Broker
Asynchronous communication between databases, creating message types,
contracts, queues, and services, sending and receiving messages, working with
priorities and sbdiagnose.
Module 10: Measuring database performance
SQL Profiler, performance counters and performance monitoring tool, linking
both tools.
Module 11: Optimising queries for performance
What Is Query Logical Flow, How the Query Optimizer Processes Queries,
Considerations to Take When Using Subqueries, Guidelines for Building Efficient
Queries.
Module 12: Optimizing an Index Strategy
How SQL Server Stores and Accesses Data, Considerations for Using Indexes,
Nonclustered Index Design, Best Use of Clustered Indexes, How to Document an
Indexing Strategy.
Module 13: Managing concurrency
Transaction Isolation Levels, Transaction Isolation Levels, Guidelines to
Reduce Locking and Blocking
Module 14: Working with spatial data
Introduction spatial data, Geometry and Geography data type (2008), working with
spatial methods, spatial indexing
Module 15: Streaming data access
Working with large objects (LOB), configuring databases for FileStream usage,
streaming data access
|