"A Training Solution Provider delivering Learning Skills to keep forever"

Call the JCS training team free now 0800 5425 150 Or mail Email | training@jcstraining.com
  • Microsoft/SQL Server 2016 Programming Duration: 5 days

SQL Server 2016 Programming Duration: 5 Days

Course Overview

Introduction A brief overview of the course 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.

 

 

 

 

Prerequisites
What you should know before attending this course

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.

  • Microsoft SQL Server 2016 Relational Database Design and Querying Fundamentals

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.

  • Microsoft SQL Server 2016 Introduction to Querying
  • Microsoft SQL Server 2016 Advanced Querying

Or, alternatively, this course.

  • Microsoft SQL Server 2016 Querying Essentials

Objectives
What the course sets out to achieve

Upon successful completion of this 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

Outline
The material covered during the course

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 RAISERROR
  • Using TRY…CATCH
  • 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
  • INSTEAD OF 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