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 people how to use SQL Server product features and tools related to database development.
Aimed at
- The primary audience for this course is IT professionals who want to gain knowledge about 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 looking to become experts in implementing a SQL Server database.
Duration
35 hours
Modalities
- On-site
- Virtual live through digital platforms
- On site
Includes
- 35 hours of training
- Trained instructors
- Participant's manual in digital format
- Proof of participation
Agenda
- Introduction to the SQL Server platform
- SQL Server Database Development Tasks
- Designing tables
- Types of data
- Working with diagrams
- Create and modify tables
- Designing tables
- Create diagrams
- Create tables
- Data partitioning
- Compress data
- Temporary tables
- Data partitioning
- Compress data
- Enforce data integrity
- Data Domain Integrity Implementation
- Implementing entity and referential integrity
- Add restrictions
- Test the restrictions
- Indexing basics
- Data types and indexes
- Heaps, clustered and non-clustered indexes
- Composite and single column indexes
- Creating a lot
- Create a clustered index
- Create a covered index
- Index strategies
- Index management
- Execution plans
- The Database Engine Optimization Advisor
- Consultation Warehouse
- Using Query Store
- Clustered stacks and indexes
- Create a covered index
- Introduction to column store indexes
- Creation of column store indexes
- Working with column store indexes
- Creating a column store index
- Create a memory-optimized columnar storage table
- Introduction to views
- Create and manage views
- Performance considerations for views
- Create standard views
- Create an updatable view
- Introduction to stored procedures
- Working with stored procedures
- Implementation of parameterized stored procedures
- Control the execution context
- Create stored procedures
- Create parameterized stored procedures
- Changes in the stored procedure execution context
- Summary of functions
- Design and implementation of scalar functions
- Design and implementation of functions with table values
- Considerations for implementing functions
- Alternatives to functions
- Formatting phone numbers
- Modify an existing function
- Design of DML activators
- Implementation of DML triggers
- Advanced trigger concepts
- Create and test the audit trigger
- Improve the audit trigger
- Memory optimized tables
- Natively compiled stored procedures
- Use memory-optimized tables
- Use of natively compiled stored procedures
- Introduction to CLR integration in SQL Server
- Implementation and publication of CLR assemblies
- Evaluation of the proposed CLR code
- Creating a CLR function with scalar value
- Creating a CLR function with table values
- Introduction to XML and XML Schemas
- XML schema and XML data storage in SQL Server
- XML data type implementation
- Using the Transact-SQL FOR XML statement
- Introduction to XQuery
- XML Destruction
- Determine when to use XML
- Testing of XML data storage in variables
- Using XML schemas
- Use of 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
- Familiarize yourself with the geometry data type
- Add spatial data to an existing table
- Search for nearby locations
- Considerations for BLOB data
- Working with FILESTREAM
- Using full-text search
- Enabling and using FILESTREAM columns
- Enabling and using file tables
- Using a full-text index
- Simultaneity and transactions
- Blocking of interns
- Implement snapshot isolation
- Implement partition level locking
- Extended events
- Working with extended events
- Live query statistics
- Optimizing the database file configuration
- Metrics
- Data collection and analysis using extended events
- Implementation of the baseline methodology
