MOC 20762: Developing SQL Databases

Learn to design and develop a Microsoft SQL Server 2016 database.

The course focuses on teaching individuals how to use SQL Server product features and tools related to developing a database.

Što ćete naučiti

  • Design and implement tables.
  • Advanced table designs.
  • Ensure data integrity through constraints.
  • 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

Kome je namijenjeno

  • The primary audience for this course is IT Professionals who want to become skilled on SQL Server product features and technologies for implementing a database.


  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of Transact-SQL.
  • Working knowledge of relational databases.

Nastavni plan

1. Introduction to Database Development
  • Introduction to the SQL Server Platform
  • SQL Server Database Development Tasks
2. Designing and Implementing Tables
  • Designing Tables
  • Data Types
  • Working with Schemas
  • Creating and Altering Tables
3. Advanced Table Designs
  • Partitioning Data
  • Compressing Data
  • Temporal Tables
4. Ensuring Data Integrity through Constraints
  • Enforcing Data Integrity
  • Implementing Domain Integrity
  • Implementing Entity and Referential Integrity
5. Introduction to Indexes
  • Core Indexing Concepts
  • Data Types and Indexes
  • Heaps, Clustered, and Nonclustered Indexes
  • Single Column and Composite Indexes
6. Designing Optimized Index Strategies
  • Index Strategies
  • Managing Indexes
  • Execution Plans
  • The Database Engine Tuning Advisor
  • Query Store
7. Columnstore Indexes
  • Introduction to Columnstore Indexes
  • Creating Columnstore Indexes
  • Working Columnstore Indexes
8. Designing and Implementing Views
  • Introduction to Views
  • Creating and Managing Views
  • Performance Considerations for Views
9. Designing and Implementing Stored Procedures
  • Introduction to Stored Procedures
  • Working with Stored Procedures
  • Implementing Parameterized Stored Procedures
  • Controlling Execution Context
10. Designing and Implementing User-Defined Functions
  • Overview of Functions Designing and Implementing Scalar Functions
  • Designing and Implementing Table-Valued Functions
  • Alternatives to Functions
11. Responding to Data Manipulation via Triggers
  • Designing DML Triggers
  • Implementing DML Triggers
  • Advanced Trigger Concepts
12. Using In-Memory Tables
  • In-Memory Tables
  • Native Stored Procedures
13. Implementing Managed Code in SQL Server
  • Introduction to CLR Integration in SQL Server
  • Implementing and Publishing CLR Assemblies
14. Storing and Querying XML Data in SQL Server
  • 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
  • Shredding XML
15. Storing and Querying Spatial Data in SQL Server
  • Introduction to Spatial Data
  • Working with SQL Server Spatial Data Types
  • Using Spatial Data in Applications
16. Storing and Querying BLOBs and Text Documents in SQL Server
  • Considerations for BLOB Data
  • Working with FileStream
  • Using Full-Text Search
17. Performance and Monitoring
  • Concurrency and Transactions
  • Locking Internals
18. Performance and Monitoring
  • Extended Events
  • Working with extended Events
  • Live Query Statistics
  • Optimize Database File Configuration
  • Metrics
  • Lab 1: Designing and Implementing Tables
  • Lab 2: Using Advanced Table Designs
  • Lab 3: Using Data Integrity Through Constraints
  • Lab 4: Implementing Indexes
  • Lab 5: Optimizing Indexes
  • Lab 6: Using Columnstore Indexes
  • Lab 7: Designing and Implementing Views
  • Lab 8: Designing and Implementing Stored Procedures
  • Lab 9: Designing and Implementing User-Defined Functions
  • Lab 10: Responding to Data Manipulation by Using Triggers
  • Lab 11: Using In-Memory Database Capabilities
  • Lab 12: Implementing Managed Code in SQL Server
  • Lab 13: Storing and Querying XML Data in SQL Server
  • Lab 14: Working with SQL Server Spatial Data
  • Lab 15: Storing and Querying BLOBs and Text Documents in SQL Server
  • Lab 16: SQL Server Concurrency
  • Lab 17: Monitoring, Tracing, and Baselining