MCSA SQL Server 2012
Exame 70-464 – Developing Microsoft SQL Server 2012 Databases
| Código: 70-464 | |
| Prova | Developing Microsoft SQL Server 2012 Databases |
| Idiomas | Inglês, Português |
| Preço | US$ 100.00 |
| Duração | 3 horas |
| Número de questões | 56 |
| Nota de aprovação | 700 pontos (70%) |
| Treinamento oficial recomendado | 10776A- Developing Microsoft SQL Server 2012 Databases |
Síntese dos Assuntos
- Implementing databases objects: tables, data types, indexes, constraints, XML data
- Implementing programming objects: SP, functions, views, triggers, CLR
- Design databases objects
- Build data quality solutions
- Optimize and troubleshoot queries
Tópicos do Exame
Implement Database Objects (31%)
- 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
- 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
- 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; 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
- 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
- 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
- Create and modify constraints (complex statements).
- This objective may include but is not limited to: create constraints on tables; define constraints; performance implications
- 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
Implement Programming Objects (21%)
- 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
- 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; 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, 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)
- 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
Design Database Objects (24%)
- 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
- 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
- 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
- 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
Optimize and Troubleshoot Queries (24%)
- 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 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
- 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
- Optimize indexing strategies.
- This objective 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.
- 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
- 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; run profiler for troubleshooting application; collect output from the Database Engine Tuning Advisor; extended events