Essential SQL Server 2012 for Developers Training

Essential SQL Server for Developers provides the definitive hands-on training for SQL Server developers to master crucial SQL server programming fundamentals using top industry practices from an expert who knows SQL intimately. Learn to write robust code for maximum database performance, re-usability, and extensive application modularity. Find out about innovative programming techniques enabled by SQL Server 2012 and Visual Studio 2010.

In this course, you learn to:

• Full comprehension of the architecture of SQL Server
• Detailed understanding of how SQL Server handles memory and threading
• Effective techniques to read query plans and assign indexes
• Skills to conquer the new data types and language features in T-SQL, like common table expressions, upserts, try/catch error handling, and windowing functions
• Powerful knowledge of the Entity Data Framework and SSDT
• Invaluable information on the built-in auditing abilities of SQL Server reflecting the latest trends
• In-depth analysis of locking, blocking, transactions, and isolation

Day 1

T-SQL Sets

In this exciting first day, we dig into the complex T-SQL language to discuss best practices for our queries for superior functioning. We look at how different ways of writing code can affect your queries. We study new T-SQL operators which can create powerful performance improvements to our code. We begin by comparing temp tables and table variables, and then dive in to derived sub-queries and common table expressions (CTE's) followed by the use of the 'apply' operator. We see how parameter sniffing drastically affects query execution. Finally, we turn to the considerations for various query operators including union, intersect, except, and pivot sets.

T-SQL Enhancements

The impressive work Microsoft started in SQL Server 2005 to improve the T-SQL language has continued in SQL Server 2012. SQL Server 2008 introduced valuable new T-SQL language features such as the keyword Microsoft implemented to handle upserts, called 'MERGE'.  To make working within in-memory tables easier, we cover User Defined Table Types and Table Valued Parameters. Traditional T-SQL error handling using return types is cumbersome and difficult to maintain, we examine how try/catch error handling is a big improvement.  Finally, we learn how to use windowing functions to solve tougher problems in fewer lines of code.  Also we'll look at FILETABLES introduced in SQL Server 2012.

T-SQL Data Types

T-SQL data types are key elements in unlocking the power of SQL Server 2008. We examine essential principles and practices in data types with an in-depth study of the VARMAX types.  Here, and in a later module, we look at developments and enhancements to the type system including the more traditional T-SQL types. We will also cover hierarchies and the file stream data type.

Day 2

Architecture

The world of the DBA is changing as they are pushed to manage more complex systems with incredible amounts of data, well into the Terabytes, while trying to keep cost low; DBAs are given more responsibilities of providing instant data access and creating applications that perform reliably to meet the heavy demands of the business. The internal architecture of SQL Server 2012 was slightly changed from previous versions as the demands placed on the DBA skyrocketed in terms of scalability, manageability and quick access to data. In this module, we inspect the internals, how they interrelate, the effects they have on performance and tuning issues, and what potential impacts they have for us as developers.

Transactions and Locking

How many times have you had users report time-outs and freezes causing a domino effect of jammed reports because a query incorrectly locked a table? Did you even know the time-out might have been caused by an improper transaction? The transaction module probes into the various choices we have when writing transactional code in SQL Server (both T-SQL as well as .NET). We see how SQL Server handles transactions, locking and discover alternatives that alleviate the headaches of poorly written code.

Security

There is a lot at risk when securing a server from potential attacks with the abundance of existing sensitive data available such as social security numbers, bank accounts, medical records, and credit card numbers. In this module, we look at how we as developers can use SQL Server's security features to properly protect our databases from internal and external threats. We examine how to encrypt data in the database, how schemas can make security permissions easier, and insider tips on security credentials and execution context.

Day 3

Extended Events

SQL Server has always had the ability and tools to monitor server state and diagnose issues inside SQL Server (for example, SQL Trace and performance monitors). SQL 2005 introduced the ability to use the tracing capabilities within the operating system event Tracing for Windows (ETW). SQL Server 2008 and 2012 builds upon this with Extended Events (EE), a general event-handling infrastructure. Extended Events is by far the most flexible and efficient event engine available within SQL Server. We look at the architecture of EE, the various components that EE introduces and also how we can use EE together with ETW to correlate event data from inside SQL Server with data from the operating system.

Performance Tuning with Indexes

Often when developers are asked to solve performance problems, the answer is to "throw hardware at it".  While inelegant, hardware can solve some performance issues. Once that option is exhausted, it's important to know how indexing, table partitioning, and query tuning can dramatically improve the performance of your SQL Server. Once the techniques taught in this module are applied, it will appear as if a switch has been turned on; the SQL Server will be running at an impressive new speed.  We'll also cover columnstore indexes introduced in SQL Server 2012.

Performance Tuning with Cache

In this session, we look at SQL Server's Query Cache, what it is and how it works. We learn to interpret query plans, and to use system views to trouble-shoot queries. Students are given the tools and knowledge to manipulate the four most important performance counters when tuning SQL Server including CPU usage, memory usage, cache hit ratios, and disk I/O.

Day 4

Automatic Data Change Management

On our final day together we embark on learning features introduced in SQL Server 2008: Change Data Capture or Change Tracking. It is a lightweight solution that provides an efficient change-tracking mechanism for applications. Using change tracking in SQL Server 2008, we no longer need to roll into our applications our own solution to monitor changed data. This overview shows how change tracking is implemented inside SQL Server and the benefits gained.

Entity Framework

Here we cover how EF works with SQL Server. We review the more advanced topics, such as mapping between the database and the business objects, and execution of stored procedures and user-defined functions. We learn to use existing stored procedures to update data in Entity Framework.

SQL Server Data Tools

You'll work with the new SSDT tools which can stand alone or be integrated into Visual Studio 2010.  You'll understand how to safely refactor database code using the new tools which allow you to view, change, and compare various versions of your T-SQL schemas. 

Additional Bonus Material:

This course also includes optional material that can be covered if time allows, including: Database Design Techniques, SQL Azure, XML, SQL CLR and improvements to the Geospatial data types and indexing.

Upcoming Dates & Locations