Developing SQL Database
Overview
Course Objective
After completing this course, students will be able to:
• Design and Implement Tables.
• Describe advanced table designs.
• Ensure Data Integrity through Constraints.
• Describe indexes, including Optimized and Column store 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
Who Should Attend
Prerequisites
Training Calendar
Intake
Duration
Program Fees
Module
Module 1 - Introduction to Database Development
• Introduction to the SQL Server Platform
• SQL Server Database Development Tasks
• Lab: SQL Server Database Development Tasks
Module 2 - Designing & Implementing Tables
• Designing Tables
• Data Types
• Working with Schemas
• Creating and Altering Tables
• Lab: Designing and Implementing Tables
Module 3 - Advanced Table Designs
• Partitioning data
• Compressing Data
• Temporal Tables
• Lab: Using Advanced Table Designs
Module 4 - Ensuring Data Integrity through Constraints
• Enforcing data Integrity
• Implementing Domain Integrity
• Implementing Entity and Referential Integrity
• Lab: Ensuring Data Integrity through Constraints
Module 5 - Introduction to Indexes
• Core Indexing Concepts
• Data Types and Indexes
• Single Column and Composite Indexes
• Lab: Implementing Indexes
Module 6 - Designing Optimized Index Strategies
• Covering Indexes
• Managing Indexes
• Execution Plans
• Using the DTE
• Lab: Designing Optimized Index Strategies
Module 7 - Column Store Indexes
• Introduction to Column Store indexes
• Creating Column Store indexes
• Working Column Store indexes
• Lab: Using Column Store indexes
Module 8 - Designing & Implementing Views
• Introduction to views
• Creating and managing views
• Performance considerations for views
• Lab: Designing and Implementing Views
Module 9 - Designing & Implementing Stored Procedures
• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context
• Lab: Designing and Implementing Stored Procedures
Module 10 - Designing & Implementing User-Defined Functions
• Overview of Functions
• Designing and Implementing Scalar Functions
• Designing and Implementing Table-Valued Functions
• Implementation Considerations for Functions
• Alternatives to Functions
• Lab: Designing and Implementing User-defined Functions
Module 11 - Responding to Data Manipulation via Triggers
• Designing DML Triggers
• Implementing DML Triggers
• Advanced Trigger Concepts
• Lab: Responding to Data Manipulation via Triggers
Module 12 - Using In-Memory Tables
• In-Memory tables
• Native Stored Procedures
• Lab: In Memory OLTP
Module 13 - Implementing Managed Code in SQL Server
• Introduction to SQL CLR Integration
• Importing and Configuring Assemblies
• Implementing SQL CLR Integration
• Lab: Implementing Managed Code in SQL Server
Module 14 - Storing & Querying XML Data in SQL Server
• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type
• Using the T-SQL FOR XML Statement
• Getting Started with xQuery
• Lab: Storing and Querying XML Data in SQL Server
Module 15 - Working with SQL Server Spatial Data
• Introduction to Spatial Data
• Working with SQL Server Spatial Data Types
• Using Spatial Data in Applications
• Lab: Working with SQL Server Spatial Data
Module 16 - Storing & Querying Blobs & Text Documents in SQL Server
• Considerations for BLOB Data
• Working with FileStream
• Using Full-Text Search
• Lab: Storing and Querying Blobs and Text Documents in SQL
Server
Module 17 - SQL Server Concurrency
• Concurrency and Transactions
• Locking Internals
• Lab : SQL Server Concurrency
Module 18 - Performance and Monitoring
• Extended Events
• Working with extended Events
• Live Query Statistics
• Optimize Database File Configuration
• Metrics
• Lab : Monitoring, Tracing, and Baselinin
FAQs
General Questions:
Q: What is the 20762 Developing SQL Database course about?
A: This course provides students with the knowledge and skills to develop a Microsoft SQL Server 2016 database. It focuses on using SQL Server 2016 features and tools related to database development, including designing tables, ensuring data integrity, working with indexes, views, stored procedures, functions, and more.
Q: Who should attend this course?
A: This course is ideal for IT professionals looking to become skilled in SQL Server 2016 database development. It’s also suitable for developers from other platforms seeking to gain expertise in implementing a SQL Server 2016 database.
Q: What are the prerequisites for this course?
A: Participants should have experience with relational database concepts and basic SQL queries. Prior experience with database development or administration is helpful but not required.
Q: How long is the course?
A: The course duration is 5 days.
Q: What key topics are covered in this course?
A: The course covers topics such as designing and implementing tables, ensuring data integrity through constraints, working with indexes (including column store), creating and managing views, designing stored procedures and user-defined functions, using triggers, in-memory tables, XML data, spatial data, BLOBs, and more.
Q: Will I receive a certification after completing the course?
A: This course prepares you for becoming proficient in SQL Server 2016 database development. While it does not provide a certification, it offers the skills necessary to excel in SQL Server development tasks.
Program Content & Skills:
Q: What is the 20762 Developing SQL Database course about?
A: This course provides advanced technical skills for SQL Server 2016 database development, covering topics such as table design, data integrity, indexes, views, stored procedures, triggers, and XML data. It is designed for IT professionals and developers working with SQL Server 2016 and prepares them to develop and manage databases using SQL Server.
Q: Who should attend this course?
A: This course is ideal for IT professionals and developers who want to gain skills in SQL Server 2016 database development, including system administrators, database developers, and those transitioning from other platforms to SQL Server.
Q: What are the prerequisites for this course?
A: There are no mandatory prerequisites, but it is recommended that participants have experience with relational database concepts and basic SQL queries. Familiarity with database development or administration is beneficial.
Q: How long is the course?
A: The course duration is 5 days.
Q: What specific topics are covered in the course?
A: The course covers designing and implementing tables, ensuring data integrity, working with indexes, column store indexes, creating views, stored procedures, user-defined functions, triggers, in-memory tables, XML and spatial data, BLOBs, and more.
Q: Will I learn about real-world database development techniques?
A: Yes, the course focuses on practical, hands-on experience, allowing participants to apply database development techniques in real-world SQL Server environments.
Q: Will I receive a certification after completing the course?
A: This course prepares you to develop and manage SQL Server 2016 databases. While it does not offer a specific certification, it provides the skills needed for SQL Server database development roles.
Submit your interest today !
