- Published 1/17/2017
- 1st Edition
Prepare for Microsoft Exam 70-762, Developing SQL Databases –and help demonstrate your real-world mastery of skills for building and implementing databases across organizations. Designed for database professionals who build and implement databases across organizations and who ensure high levels of data availability, Exam Ref focuses on the critical-thinking and decision-making acumen needed for success at the MCSA level.
Focus on the expertise measured by these objectives:
• Design and implement database objects
• Implement programmability objects
• Manage database concurrency
• Optimize database objects and SQL infrastructure
This Microsoft Exam Ref:
• Organizes its coverage by exam objectives
• Features strategic, what-if scenarios to challenge you
• Assumes you have working knowledge of Microsoft Windows, Transact-SQL, and relational databases
About the Exam
Exam 70-762 focuses on skills and knowledge for building and implementing databases across organizations and ensuring high levels of data availability.
About Microsoft Certification
Passing this exam earns you credit toward a Microsoft Certified Solutions Associate
(MCSA) certification that demonstrates your mastery of modern database
development. Exam 70-761 (Querying Data with Transact-SQL) is also required for MCSA: SQL 2016 Database Development. See full details at: microsoft.com/learning
Table of Contents
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
Implement partitioned views
Implement indexed views
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 results of Data Manipulation Language (DML) statements given existing tables and constraints
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
Implement return codes
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
Determine when to use Data Manipulation Language (DML) triggers, Data Definition Language (DDL) triggers, or logon triggers
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
Troubleshoot locking issues
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 242
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
Optimize memory 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