Preparing for Exam 70-464: Developing Microsoft SQL Server 2012 Databases

This post is intended to help candidates prepare for Exam 70-464: Developing Microsoft SQL Server 2012 Databases. It features links to a number of online resources such as

  • Online References
  • Study Guides
  • Labs and Tutorials
  • Printed Material
  • Practice Tests

Exam 70-464 – Skills Measured

1. Objective: Implement Database Objects (31%)

a. Create and alter tables (complex statements).

This objective 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

b. Design, implement, and troubleshoot security.

This objective 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 permission chains

c. Design the locking granularity level.

This objective 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; analyse 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

d. Maintain indexes.

This objective 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 operations

e. Implement data types.

This objective 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

f. Create and modify constraints (complex statements).

This objective may include but is not limited to: create constraints on tables; define constraints; performance implications

g. Work with XML Data.

This objective 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

2. Implement Programming Objects (21%)

a. Write automation scripts.

This objective 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

b. 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 procedure for data access layer; analyse and rewrite procedures and processes; program stored procedures, with T-SQL and CLR#; use table valued parameters; encryption

c. 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

d. Create, use, and alter user-defined functions (UDFs).

This objective may include but is not limited to: understand deterministic, non-deterministic functions; using cross apply with UDFs; Common Language Runtime (CLR)

d. Create and alter views (complex statements).

This objective 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

3. Design Database Objects (24%)

a. 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; 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

b. Design for concurrency.

This objective 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

c. 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

d. 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

e. Design for implicit and explicit transactions.

This objective 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 transaction escalation

4. Optimize and Troubleshoot Queries (24%)

a. 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;  analyse 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

b. 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

c. Optimize indexing strategies.

This objective may include but is not limited to: develop optimal strategy for clustered indexes; analyse 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

d. Capture and analyse execution plans.

This objective 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

e. 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 analyse the results; run profiler for troubleshooting application; collect output from the Database Engine Tuning Advisor; extended events

Microsoft Online Resources

Printed Material & eBooks

Learning Plans and Classroom Training

Labs and Tutorials

Practice Tests

MeasureUp

Not Available

selftest engine

Not Available

uCertify

$119.99

Online Demo

  • 100% Exam Coverage
  • Interactive E-Book
  • Pre-Assessment Test
  • 282 Practice Questions with Full Explanations
  • 102 Interactive Quizzes
  • Chapter by Chapter Study Guide & Notes
  • Articles, How Tos, Tips, Flash Cards
  • Test History and Performance Review and Powerful Analytics
  • Study Planner

Test4actual

$89.00

Download Demo PDF

75 Q&As

Test King

$124.99

Demo Available

74 Questions and Answers

Tagged , , ,

2 thoughts on “Preparing for Exam 70-464: Developing Microsoft SQL Server 2012 Databases

  1. slowder says:

    Thanks for including a link to my materials! If there’s any other content you need, or questions about SQL, Please let me know!

  2. What a fantastic resource listing, thank you so much!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: