iTrain Scotland IT Training Edinburgh image

Microsoft SQL Server 2016 Programming

iTrain Scotland IT Training Edinburgh image

Home > Courses > SQL courses > Microsoft SQL Server 2016 Programming

Microsoft SQL Server 2016 Programming

So, you’ve become adept at querying data in Microsoft SQL Server and now you’re ready to take your skills to the next level. Aimed primarily at IT Pro’s and developers, this 5 day course will enable you to gain much deeper insight into the inner workings of the database engine, write efficient queries that are built upon appropriate indexing structures, implement the various programmatic objects inherently supported by SQL Server, support less commonly used data-types, manage changing data and, essentially implement SQL Server in such a way as to get the maximum benefit and performance out of the product.

This is a very comprehensive and intensive course with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the learning experience. To successfully complete this course you should already be proficient at querying data and writing Transact-SQL code and, be familiar with the SQL Server Management Studio (SSMS) working environment.

Additionally, with the course structure being fully modularised, customised versions of this course can also be devised and delivered to suit individual requirements. In fact, specific content from this course could also be combined with material from other related courses in order to produce a bespoke training package – just ask for details.

With some slight modification, this course can also be delivered on a SQL Server 2014 platform.

Students should be familiar with basic programming concepts and, also understand the fundamental design of relational databases including, but not restricted to, the purpose of primary and foreign keys, entity relationships, and data normalization, such as can be found in the following course.

Course attendees should also possess good database querying skills gained in a Microsoft SQL Server environment. Knowledge of the subject matter covered in the following courses should be considered prerequisite.

Or, alternatively, this course.

Upon successful completion of the full course, students will be able to:


  • Implement and Work with SQL Server Cursors
  • Create Ad Hoc Distributed Queries
  • Setup and Manage Linked Servers
  • Understand and Implement Transactions
  • Understand SQL Server Locks
  • Understand SQL Server Indexes
  • Understand Query Performance Issues
  • Understand Table and Index Partitioning
  • Work with Full-Text Indexes
  • Create and Manage Views
  • Control the Flow of Program Execution
  • Create and Implement Stored Procedures
  • Handle Errors and Exceptions
  • Create and Implement User Defined Functions
  • Create and Implement Triggers
  • Understand and Query Metadata
  • Understand how to Secure Database Objects
  • Secure Access to Data
  • Create Database Objects in Managed Code (CLR)
  • Work with XML Data in SQL Server
  • Work with JSON Data in SQL Server
  • Use the HierarchyID Data Type to Work with Hierarchical Data
  • Understand how SQL Server Supports BLOB Data
  • Implement FILESTREAM and FileTables
  • Implement In-Memory OLTP Features
  • Understand Temporal Tables
  • Manage Changing Data

1. Cursors

Introduction to SQL Server Cursors

Retrieving Data Through a Cursor

Modifying Data Through a Cursor

Guidelines for Using Cursors


2. Distributed Queries

Ad Hoc Distributed Queries

Linked Servers

Distributed Queries Against Linked Servers


3. Transactions and Locking

What Is a Transaction?

How SQL Server Modifies Data in Tables

Managing Transactions

Nested Transactions

Transaction Isolation Levels

Delayed Durability

SQL Server Locking Architecture

Managing Locks


4. SQL Server Indexes

Understanding SQL Server Index Architecture

Creating and Using Nonclustered Indexes

Creating and Using Clustered Indexes

Filtered Indexes

Sparse Columns

Creating and Using Columnstore Indexes


5. Query Performance Considerations

How SQL Server Processes T-SQL Queries

Examining Execution Plans

Dynamically Generating T-SQL Code

Writing Efficient Search Arguments


6. Partitioning Indexes and Tables

Partition Functions

Partition Schemes

Partitioning Tables and Indexes

Querying Data in Partitions


7. Full-Text Indexes

Overview of Full-Text Indexes

Implementing Full-Text Indexes

Overview of Full-Text Search

Using the CONTAINS Predicate

Using the FREETEXT Predicate

Using the Rowset-Valued Full-Text Functions

Combining Full-Text Search and T-SQL Predicates

Useful Full-Text Indexing Guidelines


8. Implementing Views

Overview of Views

Creating and Modifying a View

Guidelines for Creating Views

Examining the Impact of Using SELECT * in Views

Restrictions for Modifying Data by Using Views

Indexed Views

Partitioned Views


9. Controlling Program Execution

Control-of-Flow Language Statements

Logical Functions


10. Implementing Stored Procedures

Overview of Stored Procedures

How Stored Procedures Are Executed by SQL Server

Creating and Using a Stored Procedure

Parameterising Stored Procedures

Returning Values from a Stored Procedure

Using Table Valued Parameters (TVPs)

Controlling Execution Context

Parameter Sniffing

Plan Guides


11. Error Handling

Using @@ERROR



Using the THROW Statement


12. Implementing User Defined Functions

Overview of User Defined Functions (UDFs)

Creating and Modifying Scalar UDFs

Creating and Modifying Table-Valued UDFs

Guidelines for Implementing UDFs


13. Implementing Triggers

Overview of Triggers

How Triggers Work

AFTER Triggers


DDL Triggers


14. Querying Metadata

Understanding Metadata

Querying Metadata by Using Views

Querying Metadata by Using Stored Procedures

Querying Metadata by Using Functions

Metadata Discovery in SQL Server


15. Managing Database Security

Managing Logins and Server Roles

Managing Users and Database Roles

Assigning and Managing Permissions

Using Schemas

Using Synonyms


16. Restricting Data Access

Row-Level Security

Dynamic Data Masking


17. CLR Integration

Overview of SQL CLR

Objects that can be Created in Managed Code

Creating a Scalar UDF

Creating a Table-Valued CLR UDF

Creating a CLR Trigger

Creating a CLR User-Defined Aggregate

Creating a CLR UDT

SQL CLR Guidelines


18. XML Support

Introduction to XML Support in SQL Server

How SQL Server Implements XML

Generating XML Based Reports

Querying XML by Using OpenXML

Introduction to XQuery

Querying XML by Using XQuery

Querying Relational Data Combined with XML Data

Use XML Indexes to Improve Performance


19. JSON Support in SQL Server 2016

Overview of Native JSON Support in SQL Server

Converting Tabular Results to JSON

JSON Related Functions

Converting JSON into Row Set Data


20. Using the HierarchyID Data Type

Creating a Table Using the HierarchyID Data Type

Working with the HierarchyID Data Type

Managing Hierarchical Data


21. Working with BLOBs and FILESTREAM

Overview of BLOB Data

Working with the Varbinary(max) Data Type

Implementing FILESTREAM

Implementing FileTables


22. In-Memory OLTP

Overview of In-Memory OLTP

Transactions and In-Memory OLTP

In-Memory OLTP Programmability

In-Memory OLTP Usage Guidelines


23. Temporal Tables

Overview of Temporal Tables

Implementing Temporal Tables

Temporal Tables Usage Guidelines


24. Tracking Changed Data

Introducing Change Data Capture (CDC)

Implementing CDC

Introducing Change Tracking

Implementing Change Tracking



Prices below are for scheduled courses held in our Edinburgh Training Centre. Major discounts available for onsite or private courses: click General Enquiry button below to enquire about private courses.
Group discount
1 place £ 3750 n/a
2 places £ 4500 n/a
3 places £ 11250 £ 5250
4 places £ 15000 £ 6000
5 places £ 18750 £ 6750
6 places £ 22500 £ 7500
7 places £ 26250 £ 8250
8 places £ 30000 £ 9000