Тест №70-464: Developing Microsoft SQL Server 2012 Databases
Продолжительность: Языки теста: English Online тест: Кол-во вопросов: Мин.проходной балл:
Skills Being MeasuredThis
exam measures your ability to accomplish the technical tasks listed
indicate the relative weight of each major topic area on the exam.The
higher the percentage, the more questions you are likely to see on that
content area on the exam.
The information after “This objective
may include but is not limited to” is intended to further define or
scope the objective by describing the types of skills and topics that
may be tested for the objective. However, it is not an exhaustive list
of skills and topics that could be included on the exam for a given
skill area. You may be tested on other skills and topics related to the
objective that are not explicitly listed here.
Implement Database Objects (31%)
Create and alter tables (complex statements).
may include but is not limited to: 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
Design, implement, and troubleshoot security.
may include but is not limited to: grant, deny, revoke;
unable to connect; execute as; certificates; loginless user;
database roles and permissions; contained users; change
Design the locking granularity level.
may include but is not limited to: 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 the data
design and improve
may include but is not limited to: 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
Implement data types.
may include but is not limited to: 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).
may include but is not limited to: create constraints on
tables; define constraints; performance implications
Work with XML Data.
may include but is not limited to: 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
Implement Programming Objects (21%)
Write automation scripts.
may include but is not limited to: automate backup testing;
shrink file; check index fragmentation; archive data; run an
SQL Server Integration Services (SSIS) job; check disk
space; automate backups
Design and implement stored procedures.
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 procedure for data access layer;
analyze and rewrite procedures and processes; program stored
procedures, with T-SQL and CLR#; use table valued
Design T-SQL table-valued and scalar functions.
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, use, and alter user-defined functions (UDFs).
may include but is not limited to: understand deterministic,
non-deterministic functions; using cross apply with UDFs;
Common Language Runtime (CLR)
Create and alter views (complex statements).
may include but is not limited to: set up and configure
partitioned tables and partitioned views; design a best
practice for using views and stored procedures and remove
the direct usage of tables
Design Database Objects (24%)
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; 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 for concurrency.
may include but is not limited to: develop a strategy to
minimize concurrency; handle concurrency to minimize locking
and eliminate as much blocking as possible, and to avoid
deadlocks; manage the transactions to limit the time to hold
lock and have fast transactions (maximize concurrency);
define locking and concurrency strategy; impact of read
committed snapshot / snapshot isolation; understand what it
solves and what it costs
Create and alter indexes.
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.
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
Design for implicit and explicit transactions.
may include but is not limited to: manage transactions; use
transactions in code; ensure data integrity by using
transactions; use transactions inside the database using
T-SQL and from the "outside" via C#/VB; distributed
Optimize and Troubleshoot Queries (24%)
Optimize and tune queries.
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 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
Troubleshoot and resolve performance problems.
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
Optimize indexing strategies.
may include but is not limited to: 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
Capture and analyze execution plans.
may include but is not limited to: collect and read
execution plan; review an execution plan to spot potential
performance issues; read an execution plan; create an index
based on an execution plan; row-based logic versus.
set-based logic, batching, splitting implicit transactions
Collect performance and system information.
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; run profiler for
troubleshooting application; collect output from the
Database Engine Tuning Advisor; extended events