Advanced Programming in SQL Server

Rating:
1 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 5
Loading...
Please Log in or register to rate

Advanced Programming in SQL Server

DB-204

The Advanced Programming in SQL Server course takes programming in SQL Server from the basic level to an advanced level, providing students with tools and techniques, which will allow them to write code for a broad range of requirements, while optimizing performance. Throughout the course the students will analyze different case studies, while comparing several approaches for each one, and choosing the best solution.

The course is based on SQL Server 2014, but it is relevant also for previous versions of SQL Server (2005/2008/2008R2/2012).

Audience

Target Audience:
The course is intended for SQL Server developers, whether development DBAs or application developers.

Prerequisites:
Students should be familiar with basic programming and with the syntax of Transact-SQL.

Course Objectives
  • Understand the various data structures and data types within SQL Server
  • Be able to design and utilize indexes and statistics efficiently
  • Be familiar with the various programming objects in SQL Server
  • Understand how the query processor works
  • Learn to write efficient Transact-SQL code using advanced techniques
Course Topics

Module 1 – Course Introduction

Module 2 – Data Structures

File and Filegroup Organization
Table and Index Organization
Data Types
Tables
Practice

Module 3 – Understanding Indexes

Index Types
Missing Indexes
The Database Engine Tuning Advisor
Guidelines for Writing Efficient Queries
Index Maintenance
Practice

Module 4 – Understanding Statistics

Statistics Types
Statistics Maintenance
Practice

Module 5 – Programming Objects Best Practices

Views
User-Defined Functions
Stored Procedures
Triggers
Dynamic SQL
Metadata Discovery
Connecting to SQL Server through .NET
Practice

Module 6 – XML

Introduction to XML
XPath and XQuery
The XML System Data Type
XML Schema Collections
XML Indexes
Converting Relational Data to XML
Converting XML to Relational Data
Practice

Module 7 – Query Processor Internals

The Procedure Cache
Compilation-Execution Sequence
Recompilations
Query Logical Simplification
Parameterization
Practice

Module 8 – Understanding Execution Plans

Execution Plan Representation
Logical and Physical Operators
Cost and Cardinality
Estimated vs. Actual Execution Plan
Practice

Module 9 – Transactions and Locks

Transactions Overview
Lock Types
Concurrency Issues
Transaction Isolation Levels
Locking Hints
Deadlocks
Nesting Transactions
Practice

Module 10 – Advanced Programming Techniques

Ranking Functions and Window Functions
Implementing Query Paging
Grouping Sets
The MERGE Statement
Generating Random Values
COUNT (DISTINCT) vs. MIN/MAX
TOP vs. MIN/MAX
Practice

Module 11 – Summary

© Copyright - Skilit - Site by Dweb