Microsoft 70-451

Free Questions in OTE format

About Microsoft 70-451 dump

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