(Retired – no longer offered as a public class)

This 5-day SQL Server training class is designed to cover all the information developers should know when coding in SQL Server. It could be used (as has been) as a class geared toward obtaining the SQL Server Developer Certification, although it goes beyond the knowledge required for that certification. Coding for best practices and best performance is stressed throughout. The class focuses on, but is not limited to, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. Coding with “Beyond Relational” feature will be covered as well as T-SQL related coding.

Instructor: Bob Beauchemin

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Curriculum

Part 1: Using Transact-SQL
This section covers T-SQL, the language most often used for writing code in SQL Server. Special attention is paid, not only to covering new SQL Server 2005 – 2014 developer features, but also to illustrating best practices and coding for performance throughout.

Module 1: Data Types in SQL Server

This module covers the data types that are available in SQL Server from a usage point of view. This module covers the T-SQL functions that apply to different data types as well. We’ll pay special attention to:

• Character data – Unicode and Nonunicode, collations, international considerations
• Integral and floating point numeric series
• Date and Time types – including expanded date and time types
• Special data types – table parameters, table types, UNIQUEIDENTIFIER, SQLVARIANT
• Large data types – using VARCHAR(MAX)/VARBINARY(MAX) as replacements for TEXT and IMAGE
• FILESTREAM storage and FileTables
• .NET data types – Geometry, Geography, and HierarchyID
• Uniqueness – identity, sequences, and uniqueidentifiers
• Integrity constraints
• In-memory tables and table variables in SQL Server 2014

Module 2: Writing Effective Queries

This module starts out with an overview of the SQL language, illustrating best practices. Evaluating a query for syntactic correctness comes next, because knowing the parts of a SQL query helps to avoid most SQL coding errors. We then proceed to cover common SQL constructs, using a query plan-oriented approach to ensure best performance.

• JOINs in detail
• Reading query plans
• Subqueries and derived tables
• CASE statement
• UNION, EXCEPT, and INTERSECT
• Relational division
• CROSS APPLY and OUTER APPLY
• Common Table Expressions

Module 3: Summarization and Analysis

This module covers T-SQL functions that are used to summarize and group rows, with special attention paid to enhancements for windowing, ranking and paging. We include:

• Row numbering, ranking, and analytic functions
• Expanded windowing functions
• Paging sets of data
• TOP clause
• PIVOT operator
• GROUPING SETS, ROLLUP, and CUBE
• Writing your own aggregate functions in SQLCLR

Module 4: Optimizing Action Statements (INSERT, UPDATE, DELETE, MERGE)

Topics covered include:

• Row constructors
• Query plans for action statements
• INSERTing SETS – SELECT INTO, INSERT-SELECT, and INSERT-EXECUTE
• UPDATE – updating via JOINs and CTEs
• INSERTing and UPDATing views
• DELETE statement – DELETE vs. TRUNCATE TABLE
• MERGE statement – INSERT/UPDATE/DELETE in one statement
• Optimizing action statements with TOP clauses
• OUTPUT from action statements

Module 5: Solving Difficult Problems in Relational Databases

The module covers, in detail, the two most difficult problems in relational development: Hierarchies and Sparse Attributes. We’ll pay special attention to SQL Server 2005 and 2008 improvements in these areas.

• Hierarchical data – trees and graphs, single-parent and multi-parent hierarchies
• Implementation – adjacency column vs. path enumeration vs. nested sets
• Querying hierarchical data with recursive Common Table Expressions
• The HierarchyID data type – an implementation of the path enumeration model as built-in type
• Modeling sparse attributes – sparse table vs. sparse columns vs. entity-attribute-value vs. XML
• PIVOT, UNPIVOT, and entity attribute value designs
• Sparse column support and designing for sparse columns

Part 2: Optimizing Transactional/Procedural Code
This section covers writing effective code to optimize database functionality. SQLCLR – that is writing procedural code in .NET languages, is also covered.

Module 1: Optimizing Transactions

This module covers the essential concepts of transactions, that is, composing multiple SQL statements into an atomic unit of work. The topics covered include:

• Batches and transactions
• Transaction modes
• Understanding BEGIN-COMMIT-ROLLBACK-SAVEPOINT transaction nesting
• Distributed transactions
• Error handling in T-SQL
• TRY-CATCH error handling
• T-SQL vs. SQLCLR error handling

Module 2: Transaction Isolation

A relational database isolates each user from other users’ work using a locking and, in some cases, a combination of locking and versioning. The module covers SQL Server’s isolation levels when using locking and versioning. Topics covered include:

• Common problems in transaction isolation
• Locking and transactions – how the four transaction isolation levels solve the common problems
• SQL Server and statement-level consistency
• Versioning in SQL Server 2005 and beyond
• Enabling versioning isolation
• Versioning and transactions – how the two versioning isolation levels solve the common problems
• Versioning and statement-level consistency
• Versioning uses and drawbacks
• Enabling and monitoring versioning
• Multi-version concurrency control in SQL Server 2014

