Training in SQL Server Development

Key Information

Duration: 4 Days days

Course Learning Skills: SQL
Additional Material: Download Course Slides Link

Brief

Objectives

This course will enable delegates to program a SQL Server database. This includes developing in Transact-SQL. Along with developing databases in the Azure cloud. The course will cover the structure of a SQL Server  database. Delegates will be able to create stored procedures, triggers, functions and views. The course will cover improving database performance by indexing tables. It will also cover the new features in the latest version of SQL Server.

Duration:

4 Days

Audience

This course is designed for those who are responsible for implementing database objects and programming SQL Server databases by using Transact-SQL.

Prerequisites

An understanding of basic relational database concepts, including:

  • Logical and physical database design
  • Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many)
  • How data is stored in tables (rows and columns)

5 Top Takeaways from the Course

  • Improve database design
  • Improve query performance
  • Make your existing data work harder for you
  • Secure data stores by restricting user access
  • Work with  data in the cloud or on-premises

Module 1: SQL Server Overview

  • SQL Server Components and Architecture
  • SQL Server Developer Tools
  • SQL Server Security Implementation

Module 2: Language Features

  • SQL Server Programming Tools
  • Elements of Transact-SQL
  • SQL Server Object Names
  • Additional Language Elements
  • Local Variables
  • Operators
  • Functions
  • Ways to Execute Transact-SQL Statements
  • New Transact-SQL (T-SQL) Features

Module 3: Design A Database

  • Define Entities
  • Apply Normalisation
  • Database Objects
  • System Tables
  • Defining Databases
  • Setting Database Options
  • Managing Data and Log File Growth

Module 4 Implement Tables

  • Create Tables
  • Adding and Dropping a Column
  • Generating Column Values
  • Adding Constraints

Module 5: Access and Modify Data

  • Selecting Data
  • Using Sub Queries
  • Select Queries with Joins
  • Modifying Data
  • Importing Data to Tables
  • Writing and Reading XML

Module 6: Advanced Query Techniques

  • Inner Joins
  • Outer Joins
  • Self Joins
  • Sub Queries
  • Creating Pivot Queries
  • Working with XML

Module 7: Implement Views

  • Introduction to Views
  • Advantages of Views
  • Defining Views
  • Modifying Data through Views
  • Optimizing Performance by Using Views

Module 8: Implement Functions

  • What Is a User-defined Function?
  • Defining User-defined Functions
  • Types of User-defined Functions
  • Scalar
  • Inline
  • Multi-statement

Module 9: Implement Triggers

  • Introduction to Triggers
  • Defining Triggers
  • How Triggers Work
  • Examples of Triggers
  • Performance Considerations

Module 10: Implementing Stored Procedures

  • What Is A Procedure?
  • Types of Stored Procedure
  • Creating Stored Procedures
  • Declaring Parameters
  • Set Return Value in A Stored Procedure

Module 11: Indexing Tables

  • Introduction to Indexes
  • Index Architecture
  • How SQL Server Retrieves Stored Data
  • How SQL Server Maintains Index and Heap Structures
  • Deciding Which Columns to Index

Module 12: Managing Transactions and Locks

  • Introduction to Transactions and Locks
  • Managing Transactions
  • SQL Server Locking
  • Managing Locks

Module 13: Design and Administer Security Levels

  • Design security plan
  • Administer authentication
  • Administer authorisation
  • Administer permissions
  • Administer users, groups and roles

Module 14: Monitoring and Tuning

  • Use SQL Profiler to monitor a database
  • Describe how the Index Tuning Wizard works and when to use it
  • Define database partitioning

Module 15: Programming Replication

  • Overview of SQL Server Replication
  • Replication Programming Interfaces
  • Configuring Replication
  • Synchronizing Data

Module 16: Introduction to Integration Services

  • Importing and Exporting Data
  • Integration Services Tools
  • Building a Package
  • Troubleshooting a Package

Module 17: Introduction to Reporting Services

  • Understanding Reporting Services
  • Configuring Reporting Services
  • Building a Simple Report
  • Creating, Publishing, and Viewing Reports
  • Using and Managing Published Reports

 Module 18: New Features in SQL Server 

  • Always Encrypted
  • Stretch Database
  • Real-time Operational Analytics
  • PolyBase into SQL Server
  • Native JSON Support
  • Enhancements to AlwaysOn
  • Enhanced In-Memory OLTP

Module 19: Working with Azure SQL Databases

  • Creating an Azure SQL Database
  • Connecting to an Azure SQL Server
  • Exporting data from an on-premises database
  • Querying an Azure SQL Database

 

 

Register your interest in a Talk-IT Course

Course Interest

Do a short survey to tell us what you think about training?

Click here to take the survey, it’ll only take a few minutes!

Scroll to Top