Microsoft 70-433

Free Questions in OTE format

About Microsoft 70-433 dump

Implementing tables and views (14%)

• Create and alter tables
  Computed and persisted columns; schemas; scripts to deploy changes to multiple environments, for example, dev, test, production; manage permissions (GRANT, DENY, REVOKE)
  
• Create and alter views
  WITH ENCRYPTION; WITH SCHEMABINDING; WITH CHECK OPTION; manage permissions (GRANT, DENY, REVOKE)
  
• Create and alter indexes
  Filtered, included columns, unique, clustered, non-clustered, FILL FACTOR; CREATE STATISTICS; indexing views, compression
  
• Create and modify constraints
  PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK; SET IDENTITY_INSERT
  
• Implement data types
  FILESTREAM; spatial, structured, and semi-structured; collations
  
• Implement partitioning solutions
  Partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers)
  

Implementing programming objects (16%)

• Create and alter stored procedures
  Table-valued parameters (TVPs), EXECUTE AS, RECOMPILE, parameter direction (output); WITH ENCRYPTION; manage permissions (GRANT, DENY, REVOKE)
  
• Create and alter user-defined functions (UDFs)
  WITH SCHEMABINDING, EXECUTE AS; manage permissions (GRANT, DENY, REVOKE)
  
• Create and alter DML triggers
  INSERTED, DELETED, INSTEAD OF, EXECUTE AS
  
• Create and alter DDL triggers
  Enabling/disabling; return event data
  
• Create and deploy CLR-based objects
  Permission sets (SAFE, UNSAFE, EXTERNAL_ACCESS), SET TRUSTWORTHY
  
• Implement error handling
  TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @@ERROR
  
• Manage transactions
  BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL, named transactions
  

Working with query fundamentals (21%)

• Query data by using SELECT statements
  LIKE, WHERE, ORDER BY, INTO
  
• Modify data by using INSERT, UPDATE, and DELETE statements
  Row constructors, DELETE FROM, UPDATE FROM, TRUNCATE TABLE
  
• Return data by using the OUTPUT clause
  INSERTED, DELETED, INTO
  
• Modify data by using MERGE statements
  INSERTED, DELETED, OUTPUT
  
• Implement aggregate queries
  Built-in aggregate functions, GROUPING SETS, GROUP BY, HAVING
  
• Combine datasets
  CROSS APPLY, OUTER APPLY, all join types; UNION, UNION ALL, INTERSECT, EXCEPT
  
• Apply built-in scalar functions
  CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX
  

Applying additional query techniques (15%)

• Implement subqueries
  Simple, correlated, scalar, list, table valued
  
• Implement CTE (common table expression) queries
  Recursive, non-recursive
  
• Apply ranking functions
  RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, NTILE
  
• Control execution plans
  Table hints, query hints
  
• Manage international considerations
  Collations, defining custom errors, filtering data, sort order, nvarchar, database collation, column collation
  

Working with additional SQL Server components (10%)

• Integrate Database Mail
  
• Implement full-text search
  CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST
  
• Implement scripts by using Windows PowerShell and SQL Server Management Objects (SMOs)
  cmdlets
  
• Implement Service Broker solutions
  Services, queues, messages, message types, message validation, contracts, activation procedures, routes
  
• Track data changes
  Change tracking; database audit specification; CHANGETABLE, Change Data capture
  

Working with XML data (12%)

• Retrieve relational data as XML
  FOR XML
  
• Transform XML data into relational data
  OPENXML, sp_xml_preparedocument, sp_xml_removedocument
  
• Query XML data
  XQUERY, XPATH
  
• Manage XML data
  XML datatype, XML indexes, XML schema collections
  

Gathering performance information (11%)

• Capture execution plans
  Graphical execution plans; SHOWPLAN, index details in Query Plans
  
• Gather trace information by using the SQL Server Profiler
  Collect output from the Database Engine Tuning Advisor
  
• Prepare a workload
  Collect information from system metadata
  
• Dynamic Management Views (DMVs), catalog views