Продолжительность: Языки теста: English Online тест: Кол-во вопросов: Мин.проходной балл:
Темы:
Skills Being
MeasuredThis
exam measures your ability to accomplish the technical tasks
listed below.The
percentages 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.
Create Database Objects (24%)
Create and alter tables using T-SQL syntax
(simple statements).
This objective may include but is not limited
to: create tables without using the built-in
tools; ALTER; DROP; ALTER COLUMN; CREATE
Create
and alter views (simple statements).
This objective may include but is not limited
to: create indexed views; create views without
using the built-in tools; CREATE, ALTER, DROP
Design
views.
This objective may include but is not limited
to: ensure code non regression by keeping
consistent signature for procedure, views, and
function (interfaces); security implications
Create and modify constraints (simple
statements).
This objective may include but is not limited
to: create constraints on tables; define
constraints; unique constraints; default
constraints; primary and foreign key constraints
Create and alter DML triggers.
This objective may include but is not limited
to: inserted and deleted tables; nested
triggers; types of triggers; update functions;
handle multiple rows in a session; performance
implications of triggers
Work with Data (27%)
Query data by using SELECT statements.
This objective may include but is not limited
to: use the ranking function to select top(X)
rows for multiple categories in a single query;
write and perform queries efficiently using the
new code items such as synonyms and joins
(except, intersect); implement logic which uses
dynamic SQL and system metadata; write
efficient, technically complex SQL queries,
including all types of joins versus the use of
derived tables; determine what code may or may
not execute based on the tables provided; given
a table with constraints, determine which
statement set would load a table; use and
understand different data access technologies;
CASE versus ISNULL versus COALESCE
Implement sub-queries.
This objective may include but is not limited
to: identify problematic elements in query
plans; pivot and unpivot; apply operator; cte
statement; with statement
Implement data types.
This objective may include but is not limited
to: use appropriate data; understand the uses
and limitations of each data type; impact of
GUID (newid, newsequentialid) on database
performance, when to use which data type for
columns
Implement aggregate queries.
This objective may include but is not limited
to: new analytic functions; grouping sets;
spatial aggregates; apply ranking functions
Query and manage XML data.
This objective may include but is not limited
to: understand XML datatypes and their schemas
and interoperability with limitations and
restrictions; implement XML schemas and handling
of XML data; how to handle XML data in SQL
Server and when and when not to use it,
including XML namespaces; import and export XML;
XML indexing
Modify Data (24%)
Create and alter stored procedures (simple
statements).
This objective may include but is not limited
to: write a stored procedure to meet a given set
of requirements; branching logic; create stored
procedures and other programmatic objects;
techniques for developing stored procedures;
different types of stored procedure results;
create a stored procedure for data access layer;
program stored procedures, triggers, and
functions with T-SQL
Modify data by using INSERT, UPDATE, and DELETE
statements.
This objective may include but is not limited
to: given a set of code with defaults,
constraints, and triggers, determine the output
of a set of DDL; know which SQL statements are
best to solve common requirements; use output
statement
Combine datasets.
This objective may include but is not limited
to: difference between UNION and UNION all; case
vs. isnull vs. coalesce; modify data by using
MERGE statements
Work with functions.
This objective may include but is not limited
to: understand deterministic and
non-deterministic functions; scalar and table
values; apply built-in scalar functions; create
and alter user-defined functions (UDFs)
Troubleshoot and Optimize Queries (25%)
Optimize queries.
This objective may include but is not limited
to: understand statistics; read query plans;
plan guides; DMVs; hints; statistics IO; dynamic
vs. parameterized queries; describe the
different join types (HASH, MERGE, LOOP) and
describe the scenarios in which they would be
used
Manage transactions.
This objective may include but is not limited
to: mark a transaction; understand begin tran,
commit, and rollback; implicit vs. explicit
transactions; isolation levels; scope and type
of locks; trancount
Evaluate the use of row-based operations vs.
set-based operations.
This objective may include but is not limited
to: when to use cursors; impact of scalar UDFs;
combine multiple DML operations
Implement error handling.
This objective may include but is not limited
to: implement try/catch/throw; use set based
rather than row based logic; transaction
management
О нас
