Create Account

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.
File Date Q&A Votes Size  
Microsoft.70-451.v2015-04-30.110q.ote 2015-04-30 110 0/0 728.58 Kb
Microsoft.70-451.v2014-08-28.110q.ote 2014-08-28 110 0/0 728.72 Kb

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

© 2006 ExamBrainDumps