Essential SQL Server for Developers provides hands-on training for SQL Server developers. Learn to write code for maximum database performance, re-usability, and application modularity. Discover how and when to use .NET support built into SQL Server 2005. Find out about new programming techniques enabled by SQL Server 2005 and Visual Studio 2005.

You'll get answers to these questions:

• How can I improve and evaluate the performance of my queries?
• How can I take advantage of query plans to optimize database
• performance?
• How can I write multi-threaded and asynchronous Transact-SQL
• (T-SQL) code?
• How can I integrate SQL Server with other applications and
• database servers?
• What's the best way to secure my data?

Come and learn how to utilize SQL Server to the max!

DevelopMentor's Essential courses provide five days of instructor-led training for the experienced developer. Gain deep understanding of your development platform. Acquire skills you need to be productive today. Build a solid foundation for more advanced topics.

• Understand the architecture of SQL Server
• Understand how SQL Server handles memory and threading
• Read query plans
• Assign indexes
• Use the new T-SQL operators
• Use the XML data type
• Write applications that use SQL Server Service Broker (SSB)
• Understand SQL Server Integration Services (SSIS)
• Use Reporting Services
• Take advantage of CLR integration to write great performing applications

Essential SQL Server 2005 for Developers (FW180)

Day 1
SQL Server Internals – I: The Architecture
The internal architecture of SQL Server 2005 was somewhat changed to previous versions. In this module we take a closer look at the internals and what impact they have for us as developers. Some topics covered are:

• Memory handling
• Threading behaviour
• SOS 
  

SQL Server Internals – II: Data Types
We look at data-types in general in SQL Server; how they are stored, etc. and we also look in-depth at the new data types SQL Server 2005 introduces; among them the VARMAX types and XML. We will also discuss XML usage, including indexing, and support for XML schemas.
    
SQL Server Internals – III: Security and Encryption
In this module we’ll look at how we as developers can use SQL Server’s security features. Some of the topics we will discuss are:

• Schemas
• Encryption
• Execution context

   
Day 2
SQLCLR – I: Programming SQLCLR
One of the really exciting features in SQL Server 2005 is the integration of .NET inside SQL Server (SQLCLR). Here we take a look at how to create assemblies that can be used from inside SQL Server. We’ll also catalog .NET assemblies and examine the appropriate metadata tables.
    
SQLCLR – II: SQLCLR Data Access
Some SQLCLR procedures, functions, and triggers need access to data in the database. The SqlClient data provider has been extended to support optimized access without requiring an addition connection. We'll talk about how to use this provider inside the database, as well as the extensions that work under inside the database.
    
Client-Side Data Access using ADO.NET 2.0 SqlClient Provider
Microsoft enhanced the SqlClient provider in ADO.NET 2.0 in order to be able to utilize new features in SQL Server 2005. In this module we’ll look at these new enhancements. Some of the topics are:

• Multiple Active Resultsets (MARS)
• Asynchronous Execution
• Query Notifications

   
Day 3
T-SQL - I: New Functionality in SQL Server 2005
In this module we look at new features in SQL Server 2005 that can make life easier for use as developers. Examples of what is covered are:

• Error handling
• Ranking and Pivot
• New trigger types  
  

T-SQL – II: Transactions and Locking
The transaction module is a deep dive into the various choices we have when writing transactional code in SQL Server (both T-SQL as well as .NET). We’ll look at how SQL Server handles transactions. Hand in hand with transactions goes locking and we’ll look at how SQL Server does locking. Some topics are:

• Isolation Levels
• DTC Concepts
• Nesting of transactions (or not)
• Locking scenarios

Service Broker
SQL Server 2005 introduces asynchronous queuing inside the database through the SQL Server Service Broker. We’ll set up a simple Service Broker-based application using the new broker DDL and objects, then program the application using DML extensions. We’ll also discuss how Service Broker solves some very difficult problems in producing stateful but scalable applications.
    

Day 4
Performance – I: Plan Cache and Query Plans
In this session we look at SQL Server's Query Cache, what it is and how it works. We'll also look at how to interpret query plans, and use system views to trouble-shoot queries.

• Query plans and plan guides
• System management views related to query plans

Performance – II: Database Objects and Indexes
In this module we look at things that can affect performance based on the objects in the database, and how make sure we are architecting our databases in the best possible way. Example of topics included in this module:

• Indexing
• Partitioning
• Database Tuning Advisor

Performance – III: T-SQL Code
Here we look at what to think about when coding TSQL so we are getting as good performance as possible. We will be looking at how different ways of writing code can affect your queries. We are also looking at new T-SQL operators in SQL 2005 which can makes performance improvements for our code:

• Temp table vs. table variables
• Derived sub-queries and common table expressions (CTE's)
• Apply operator " Parameter sniffing

   
Day 5
Performance – IV: T-SQL vs. SQLCLR
Given the capabilities to write code using both T-SQL as well as .NET, what should we choose? In this module we look at the use cases for both scenarios.
    
SQL Server Integration Services
SQL Server Integration Services (SSIS) is completely re-written in SQL 2005. In this module we’ll get an overview of SSIS and look at topics like:

• Task Flows
• Data Flows

SQL Server Reporting Services
SQL Server Reporting Services (RS) was first introduced as an add-on component for SQL Server 2000. In SQL Server 2005, RS is part of the install. Here we cover how to write reports, and how to deploy them. We will also look at programming within Reports, using the Reporting Web Service and using the ReportViewer Controls.