Contents
Free ebooks from Microsoft Press
Quick access to online references
Errata, updates, & book support
Chapter 1 Design and implement database objects
Skill 1.1: Design and implement a relational database schema
Designing tables and schemas based on business requirements
Improving the design of tables by using normalization
Writing table create statements
Determining the most efficient data types to use
Skill 1.2: Design and implement indexes
Design new indexes based on provided tables, queries, or plans
Distinguish between indexed columns and included columns
Implement clustered index columns by using best practices
Recommend new indexes based on query plans
Skill 1.3: Design and implement views
Design a view structure to select data based on user or business requirements
Identify the steps necessary to design an updateable view
Skill 1.4: Implement columnstore indexes
Determine use cases that support the use of columnstore indexes
Identify proper usage of clustered and non-clustered columnstore indexes
Design standard non-clustered indexes in conjunction with clustered columnstore indexes
Implement columnstore index maintenance
Chapter 2 Implement programmability objects
Skill 2.1 Ensure data integrity with constraints
Define table and foreign-key constraints to enforce business rules
Write Transact-SQL statements to add constraints to tables
Identify proper usage of PRIMARY KEY constraints
Skill 2.2 Create stored procedures
Design stored procedure components and structure based on business requirements
Implement input and output parameters
Implement table-valued parameters
Streamline existing stored procedure logic
Implement error handling and transaction control logic within stored procedures
Skill 2.3 Create triggers and user-defined functions
Design trigger logic based on business requirements
Recognize results based on execution of AFTER or INSTEAD OF triggers
Design scalar-valued and table-valued user-defined functions based on business requirements
Identify differences between deterministic and non-deterministic functions
Chapter 3 Manage database concurrency
Skill 3.1: Implement transactions
Identify DML statement results based on transaction behavior
Recognize differences between and identify usage of explicit and implicit transactions
Implement savepoints within transactions
Determine the role of transactions in high-concurrency databases
Skill 3.2: Manage isolation levels
Identify differences between isolation levels
Define results of concurrent queries based on isolation level
Identify the resource and performance impact of given isolation levels
Skill 3.3: Optimize concurrency and locking behavior
Identify lock escalation behaviors
Capture and analyze deadlock graphs
Identify ways to remediate deadlocks
Skill 3.4: Implement memory-optimized tables and native stored procedures
Define use cases for memory-optimized tables
Optimize performance of in-memory tables
Determine best case usage scenarios for natively compiled stored procedures
Enable collection of execution statistics for natively compiled stored procedures
Chapter 4 Optimize database objects and SQL infrastructure
Skill 4.1: Optimize statistics and indexes
Determine the accuracy of statistics and the associated impact to query plans and performance
Design statistics maintenance tasks
Use dynamic management objects to review current index usage and identify missing indexes
Consolidate overlapping indexes
Skill 4.2: Analyze and troubleshoot query plans
Capture query plans using extended events and traces
Identify poorly performing query plan operators
Compare estimated and actual query plans and related metadata
Configure Azure SQL Database Performance Insight
Skill 4.3: Manage performance for database instances
Manage database workload in SQL Server
Design and implement Elastic Scale for Azure SQL Database
Select an appropriate service tier or edition
Optimize database file and tempdb configuration
Monitor and diagnose schedule and wait statistics using dynamic management objects
Troubleshoot and analyze storage, IO, and cache issues
Monitor Azure SQL Database query plans
Skill 4.4: Monitor and trace SQL Server baseline performance metrics
Monitor operating system and SQL Server performance metrics
Compare baseline metrics to observed metrics while troubleshooting performance issues
Identify differences between performance monitoring and logging tools
Monitor Azure SQL Database performance
Determine best practice use cases for extended events
Distinguish between Extended Events targets
Compare the impact of Extended Events and SQL Trace
Define differences between Extended Events Packages, Targets, Actions, and Sessions
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning resources for you. To participate in a brief online survey, please visit: