- Published 10/5/2017
- 1st Edition
About the Book:
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
Microsoft Exam Ref publications stand apart from third-party study guides because they:
- Provide guidance from Microsoft, the creator of Microsoft certification exams
- Target IT professional-level exam candidates with content focused on their needs, not "one-size-fits-all content
- Streamline study by organizing material according to the exam’s objective domain (OD), covering one functional group and its objectives in each chapter
- Feature Thought Experiments to guide candidates through a set of "what if?" scenarios, and prepare them more effectively for Pro-level style exam questions
- Explore big picture thinking around the planning and design aspects of the IT pro’s job role
About the Practice Test:The 70-762 practice test is designed to help candidates prepare for and pass Microsoft's 70-762 exam.
Qualified candidates should be database professionals who build and implement high-availability databases across organizations. Candidates should be able to design and implement database objects, such as the schema, indexes, and views. Candidates should also be able to ensure data consistency with constraints and create stored procedures, triggers, and user-defined functions. A solid understanding of how to manage database concurrency is also required. Candidates should also be able to optimize database objects and the SQL infrastructure.
Certification: This exam counts as credit toward the following certifications:
MCSA, MCSE
This test contains 153 questions and covers the following objectives:
- Design and implement database objects - 39
- Implement programmability objects - 33
- Manage database concurrency - 39
- Optimize database objects and SQL infrastructure - 42
This test includes references to the following media:
Why Choose the Microsoft Official Practice Test powered by MeasureUp?Quality test content is extremely important to us so that you will be prepared on exam day. We ensure that all objectives of the exam are covered in depth so you'll be ready for any question on the exam. Our practice tests are written by industry experts in the subject matter. They work closely with certification providers to understand the exam objectives, participate in beta testing and take the exam themselves before creating new practice tests. Our quality content and innovative technology have earned the prestigious credential of Microsoft Certified Practice Test Provider.
- Online performance-based simulations give hands on work environment experience
- Questions are similar to exam questions so you test your knowledge of exam objectives
- Detailed explanations for both correct and distractor answers reinforce the material
- Study Mode covers all objectives ensuring topics are covered
- Certification Mode (timed) prepares students for exam taking conditions
- Instant, drill-down score reports tell you exactly the areas to focus on.
How to obtain the Practice Test: The Official Microsoft Practice Test offered with this book is fulfilled by MeasureUp.com. With your order, you will receive a product key on your Account Page. Registering the product key at MeasureUp.com will give you access to a downloadable practice test which does not expire as well as 30-day access to the online version. Before accessing your practice test, you will be required to create an account at MeasureUp.com if you don’t already have one. The downloadable practice test is licensed by MeasureUp for a single user and can be downloaded on up to three different devices. Downloading practice tests to virtual machines is prohibited.
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