Create Account

Microsoft 70-451

Designing Database Solutions and Data Access Using Microsoft SQL Server 2008

Free Questions in OTE format

 
File Size  
Microsoft.70-451.v2015-04-30.by.barbara.110q.ote 681.66 Kb
Microsoft.70-451.v2014-08-28.by.robert.110q.ote 682.73 Kb

Notification about new Microsoft 70-451 files

Subscribe to Microsoft 70-451 dump here, you will be informed about new OTE files.
Subscribe
 

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