Home > Articles > Microsoft > MCSE

  • Print
  • + Share This
Exam Objectives

Exam Objectives

The exam objectives are broken up into eight different categories. The 70-459 exam measures your ability to accomplish the technical tasks listed below. The objectives for exam 70-459 as stated by Microsoft are as follows:

Implement Database Objects

  • Create and alter tables.
  • This objective may include but is not limited to: develop an optimal strategy for using temporary objects (table variables and temporary tables); manage a table without using triggers; data version control and management; create tables without using the built-in tools; understand the difference between @Table and #table

  • Design, implement, and troubleshoot security.
  • This objective may include but is not limited to: grant, deny, revoke; connection issues; execute as; certificates; loginless user; database roles and permissions; contained users; change permission chains

  • Create and modify constraints (complex statements).
  • This objective may include but is not limited to: create constraints on tables; define constraints; performance implications

Implement Programming Objects

  • Design and implement stored procedures.
  • This objective may include but is not limited to: create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of stored procedure results; create stored procedures for data access layer; analyze and rewrite procedures and processes; program stored procedures with T-SQL and CLR#; use table-valued parameters; encryption

  • Design T-SQL table-valued and scalar functions.
  • This objective may include but is not limited to: ensure code non-regression by keeping consistent signature for procedure, views, and function (interfaces); turn scripts that use cursors and loops into a SET-based operation

  • Create and alter views.
  • This objective may include but is not limited to: set up and configure partitioned tables and partitioned views; design for using views and stored procedures, and remove the direct usage of tables

Design Database Objects

  • Design tables.
  • This objective may include but is not limited to: 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; understand advantages and disadvantages of using a GUID as a clustered index; understand performance implications of # vs. @ temp tables and how to decide which to use, when, and why; use of set-based vs. row-based logic; encryption (other than TDE); table partitioning; filestream and filetable

  • Create and alter indexes.
  • This objective may include but is not limited to: 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

  • Design data integrity.
  • This objective may include but is not limited to: design table data integrity policy (checks, private key/foreign key, uniqueness, XML schema); select a primary key; data usage patterns

Optimize and Troubleshoot Queries

  • Optimize and tune queries.
  • This objective may include but is not limited to: tune a badly performing query; identify long running queries; review and optimize code; analyze execution plans to optimize queries; tune queries using execution plans and database tuning advisor (DTA); design advanced queries using pivots and 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; demonstrate use of recursive CTE; full text search; control execution plans

  • Troubleshoot and resolve performance problems.
  • This objective may include but is not limited to: interpret performance monitor data; impact of recovery modal on database size, and recovery; how to clean up if .MDF and .LDF files get to large; identify and fix transactional replication problems; detect and resolve server hung failure; identify and troubleshoot data access problems

  • Collect performance and system information.
  • This objective may include but is not limited to: use Data Management Views to determine performance issues; from system metadata; gather trace information by using the SQL Server Profiler; develop monitoring strategy for production database; run a profiler trace and analyze the results; use profiler to troubleshoot applications; collect output from the Database Engine Tuning Advisor; extended events

Design Database Structure

  • Design for business requirements.
  • This objective may include but is not limited to: business to data translations; identify which SQL Server components to use to support business requirements; design a normalization area; de-normalize by using SQL Server features (such as materialization via indexed views)

  • Design physical database and object placement.
  • This objective may include but is not limited to: filestream and filetable; logical vs. physical design; file groups

  • Design SQL Server instances.
  • This objective may include but is not limited to: create a specification for hardware for new instances; design an instance; design SQL to use only certain CPUs (including affinity masks); design clustered instances including Microsoft Distributed Transaction Control (MSDTC); memory allocation

Design Databases and Database Objects

  • Design a database model.
  • This objective may include but is not limited to: design a logical schema; design a normalized database; design data access and data layer architecture; understand the relational model; design a normalized data model; design a database schema; create and maintain a schema upgrade and downgrade script that include the most optimal schema deployment and data migration; Entity-Attribute-Value (EAV) modeling, generalization/specialization, star-schema; optimize the design for normalization to the right level for the application; design security architecture; understand impact of collation, ANSI NULLS, and QUOTED IDENTIFIER

  • Design tables.
  • This objective may include but is not limited to: 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; understand the performance implications of # vs. @ temp tables and how to decide which to use, when, and why; use of set-based rather than row-based logic; filestream and filetable; semantic engine; sequences; row/page compression; data type selection

  • Design T-SQL stored procedures.
  • This objective may include but is not limited to: write a stored procedure to meet a given set of requirements; design using views and stored procedures to remove the direct usage of tables

Design Database Security

  • Design an application strategy to support security.
  • This objective may include but is not limited to: design security; implement schemas and schema security; design maintenance, including SQL logins vs. integrated authentication, permissions, and mirroring issues; use appropriate mechanisms to enforce security roles and signed stored procedures; encryption; contained logins

  • Design instance-level security configurations.
  • This objective may include but is not limited to: implement separation of duties using different login roles; design and implement a data safety strategy that meets the requirements of the installation; choose authentication type, logon triggers, and regulatory requirements; transparent data encryption; DDL triggers

Design a Troubleshooting and Optimization Solution

  • Troubleshoot and resolve concurrency issues.
  • This objective may include but is not limited to: examine deadlocking issues using the SQL server logs; design the reporting database infrastructure; monitor issues via DMV; diagnose blocking, live locking, and deadlocking; diagnose waits; performance detection with built-in DMVs; know how concurrency affects performance

  • Design a monitoring solution at the instance level.
  • This objective may include but is not limited to: design auditing strategies including XE, Profiler, Perfmon, and DMV usage; set up file and table growth monitoring; collect performance indicators and counters; content management systems; policies

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.