This is a full list from the Microsoft website integrated with their published April 2014 update.
1. Implement database objects (30-35%)
Create and alter tables (complex statements)
- Develop an optimal strategy for using temporary objects (table variables and temporary tables)
- how not to rely on triggers solely as a means to manage a table
- data version control and management
- create tables without using the built-in tools
- understand the difference between @Table and #table
- create calculated columns
- implement partitioned tables, schemas, and functions
- implement column collation
- implement in-memory OLTP
Design, implement, and troubleshoot security
- Grant, deny, revoke
- unable to connect
- execute as
- certificates
- loginless user
- database roles and permissions
- contained users
- change permission chains
- implement cross db ownership chaining
- implement schema security
- implement server roles
- review effective permissions
- troubleshoot and repair orphaned users
Design the locking granularity level
- Choose the right lock mechanism for a given task, handling and/or avoiding deadlocks
- fix locking and blocking issues caused by previous development or third-party apps
- analyze a deadlock scenario to alleviate the issue
- impact of isolation level and ado defaults
- impact of locks and lock escalation
- reduce locking scenarios
- how isolation levels affect blocking and locking
- identify bottlenecks in, and improve, the data design
- design index locking properties
- design transactions that minimize locking
- design appropriate concurrency control, such as
- pessimistic or optimistic
Maintain indexes
- Inspect physical characteristics of indexes and perform index maintenance
- identify fragmented indexes
- identify unused indexes
- implement indexes
- defrag/rebuild indexes
- set up a maintenance strategy for indexes and statistics
- optimize indexes (full, filter index)
- statistics (full, filter) force or fix queue
- when to rebuild versus reorg and index
- create a tuning and maintenance strategy for proactive operations
- align indexes on partitioned tables
- inspect indexes by using dynamic management objects
Implement data types
- Use appropriate data types
- develop a CLR data type
- understand the difference between @Table and #table
- impact of GUID (newid, newsequentialid) on database performance, indexing and privacy
- use spatial data
- LOB data types
- understand when and how to use column store and sparse columns
- implicit and explicit conversions, integer math
Create and modify constraints (complex statements)
- Create constraints on tables
- define constraints
- performance implications
- implement cascading deletes
- configure constraints for bulk inserts
Work with XML data
- Implement XML
- use XML (Query, Input, Output)
- transform XML data into relational data
- retrieve relational data as XML
- FOR XML
- design a strategy to transform XML into relational data
- design a strategy to query and modify XML data
- understand XML data types and their schemas and interoperability, limitations, and restrictions
- implement XML schemas and handling of XML data
- how to handle it in SQL Server and when and when not to use it, including XML namespaces
- import and export XML
- return tables from XML data types using XQuery
- implement XML selective indexes
2. Implement programming objects (20-25%)
Write automation scripts
- Automate backup testing
- shrink file
- check index fragmentation
- archive data
- run an SQL Server Integration Services (SSIS) job
- check disk space
- Write scripts that automate backups, including backup to Windows Azure Blob Storage Service
Design and implement stored procedures
- Create stored procedures and other programmatic objects
- techniques for developing stored procedures
- different types of stored procedure results
- create stored procedure for data access layer
- analyze and rewrite procedures and processes
- program stored procedures, with T-SQL and CLR#
- use table valued parameters
- encryption
- implement error handling, including TRY…CATCH
- configure appropriate connection settings
- design appropriate query paging, including OFFSET and FETCH
Design T-SQL table-valued and scalar functions
- modify scripts that use cursors and loops into a SET- based operation
- design deterministic and non-deterministic functions
Create, use, and alter user-defined functions (UDFs)
- Understand deterministic, non-deterministic functions
- use cross apply with UDFs
- Common Language Runtime (CLR)
Create and alter views (complex statements)
- set up and configure partitioned tables and partitioned views
- create indexed views
3. Design database objects (20-25%)
Design tables
- Data design patterns
- develop normalized and de-normalized SQL tables
- understand the difference between physical tables, temp tables, temp table variables, and common table expressions
- design transactions
- design views
- describe advantages / disadvantages of using a GUID as a clustered index
- understand performance implications of # versus @ temp tables and how to decide which to use, when, and why
- use of set-based rather than row- based logic
- encryption (other than TDE)
- table partitioning
- filestream and filetable
- design tables for In-Memory OLTP
Design for concurrency
- develop a strategy to maximize concurrency
- define a locking and concurrency strategy
- design a transaction isolation strategy, including server database and session
- design triggers for concurrency
Create and alter indexes
- Create indexes and data structures
- create filtered indexes
- create an indexing strategy
- design and optimize indexes
- design indexes and statistics
- assess which indexes on a table are likely to be used given different search arguments (SARG)
- column store indexes
- semantic indexes
- create spatial indexes
Design data integrity
- Design table data integrity policy (checks, private key/foreign key, uniqueness, XML schema)
- select a primary key
- data usage patterns
- design a table data integrity policy, including nullability
Design for implicit and explicit transactions
- manage transactions
- ensure data integrity by using transactions
- manage distributed transaction escalations
- design savepoints
- design error handling for transactions, including TRY, CATCH, and THROW
4. Optimize and troubleshoot queries (20-25%)
Optimize and tune queries
- Tune a badly performing query
- identify long running queries
- review and optimize code
- analyze execution plans to optimize queries
- tune a query that is poorly written
- tune queries using execution plans and database tuning advisor (DTA)
- design advanced queries: pivots, utilizing common table expressions (CTE), design the database layout and optimize queries (for speed and/or data size)
- understand different data types
- basic knowledge of query hints
- tune query workloads, using realistic data sets not being production data sets
- demonstrate use of recursive CTE
- full text search
- control execution plans
- implement semantic search
- implement plan guides
Troubleshoot and resolve performance problems- Interpret performance monitor data
- impact of recovery modal on database size, and recovery
- how to clean up if .MDF and .LDF files get too large
- identify and fix transactional replication problems
- detect and resolve server hung, failure
- identify and troubleshoot data access problems
- integrate performance monitor data with SQL Traces
- manage tempdb contention and auto growth
- implement Resource Governor
- monitor and resolve In-Memory OLTP issues, including merge and garbage collection
Optimize indexing strategies- Develop optimal strategy for clustered indexes
- analyze index usage
- know the difference between the type of indexes and when to choose one over the other
- optimize indexing for data warehousing vs. optimize indexing for Online Transaction Processing (OLTP)
- generate appropriate indexes and statistics with include columns
- apply effective and efficient indexes, including the use of INCLUDE lists
- full-text indexing
- create filtered indexes
- implement columnstore indexes
- optimize online index maintenance
Capture and analyze execution plans- collect and read execution plans
- create an index based on an execution plan
- batch or split implicit transactions
- split large queries
- consolidate smaller queries
- review and optimize parallel plans
Collect performance and system information- monitor performance using Dynamic Management Views
- collect output from the Database Engine Tuning Advisory
- design Extended Events Sessions
- review and interpret Extended Event logs
- optimize Extended Event session settings
- use Activity Monitor to minimize server impact and determine IO bottlenecks
- monitor In-Memory OLTP resources