Additional Material: Download Course Slides Link
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