Module 3: Optimizing Procedural Code – Stored Procedures

This module covers the things a programmer can be aware of in order to produce better performing stored procedures. We cover all of the programmer/administrator-controllable areas that can result in slow and suboptimal code. Topics covered include:

• Types of stored procedures
• Compiled stored procedures in SQL Server 2014
• When and how to optimize code and queries
• The ten main reasons why queries run slowly
• Query processing and execution – observing and troubleshooting
• Using dynamic management views to obtain query information
• Plan selections, statistics, and indexes
• Optimizing query plan reuse
• Parameterization and auto-parameterization
• Parameter sniffing and query plan reuse
• How to control plan reuse
• Choosing the correct table structures – temp table vs. table variables
• Procedural code and query optimization
• Query hints, plan forcing, and plan guides – implementing plan stability

Module 4: Optimizing Procedural Code – User-Defined Functions

Each of the three types of user-defined function has its special usage, but user-defined functions are not stored procedures. In this module, we investigate the best usage of these functions for performance. We’ll look at query plans produced and investigate when SQLCLR might be a better choice for some use cases. Topics covered include:

• The three types of user-defined functions
• Limitations of user-defined functions
• UDFs vs. stored procedures
• Query plans produced by user-defined functions
• Drawbacks of database access and scalar user-defined functions
• SQLCLR vs. T-SQL for scalar user-defined functions
• Designing views for best performance
• Indexed views
• Distributed Partitioned Views
• Instead-of and after triggers
• Inserted and deleted tables
• Determining the number of rows affected

Module 5: SQLCLR Coding

This module covers how the CLR runs in SQL Server’s process for best security, reliability, and performance. The specifics of mapping T-SQL constructs or CLR and coding We’ll discuss SQLCLR vs. T-SQL and the use cases when each one is the best choice.

• SQLCLR internals
• Working with assemblies
• Assembly security
• SQLCLR and .NET framework interaction
• Troubleshooting tools
• .NET procedures, functions, and triggers
• Data type mappings, nullability, and parameters
• T-SQL vs. SQLCLR vs SQL middle-tier code

Part 3: Beyond Relational Topics
This last day covers writing using XML in SQL Server. In addition, more specialized topics such as Service Broker, Full-text Search and Tracking Changes are covered. If time permits, there will be a question-answer session on topics of the students’ choice that are listed as SQL Server Developer exam topics.

Module 1: Designing for the Correct Data Model

XML is often used for unstructured documents. XHTML pages, book text, and legal documents fit into this category. Maintaining document order is critical. Semi-structured data may contain a definite “outline”-type structure with large text unstructured passages.

XML can be stored and used in its native form by using the XML data type, or you can take advantage of XML’s hierarchical nature to store the data in multiple relational tables. This is analogous to decomposing an object graph, so making rowsets from XML is decomposition. Recreating the XML hierarchy is known as XML composition. SQL Server has support for composition and decomposition, using this can sometimes result in a more performant application.

• XML data type specifics
• Using different encodings
• XML Schema support with XML Schema Collections
• XML decomposition – OPENXML and XML.nodes method
• XML composition – SELECT FOR XML
• SELECT for XML variations and options

Module 2: Querying and Modifying XML

Being able to effectively query your data is critical in any application, especially with data that contains structure and free-form text. XQuery is the language for querying XML in a database environment. It can be SQL-like in structure. In addition, SQL Server implements a XML modification language using an XQuery dialect specific to SQL Server.

• XPath and FLWOR expressions
• XQuery methods
• Using XQuery to query the plan cache and XEvents
• XML indexing internals
• Choosing the best XML index
• Selective XML indexes
• Modification operations – XML DML
• Locking and logging during modification

Module 3: Full-Text and Semantic Search

Full-text search has been an optional component of SQL Server since SQL Server 7. It provides special components, indexes, and T-SQL functions that allow semantic queries and relevance ranking of unstructured data in addition to traditional T-SQL LIKE operations that implement string-based queries. In this module, I’ll discuss the individual components that comprise full text search, as well as how to define and manage full text catalog, indexes, and queries.

• Full-text indexes – definition and performance-related choices
• Programming full-text and semantic search
• Full-text index plans and best practices for performance
• Troubleshooting full-text search with Dynamic Management Views and XEvents

Module 4: Asynchronous T-SQL using SQL Server Service Broker

Service Broker is queued, asynchronous functionality inside the database, defined through database objects with DDL and programmed using DML with some broker-specific extensions. This module looks at Service Broker to implement application decoupling leading to better scalability and reliability. Topics include:

• Decoupling a traditional stored procedure
• Service Broker database object basics
• Service Broker internals
• Message priority
• Activation-based programming
• Conversations, messages, and conversation groups
• Diagnosing broker-related applications
• Best practices for broker-based programming

Questions?

If you have any questions, please contact us.