Microsoft 70-451
Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Free Questions in OTE format
Click on file name to import it and enjoy with free basic account at Loorex!
Have a file? A Starter account at Loorex.com will be given for each
uploaded file.
Notification about new Microsoft 70-451 files
Subscribe to Microsoft 70-451 dump here, you will be informed about new OTE files.
Subscribe
Registered members can subscribe in one click and easily manage their subscription. Please
create an account, it is free!
About Microsoft 70-451 dump
This exam measures your ability to accomplish the technical tasks listed below.
- Design a database strategy (13%)
- Identify which SQL Server components to use to support business requirements
SQL Server Agent, DB mail, Service Broker, Full-Text Search, Distributed Transaction Coordinator (DTC), linked servers
- Design a database model
Normalization, entities, entity relationships
- Design a data model by using the Entity Framework
Define and maintain mapping (query versus stored procedures), defining a data model, entity SQL
- Design an application strategy to support security
Application roles, schema ownership, execution context, Windows versus SQL authentication, permissions and database roles
- Design a solution by using Service Broker
Design services, contracts, activation, routes, message types, queues, remote service binding, priorities
- Design a Full-Text Search strategy
CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE
- Design database tables (16%)
- Identify the appropriate usage of new data types
Geography, geometry, hierarchyid, date, time, datetime2, datetimeoffset, varbinary (max) filestream
- Design tables
Table width, sizing data types, IN_ROW_DATA (BLOBs), overflow data, sparse columns, computed columns, persisted computed columns
- Design data integrity
Primary key, foreign key, check constraint, default constraint, NULL/NOT NULL, unique constraint, DML triggers
- Design programming objects (17%)
- Design T-SQL stored procedures
Execution context (EXECUTE AS), table-valued parameters, determine appropriate way to return data, WITH RECOMPILE/OPTION (RECOMPILE), error handling, TRY/CATCH
- Design views
Common table expressions, partitioned views, WITH CHECK OPTION, WITH SCHEMABINDING
- Design T-SQL table-valued and scalar functions
Inline table-valued functions versus views, multi-statement table-valued functions, determinism
- Design Common Language Runtime (CLR) table-valued and scalar functions
Assembly PERMISSION_SET, CLR versus T-SQL, ordered versus non-ordered
- Design CLR stored procedures, aggregates, and types
Assembly PERMISSION_SET, CLR versus T-SQL, ordered versus non-ordered, execute static methods on user-defined types, multi-parameter aggregations
- Evaluate special programming constructs
Dynamic versus prepared SQL (CREATE PROCEDURE… WITH EXECUTE AS) procedure, protect against SQL injection
- Design a transaction and concurrency strategy (14%)
- Design the locking granularity level
Locking hints, memory consumption
- Design for implicit and explicit transactions
Nested transactions, savepoints, TRY/CATCH
- Design for concurrency
Hints, transaction isolation level, effect of database option READ_COMMITTED_SNAPSHOT, rowversion and timestamp datatypes
- Design an XML strategy (8%)
- Design XML storage
Determine when to use XML for storage, untyped versus typed (XML schema collection)
- Design a strategy to query and modify XML data
When to use appropriate XPath and XQuery expressions, .query versus .value, XML indexes for performance, typed versus untyped, .exist, .modify
- Design a query strategy by using FOR XML
Views, FOR XML PATH and EXPLICIT, FOR XML…TYPE
- Design a strategy to transform XML into relational data
.nodes, .value, .query, XQuery, and XPath
- Design queries for performance (17%)
- Optimize and tune queries
Optimizer hints, common table expressions (CTEs), search conditions, temporary storage, GROUP BY [GROUPING SETS|CUBE|ROLLUP]
- Analyze execution plans
Execution order, logical and physical operators, join operators, minimize resource costs, compare query costs
- Evaluate the use of row-based operations versus set-based operations
Row-based logic versus set-based logic, batching, splitting implicit transactions
- Design a database for optimal performance (15%)
- Optimize indexing strategies
Table-valued function, views, filtered indexes, indexed views, clustered and non-clustered, unique
- Design scalable database solutions
Scale up versus scale out, federated databases, distributed partitioned views, scalable shared databases, replication, offload read-only query (database mirroring)
- Resolve performance problems by using plan guides
Object plan guides, SQL plan guides, templates plan guides, dynamic management views
- Design a table and index compression strategy
Row versus page, update frequency, page compression implementation, compress individual partitions
- Design a table and index partitioning strategy
Switch partitions, merging, splitting, staging, creating, schemes and functions