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.
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
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.
