Microsoft Exam 70-762 Developing SQL Databases

Skills measured

    Design and implement database objects
        Design and implement a relational database schema
            Design tables and schemas based on business requirements
                Lesson 1: Systematically Approaching Design Stages
            Improve the design of tables by using normalization
                Lesson 2: Designing a Normalized Database
            Write table create statements
                NULL vs NOT NULL
                Understand use of schemas
                Temporal Tables
                Create Table (non-azure)
                Create Table (azure)
            Determine the most efficient data types to use
                Understand differences between exact and approximate numeric fields
                    Data Types (Transact-SQL)
                Understand the differences between character string and Unicode character strings
                    Data Types (Transact-SQL)
                Understand hierarchyid data type
                Understand rowversion data type
                Understand table data type
                Understand xml data type
                Understand uniqueidentifier data type
        Design and implement indexes
            Clustered and Nonclustered Indexes described
            Design new indexes based on provided tables, queries, or plans
                SQL Server Index Design Guide
                    Filtered indexes
            Distinguish between indexed columns and included columns
                Create Indexes with Included Columns
            Implement clustered index columns by using best practices
                Create Clustered Indexes
            Recommend new indexes based on query plans
        Design and implement views
            Design a view structure to select data based on user or business requirements
                CREATE VIEW (Transact-SQL)
            Identify the steps necessary to design an updateable view
                Updatable Views
            Implement partitioned views
                Using Partitioned Views
            Implement indexed views
                Create Indexed Views
        Implement columnstore indexes
            Columnstore indexes – Design Guidance
            Determine use cases that support the use of columnstore indexes
                Columnstore Indexes Guide
                    Very high level of compression which reduces storage costs
                    Improved performance
                    Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads
                    Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload
                Columnstore Index: Differences between Clustered/Nonclustered Columnstore Index
            Identify proper usage of clustered and non-clustered columnstore indexes
                Columnstore Indexes for Data Warehousing
            Design standard non-clustered indexes in conjunction with clustered columnstore indexes
                Columnstore Indexes for Data Warehousing
            Implement columnstore index maintenance
                SQL 2016: Columnstore row group Merge policy and index maintenance improvements
    Implement programmability objects
        Ensure data integrity with constraints
            Unique Constraints and Check Constraints
            Define table and foreign key constraints to enforce business rules
                    Allowing Null Values
                    CHECK Constraints
                    UNIQUE Constraints
                    PRIMARY KEY Constraints
                    FOREIGN KEY Constraints
            Write Transact-SQL statements to add constraints to tables
                column_constraint (Transact-SQL)
                table_constraint (Transact-SQL)
            Identify results of Data Manipulation Language (DML) statements given existing tables and constraints
                Data Manipulation Language (DML) Statements (Transact-SQL)
            Identify proper usage of PRIMARY KEY constraints
                Primary Key Constraints
        Create stored procedures
            Create Views and Stored Procedures
            Design stored procedure components and structure based on business requirements
                CREATE PROCEDURE (Transact-SQL)
            Implement input and output parameters
                Passing Parameters
            Implement table-valued parameters, implement return codes
                Use Table-Valued Parameters (Database Engine)
            Implement return codes
                RETURN (Transact-SQL)
            Streamline existing stored procedure logic
            Implement error handling and transaction control logic within stored procedures
                TRY…CATCH (Transact-SQL)
                @ERROR (Transact-SQL). Not recommended for new projects
                BEGIN TRANSACTION (Transact-SQL)
                COMMIT TRANSACTION (Transact-SQL)
                ROLLBACK TRANSACTION (Transact-SQL)
                SAVE TRANSACTION (Transact-SQL)
        Create triggers and user-defined functions
            DDL Triggers
            Design trigger logic based on business requirements
                CREATE TRIGGER (Transact-SQL)
            Determine when to use Data Manipulation Language (DML) triggers, data definition (DDL) triggers or logon triggers
                DDL Triggers
                DML Triggers
                Logon Triggers
            Recognize results based on execution of AFTER or INSTEAD OF triggers
                Types of DML Triggers
            Design scalar-valued and table-valued user-defined functions based on business requirements
                Create User-defined Functions (Database Engine)
            Identify differences between deterministic and non-deterministic functions
                Deterministic and Nondeterministic Functions
    Manage database concurrency
        Implement transactions
            Identify DML statement results based on transaction behavior
            Recognize differences between and identify usage of explicit and implicit transactions
                Transaction Statements (Transact-SQL)
            Implement savepoints within transactions
                SAVE TRANSACTION (Transact-SQL)
            Determine the role of transactions in high-concurrency databases
                SQL Server Transaction Locking and Row Versioning Guide
        Manage isolation levels
            Identify differences between Read Uncommitted, Read Committed, Repeatable Read, Serializable, and Snapshot isolation levels
            Define results of concurrent queries based on isolation level
                SQL Server Isolation Levels By Example
            Identify the resource and performance impact of given isolation levels
                READ UNCOMMITTED – Fast as locking contention is minimised. Potential to read uncommitted transactions, dirty reads, that may be rolled back.
                READ COMMITTED – Can be blocked, and can block other transactions, hence can be slower than READ UNCOMMITTED. Dirty Reads are prevented, and so the data integrity is improved.
                REPEATABLE READ – As READ COMMITTED but more restrictive in that it prevents rows read from being updated until the current transaction is committed, so likely to increase blocking.
                SNAPSHOT – Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
                SERIALIZABLE – As REPEATABLE READ but more restrictive in that it prevents rows read from being inserted that cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes, so likely to increase blocking.
        Optimize concurrency and locking behavior
            Troubleshoot locking issues
                SQL Server Transaction Locking and Row Versioning Guide
            Identify lock escalation behaviors
                Lock Escalation
            Capture and analyze deadlock graphs
                Analyze Deadlocks with SQL Server Profiler
            Identify ways to remediate deadlocks
                Minimizing Deadlocks
        Implement memory-optimized tables and native stored procedures
            Introduction to Memory-Optimized Tables
            Define use cases for memory-optimized tables versus traditional disk-based tables
                In-Memory OLTP (In-Memory Optimization)
            Optimize performance of in-memory tables by changing durability settings
                Introduction to Memory-Optimized Tables
            Determine best case usage scenarios for natively compiled stored procedures
                Best Practices for Calling Natively Compiled Stored Procedures
            Enable collection of execution statistics for natively compiled stored procedures.
                Monitoring Performance of Natively Compiled Stored Procedures
    Optimize database objects and SQL infrastructure
        Optimize statistics and indexes
            Determine the accuracy of statistics and the associated impact to query plans and performance
                Managing SQL Server Statistics
            Design statistics maintenance tasks
                Database Properties (Options Page) – Automatic
                Auto Create Statistics
                Auto Update Statistics
                UPDATE STATISTICS (Transact-SQL)
                SQL Server Statistics: Maintenance and Best Practices
            Use dynamic management objects to review current index usage and identify missing indexes
                sys.dm_db_index_usage_stats (Transact-SQL) – Current index usage
                Finding Missing Indexes(Transact-SQL)
            Consolidate overlapping indexes
                An Approach to SQL Server Index Tuning
        Analyze and troubleshoot query plans
            Best Practice with the Query Store
            Capture query plans using extended events and traces
                How do I obtain a Query Execution Plan?
                Extracting ShowPlan XML from SQL Server Extended Events
            Identify poorly performing query plan operators
                Ten Common Threats to Execution Plan Quality
            Create efficient query plans using Query Store
                Monitoring Performance By Using the Query Store
            Compare estimated and actual query plans and related metadata
                Execution Plan Basics
            Configure Azure SQL Database Performance Insight
                Azure SQL Database Query Performance Insight
        Manage performance for database instances
            Performance Center for SQL Server Database Engine and Azure SQL Database
            Manage database workload in SQL Server
                Resource Governor
            Design and implement Elastic Scale for Azure SQL Database
                Scaling out with Azure SQL Database
            Select an appropriate service tier or edition
                SQL Server 2016 SP1 editions
                SQL Database options and performance: Understand what’s available in each service tier
            Optimize database file and tempdb configuration
                Using Files and Filegroups
                FileTables (SQL Server)
                TEMPDB Enhancements in SQL Server 2016
                    Installs a file for each logical process core up to a maxumum of 8. This helps reduce contention.
                    Enables recommended trace flags automatically
                        Trace Flag 1118 reduces Shared Global Allocation Map (SGAM) contention.
                        Trace Flag 1117 strictly forces all data files within the filegroup to grow at the same time.
            Optimize memory configuration
                Server Memory Server Configuration Options
            Monitor and diagnose scheduling and wait statistics using dynamic management objects
                SQL Server Performance Tuning Using Wait Statistics: A Beginner’s Guide
            Troubleshoot and analyze storage, IO, and cache issues
                A DBA guide to SQL Server performance troubleshooting – Part 1 – Problems and performance metrics
            Monitor Azure SQL Database query plans
                Monitoring Azure SQL Database using dynamic management views
                Monitoring Performance By Using the Query Store
        Monitor and trace SQL Server baseline performance metrics
            Monitor operating system and SQL Server performance metrics
                The Accidental DBA (Day 21 of 30): Essential PerfMon counters
            Compare baseline metrics to observed metrics while troubleshooting performance issues
                Monitoring Performance By Using the Query Store
                Since the query store retains multiple execution plans per query, it can enforce policies to direct the query processor to use a specific execution plan for a query.Common scenarios for using the Query Store feature are:

                    Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
                    Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
                    Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.
                    Audit the history of query plans for a given query.
                    Analyze the resource (CPU, I/O, and Memory) usage patterns for a particular database.

                The query store contains two stores; a plan store for persisting the execution plan information, and a runtime stats store for persisting the execution statistics information.

                Available in all SQL editions including localdb.
                The SQL Server 2016 Query Store: Overview and Architecture
            Identify differences between performance monitoring and logging tools, such as perfmon and dynamic management objects
            Monitor Azure SQL Database performance; determine best practice use cases for extended events
                Monitoring database performance in Azure SQL Database
                Monitoring Azure SQL Database using dynamic management views
            Determine best practice use cases for extended events
                Extended Events
            Distinguish between Extended Events targets
                Migrating from SQL Trace to Extended Events
                Comparing Extended Events vs SQL Trace – or why SQL Trace & Profiler are just a thing of the past 🙂
            Compare the impact of Extended Events and SQL Trace
                Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load
            Define differences between Extended Events Packages, Targets, Actions, and Sessions
                SQL Server Extended Events Packages
                Targets for Extended Events in SQL ServerAn item that receives the output data from a captured event. The target displays the data to you.
                    etw_classic_sync_target – inter-operate with Event Tracing for Windows (ETW) to monitor system activity
                    event_counter – counts how many times each specified event occurs
                    event_file – disk file
                    histogram – bucketizer
                    pair_matching – enables you to detect start events that occurs without a corresponding end event
                    ring buffer – FIFO or FIFO per event
                View the Extended Events Equivalents to SQL Trace Event Classes
                SQL Server Extended Events Sessions