Exam
70-433

Windows

  • Published:
    December 10, 2008
  • Languages:
    English
  • Audiences:
    IT professionals
  • Technology:
    Microsoft SQL Server 2008
  • Credit toward certification:
    MCTS, MCITP

TS: Microsoft SQL Server 2008, Database Development

This exam has been retired

For currently available options, please see the Microsoft Certification exam list.

Skills measured

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

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

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

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

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

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

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

Preparation resources

Who should take this exam?

Candidates should have two to three years of experience creating business intelligence (BI) solutions with SQL Server 2008. Candidates for this exam typically work with the full suite of BI technologies in SQL Server 2008, including Reporting Services, Analysis Services, and Integration Services.

More information about exams

Preparing for an exam

We recommend that you review this exam preparation guide in its entirety and familiarize yourself with the resources on this website before you schedule your exam. See the Microsoft Certification exam overview for information about registration, videos of typical exam question formats, and other preparation resources. For information on exam policies and scoring, see the Microsoft Certification exam policies and FAQs.

Note

This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format. To help you prepare for this exam, Microsoft recommends that you have hands-on experience with the product and that you use the specified training resources. These training resources do not necessarily cover all of the topics listed in the "Skills measured" section.