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

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:

This objective may include but is not limited to: WITH ENCRYPTION; WITH SCHEMABINDING; WITH CHECK OPTION; manage permissions (GRANT, DENY, REVOKE) 

Create and alter indexes:

This objective may include but is not limited to: filtered, included columns, unique, clustered, non-clustered, FILL FACTOR; CREATE STATISTICS; indexing views 

Create and modify constraints

This objective may include but is not limited to: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK; SET IDENTITY_INSERT 

Implement data types:

This objective may include but is not limited to: FILESTREAM; spatial, structured, and semi-structured; collations 

Implement partitioning solutions:

This objective may include but is not limited to: partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers) 

Create and alter stored procedures:

This objective may include but is not limited to: table-valued parameters (TVPs), EXECUTE AS, RECOMPILE, parameter direction (output); WITH ENCRYPTION; manage permissions (GRANT, DENY, REVOKE) 

Create and alter user-defined functions (UDFs):

This objective may include but is not limited to: WITH SCHEMABINDING, EXECUTE AS; manage permissions (GRANT, DENY, REVOKE)

Create and alter DML triggers:

This objective may include but is not limited to: INSERTED, DELETED, INSTEAD OF, EXECUTE AS 

Create and alter DDL triggers:

This objective may include but is not limited to: enabling/disabling; return event data 

Create and deploy CLR-based objects:

This objective may include but is not limited to: permission sets (SAFE, UNSAFE, EXTERNAL_ACCESS), SET TRUSTWORTHY 

Implement error handling:

This objective may include but is not limited to: TRY/CATCH, RAISERROR, retrieving error information, custom error messages, @@ERROR 

Manage transactions:

This objective may include but is not limited to: BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL

Query data by using SELECT statements:

This objective may include but is not limited to: LIKE, WHERE, ORDER BY, INTO 

Modify data by using INSERT, UPDATE, and DELETE statements:

This objective may include but is not limited to: row constructors, DELETE FROM FROM, UPDATE FROM, TRUNCATE TABLE 

Return data by using the OUTPUT clause:

This objective may include but is not limited to: INSERTED, DELETED, INTO 

Modify data by using MERGE statements:

This objective may include but is not limited to: INSERTED, DELETED, OUTPUT

Implement aggregate queries:

This objective may include but is not limited to: built-in aggregate functions, GROUPING SETS, GROUP BY, HAVING

Combine datasets:

This objective may include but is not limited to: CROSS APPLY, OUTER APPLY, all join types; UNION, UNION ALL, INTERSECT, EXCEPT 

Apply built-in scalar functions:

This objective may include but is not limited to: CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX 

Implement subqueries:

This objective may include but is not limited to: simple, correlated, scalar, list, table valued 

Implement CTE (common table expression) queries:

This objective may include but is not limited to: recursive, non-recursive 

Apply ranking functions:

This objective may include but is not limited to: RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, NTILE

Control execution plans:

This objective may include but is not limited to: table hints, query hints 

Manage international considerations:

This objective may include but is not limited to: collations, defining custom errors, filtering data, sort order, nvarchar, database collation, column collation 

Integrate Database Mail

Implement full-text search:

This objective may include but is not limited to: CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST 

Implement scripts by using Windows PowerShell and SQL Server Management Objects (SMOs):

This objective may include but is not limited to: cmdlets 

Implement Service Broker solutions:

This objective may include but is not limited to: services, queues, messages, message types, message validation, contracts, activation procedures 

Track data changes:

This objective may include but is not limited to: change tracking; database audit specification; CHANGETABLE 

Retrieve relational data as XML:

This objective may include but is not limited to: FOR XML

Transform XML data into relational data:

This objective may include but is not limited to: OPENXML, sp_xml_preparedocument, sp_xml_removedocument 

Query XML data:

This objective may include but is not limited to: XQUERY, XPATH

Manage XML data:

This objective may include but is not limited to: XML datatype, XML indexes, XML schema collections 

Capture execution plans:

This objective may include but is not limited to: graphical execution plans; SHOWPLAN

Gather trace information by using the SQL Server Profiler

Collect output from the Database Engine Tuning Advisor:

This objective may include but is not limited to: prepare a workload

Collect information from system metadata:

This objective may include but is not limited to: Dynamic Management Views (DMVs), catalog views