Microsoft SQL Server 2008, Database Development

Code: 70-433
Price:

Exam 70-433 - Microsoft SQL Server 2008, Database Development

Skills measured

This exam measures your ability to accomplish the technical tasks listed below. The percentages indicate the relative weight of each major topic area on the exam. The higher the percentage, the more questions you are likely to see on that content area on the exam.

Prerequisties:

Microsoft Training6231 -Maintaining a Microsoft SQL Server 2008 R2 Database

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

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)

Preparation resources

  • Creating SQL Server tables
  • Creating indexes (database engine )
  • Understanding partitioning

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

Preparation resources

  • How to: Create a stored procedure (SQL Server Management Studio)
  • Creating user-defined functions (database engine)
  • Deploying CLR database objects

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

Preparation resources

  • Query fundamentals
  • Aggregate functions (Transact-SQL )
  • ODBC scalar functions (Transact-SQL)

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

Preparation resources

  • Subquery fundamentals
  • Using common table expressions
  • International considerations for databases and database engine applications

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

Preparation resources

  • Database Mail
  • Administering full-text search
  • Operations (Service Broker)

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

Preparation resources

  • Retrieving XML data
  • Implementing XML in SQL Server
  • Maintaining XML in SQL Server

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

Course Reviews

No reviews found for this course.

Be the first to write a review