Banner

Developing SQL Databases

Live Classroom
Duration: 5 days
Live Virtual Classroom
Duration: 5 days
Pattern figure

Overview

This five-day course equips participants with the knowledge and skills essential for developing SQL databases. The course discusses how participants can use SQL Server product features and tools related to developing a database. After completing the course, participants will be skilled in implementation of a SQL Server database.
microsoft

What You'll Learn

  • Design and Implement Tables
  • Describe advanced table designs
  • Ensure Data Integrity through Constraints
  • Describe indexes, including Optimized and Columnstore indexes
  • Design and Implement Views
  • Design and Implement Stored Procedures
  • Design and Implement User Defined Functions
  • Respond to data manipulation using triggers
  • Design and Implement In-Memory Tables
  • Implement Managed Code in SQL Server
  • Store and Query XML Data
  • Work with Spatial Data
  • Store and Query Blobs and Text Documents

Curriculum

  • Introduction to the SQL Server Platform
  • SQL Server Database Development Tasks

  • Designing Tables
  • Data Types
  • Working with Schemas
  • Creating and Altering Tables
  • Lab : Designing and Implementing Tables
    • Designing Tables
    • Creating Schemas
    • Creating Tables

  • Partitioning Data
  • Compressing Data
  • Temporal Tables
  • Lab : Using Advanced Table Designs
    • Partitioning Data
    • Compressing Data

  • Enforcing Data Integrity
  • Implementing Data Domain Integrity
  • Implementing Entity and Referential Integrity
  • Lab : Using Data Integrity Through Constraints
    • Add Constraints
    • Test the Constraints

  • Core Indexing Concepts
  • Data Types and Indexes
  • Heaps, Clustered, and Nonclustered Indexes
  • Single Column and Composite Indexes
  • Lab : Implementing Indexes
    • Creating a Heap
    • Creating a Clustered Index
    • Creating a Covered Index

  • Index Strategies
  • Managing Indexes
  • Execution Plans
  • The Database Engine Tuning Advisor
  • Query Store
  • Lab : Optimizing Indexes
    • Using Query Store
    • Heaps and Clustered Indexes
    • Creating a Covered Index

  • Introduction to Columnstore Indexes
  • Creating Columnstore Indexes
  • Working with Columnstore Indexes
  • Lab : Using Columnstore Indexes
    • Creating a Columnstore Index
    • Create a Memory Optimized Columnstore Table

  • Introduction to Views
  • Creating and Managing Views
  • Performance Considerations for Views
  • Lab : Designing and Implementing Views
    • Creating Standard Views
    • Creating an Updateable view

  • Introduction to Stored Procedures
  • Working with Stored Procedures
  • Implementing Parameterized Stored Procedures
  • Controlling Execution Context
  • Lab : Designing and Implementing Stored Procedures
    • Create Stored procedures
    • Create Parameterized Stored procedures
    • Changes Stored Procedure Execution Context

  • Overview of Functions
  • Designing and Implementing Scalar Functions
  • Designing and Implementing Table-Valued Functions
  • Considerations for Implementing Functions
  • Alternatives to Functions
  • Lab : Designing and Implementing User-Defined Functions
    • Format Phone numbers
    • Modify an Existing Function

  • Designing DML Triggers
  • Implementing DML Triggers
  • Advanced Trigger Concepts
  • Lab : Responding to Data Manipulation by Using Triggers
    • Create and Test the Audit Trigger
    • Improve the Audit Trigger

  • Memory-Optimized Tables
  • Natively Compiled Stored Procedures
  • Lab : Using In-Memory Database Capabilities
    • Using Memory-Optimized Tables
    • Using Natively Compiled Stored procedures

  • Introduction to CLR Integration in SQL Server
  • Implementing and Publishing CLR Assemblies
  • Lab : Implementing Managed Code in SQL Server
    • Assessing Proposed CLR Code
    • Creating a Scalar-Valued CLR Function
    • Creating a Table Valued CLR Function

  • Introduction to XML and XML Schemas
  • Storing XML Data and Schemas in SQL Server
  • Implementing the XML Data Type
  • Using the Transact-SQL FOR XML Statement
  • Getting Started with XQuery
  • Shredding XML
  • Lab : Storing and Querying XML Data in SQL Server
    • Determining when to use XML
    • Testing XML Data Storage in Variables
    • Using XML Schemas
    • Using FOR XML Queries
    • Creating a Stored Procedure to Return XML

  • Introduction to Spatial Data
  • Working with SQL Server Spatial Data Types
  • Using Spatial Data in Applications
  • Lab : Working with SQL Server Spatial Data
    • Become Familiar with the Geometry Data Type
    • Add Spatial Data to an Existing Table
    • Find Nearby Locations

  • Considerations for BLOB Data
  • Working with FILESTREAM
  • Using Full-Text Search
  • Lab : Storing and Querying BLOBs and Text Documents in SQL Server
    • Enabling and Using FILESTREAM Columns
    • Enabling and Using File Tables
    • Using a Full-Text Index

  • Concurrency and Transactions
  • Locking Internals
  • Lab : SQL Server Concurrency
    • Implement Snapshot Isolation
    • Implement Partition Level Locking

  • Extended Events
  • Working with extended Events
  • Live Query Statistics
  • Optimize Database File Configuration
  • Metrics
  • Lab : Monitoring, Tracing, and Baselining
    • Collecting and Analyzing Data Using Extended Events
    • Implementing Baseline Methodology
waves
Ripple wave

Who should attend

Who should attend?

The course is highly recommended for –

  • Software engineers
  • Database administrators
  • Database architects
  • System administrators
  • System architects
  • SQL database administrators
  • SQL developers

Prerequisites

For this course, participants need to have basic knowledge of Microsoft Windows operating system and its core functionality, as well as working knowledge of Transact-SQL and relational databases.

Interested in this Course?

    Ready to recode your DNA for GenAI?
    Discover how Cognixia can help.

    Get in Touch
    Pattern figure
    Ripple wave