Designing a Database Strategy (13 percent) Identify which SQL Server components to use to support business requirements.
This objective may include but is not limited to: SQL Server Agent, DB mail, Service Broker, Full-Text Search, Distributed Transaction Coordinator (DTC), linked servers Design a database model.
This objective may include but is not limited to: normalization, entities, entity relationships Design a data model by using the Entity Framework.
This objective may include but is not limited to: defining and maintaining mapping (query vs. stored proc), defining a data model, entity SQL Design an application strategy to support security.
This objective may include but is not limited to: application roles, schema ownership, execution context, Windows vs. SQL authentication, permissions and database roles Design a solution by using Service Broker.
This objective may include but is not limited to: designing services, contracts, activation, routes, message types, queues, remote service binding, priorities Design a Full-Text Search strategy.
This objective may include but is not limited to: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE
Designing Database Tables (16 percent) Identify the appropriate usage of new data types.
This objective may include but is not limited to: geography, geometry, hierarchyid, date, time, datetime2, datetimeoffset, varbinary (max) filestream Design tables.
This objective may include but is not limited to: table width, sizing data types, IN_ROW_DATA (BLOBs), overflow data, sparse columns, computed columns, persisted computed columns Design data integrity.
This objective may include but is not limited to: primary key, foreign key, check constraint, default constraint, NULL/NOT NULL, unique constraint, DML triggers
Designing Programming Objects (17 percent) Design T-SQL stored procedures.
This objective may include but is not limited to: execution context (EXECUTE AS), table-valued parameters, determining appropriate way to return data, WITH RECOMPILE/OPTION (RECOMPILE), error handling, TRY/CATCH Design views.
This objective may include but is not limited to: common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING Design T-SQL table-valued and scalar functions.
This objective may include but is not limited to: inline table-valued functions vs. views, multi-statement table-valued functions, determinism Design Common Language Runtime (CLR) table-valued and scalar functions.
This objective may include but is not limited to: assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered Design CLR stored procedures, aggregates, and types.
This objective may include but is not limited to: assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered, executing static methods on user-defined types, multi-parameter aggregations Evaluate special programming constructs.
This objective may include but is not limited to: dynamic vs. prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure, protecting against SQL injection
Designing a Transaction and Concurrency Strategy (14 percent) Design the locking granularity level.
This objective may include but is not limited to: locking hints, memory consumption Design for implicit and explicit transactions.
This objective may include but is not limited to: nested transactions, savepoints, TRY/CATCH Design for concurrency.
This objective may include but is not limited to: hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes
Designing an XML Strategy (8 percent) Design XML storage.
This objective may include but is not limited to: determining when to use XML for storage, untyped vs. typed (XML schema collection) Design a strategy to query and modify XML data.
This objective may include but is not limited to: when to use appropriate XPath and XQuery expressions, .query vs. .value, XML indexes for performance, typed vs. untyped, .exist, .modify Design a query strategy by using FOR XML.
This objective may include but is not limited to: views, FOR XML PATH and EXPLICIT, FOR XML…TYPE Design a strategy to transform XML into relational data.
This objective may include but is not limited to: .nodes, .value, .query, XQuery and XPath
Designing Queries for Performance (17 percent) Optimize and tune queries.
This objective may include but is not limited to: optimizer hints, common table expressions (CTEs), search conditions, temporary storage, GROUP BY [GROUPING SETS|CUBE|ROLLUP] Analyze execution plans.
This objective may include but is not limited to: execution order, logical and physical operators, join operators, minimize resource costs, compare query costs Evaluate the use of row-based operations vs. set-based operations.
This objective may include but is not limited to: row-based logic vs. set-based logic, batching, splitting implicit transactions
Designing a Database for Optimal Performance (15 percent) Optimize indexing strategies.
This objective may include but is not limited to: table-valued function, views, filtered indexes, indexed views, clustered and non-clustered, unique Design scalable database solutions.
This objective may include but is not limited to: scale up vs. scale out, federated databases, distributed partitioned views, scalable shared databases, replication, offloading read-only query (database mirroring) Resolve performance problems by using plan guides.
This objective may include but is not limited to: object plan guides, SQL plan guides, templates plan guides, dynamic management views Design a table and index compression strategy.
This objective may include but is not limited to: row vs. page, update frequency, page compression implementation, compressing individual partitions Design a table and index partitioning strategy.
This objective may include but is not limited to: switching partitions, merging, splitting, staging, creating, schemes and functions
|
