Description
This five-day, instructor-led course provides students with the knowledge and skills to develop a Microsoft SQL Server database. The course focuses on teaching individuals how to use the features and tools of SQL Server products related to database development.
Addressed to
- The primary audience for this course is IT professionals who want to gain an understanding of the features and technologies of SQL Server products to implement a database. The secondary audiences for this course are people who are developers of other product platforms who are looking to become experts in implementing a SQL Server database.
Duration
35 hours
Modalities
- Face-to-face
- Virtual live through digital platforms
- on site
Related Courses
- 20761-C: Querying Data with Transact-SQL
- 20764-C: Administering a SQL Database Infrastructure
It includes
- 35 hours of training
- Trained instructors
- Participant manual in digital format
- Proof of participation
Temary
Lesson 1: Introduction to Database Development
- Introduction to the SQL Server platform
- SQL Server Database Development Tasks
Lesson 2: Table Design and Implementation
- design tables
- Type of data
- Work with schematics
- Create and modify tables
Laboratory: Design and implementation of tables
- design tables
- Create schematics
- create tables
Lesson 3: Advanced Table Layouts
- data partition
- Compress data
- temporary tables
Lab : Using Advanced Table Layouts
- data partition
- Compress data
Lesson 4: Ensuring Data Integrity Using Constraints
- Enforce data integrity
- Implementing Data Domain Integrity
- Implementing entity and referential integrity
Lab: Using Data Integrity Through Constraints
- Add restrictions
- Try the restrictions
Lesson 5: Introduction to indexes
- Indexing Basics
- Data types and indexes
- Heaps, clustered, and nonclustered indices
- Composite and single column indexes
Lab: Implementing Indexes
- creating a lot
- Create a clustered index
- Create a covered index
Lesson 6: Design Optimized Index Strategies
- Index Strategies
- Index Management
- Execution plans
- The Database Engine Optimization Advisor
- Query Store
Lab: Index Optimization
- Using the Query Store
- Heaps and clustered indices
- Create a covered index
Lesson 7: Columnstore Indexes
- Introduction to columnstore indexes
- Creating columnstore indexes
- Work with columnstore indexes
Lab : Using Columnstore Indexes
- Create a columnstore index
- Create a memory-optimized columnstore table
Lesson 8: View Design and Implementation
- Introduction to views
- Create and manage views
- Performance considerations for views
Lab: View Design and Implementation
- Create standard views
- Create an updateable view
Lesson 9: Design and Implementation of Stored Procedures
- Introduction to stored procedures
- Work with stored procedures
- Implementing Parameterized Stored Procedures
- Control execution context
Laboratory: Design and implementation of stored procedures
- Create stored procedures
- Create parameterized stored procedures
- Changes in the execution context of the stored procedure
Lesson 10: Designing and Implementing User-Defined Functions
- Feature Overview
- Design and implementation of scalar functions
- Design and implementation of table-valued functions
- Considerations for Implementing Features
- Alternatives to functions
Lab: Design and Implementation of User-Defined Functions
- Format phone numbers
- Modify an existing role
Lesson 11: Responding to Data Manipulation Using Triggers
- Design of DML triggers
- Implementation of DML triggers
- Advanced trigger concepts
Lab : Responding to Data Manipulation Using Triggers
- Create and test the audit trigger
- Improve audit trigger
Lesson 12: Using tables in memory
- Memory-optimized tables
- Natively Compiled Stored Procedures
Lab: Using In-Memory Database Capabilities
- Use memory-optimized tables
- Using Natively Compiled Stored Procedures
Lesson 13: Implementing Managed Code in SQL Server
- Introduction to CLR integration in SQL Server
- Deploying and publishing CLR assemblies
Lab : Implementing Managed Code in SQL Server
- Evaluation of the proposed CLR code
- Creating a CLR function with scalar value
- Create a table-valued CLR function
Lesson 14: Storing and Querying XML Data in SQL Server
- Introduction to XML and XML Schemas
- Storing XML Schemas and Data in SQL Server
- Implementation of the XML data type
- Using the Transact-SQL FOR XML Statement
- Introduction to XQuery
- XML destruction
Lab : Storing and Querying XML Data in SQL Server
- Determine when to use XML
- Test storing XML data in variables
- Use XML schemas
- Using FOR XML queries
- Create a stored procedure to return XML
Lesson 15: Storing and querying spatial data in SQL Server
- Introduction to spatial data
- Work with spatial data types in SQL Server
- Use spatial data in applications
Lab : Working with SQL Server Spatial Data
- Get familiar with the geometry data type
- Add spatial data to an existing table
- Find nearby locations
Lesson 16: Storing and Querying BLOBs and 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
- Enabling and using FILESTREAM columns
- Enabling and using file tables
- Use a full-text index
Lesson 17: SQL Server Concurrency
- Concurrency and transactions
- intern blocking
Lab : SQL Server Concurrency
- Implement snapshot isolation
- Implement partition level locking
Lesson 18: Performance and Monitoring
- extended events
- Work with extended events
- Live query statistics
- Optimize database file settings
- Metrics
Laboratory: Monitoring, Tracking and Bases
- Data collection and analysis using extended events
- Implementation of the baseline methodology
