Certificaion of MCTS for Database Developer(70-433).

Introduction 

Most of the us don't know about the certification topics for 70-433 or on the basis of 70-433 book of microsoft is not only sufficient to study and clear certification. To upgrade with the knowledge and the technology we need to do a study in deep.

so following are the 7 topics which focus on the certificaion of MCTS for database developer(70-433).

70-433 Exam Requirements 

The MCTS Exam 70-433 focuses on 7 

Main areas:

• Implementing tables and views
• Implementing programming objects
• Working with query fundamentals
• Applying additional query techniques
• Working with additional SQL Server components
• Working with XML data
• Gathering performance information

Let's take a closer look at these 7 areas. 

Implementing Tables and Views (14%)

Implementing tables and views takes up 14% of the total exam questions and assumes that you are familiar with:

• Creating and altering tables including computed and persisted columns, schemas, scripts to deploy changes to multiple environments and more
• Creating and altering views such as WITH ENCRYPTION, WITH SCHEMABINDING, WITH CHECK OPTION and others
• Creating and altering indexes including filtered, unique, clustered, non-clustered, FILL FACTOR, CREATE STATISTICS, indexing views and more
• Creating and modifying constraints including PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling and more
• Implementing data types such as FILESTREAM, spatial, structured, and semi-structured, collations and others
• Implementing partitioning solution including partitioned tables and indexes, distributed partitioned views, etc

Implementing Programming Objects (16%)

The second largest focus point of the 70-433 exam focuses on implementing programming objects and makes up 16% of the total exam. This section measures your ability to:

• create and alter stored procedures including able-valued parameters, parameter direction, manage permissions and more
• create and alter user-defined functions (UDFs) including WITH SCHEMABINDING, EXECUTE AS, managing permissions, etc
• create and alter DML (data manipulation language) triggers such as INSERTED, DELETED, INSTEAD OF, EXECUTE AS and others
• create and alter DDL (data definition language) triggers such as enabling/disabling, return event data and others
• create and deploy CLR-based objects including permission sets, SET TRUSTWORTHY and more
• implement error handling including TRY/CATCH, RAISERROR, retrieving error information, custom error messages and more
• manage transactions such as BEGIN TRANSACTION, COMMIT, ROLLBACK, SET TRANSACTION ISOLATION LEVEL and others

Working with Query Fundamentals (21%)

Working with query fundamentals takes up the largest portion of the 70-433 exam and requires that you are familiar with:

• query data by using SELECT statements such as LIKE, WHERE, ORDER BY, and INTO
• modify data by using INSERT, UPDATE, and DELETE statements
• return data by using the OUTPUT clause as well as INSERTED, DELETED, and INTO statements
• modify data by using MERGE statements such as INSERTED, DELETED, and OUTPUT
• implement aggregate queries including built-in aggregate functions, GROUPING SETS, and GROUP BY, among others
• combine datasets using CROSS APPLY, OUTER APPLY, UNION, UNION ALL, INTERSECT, EXCEPT and more
• apply built-in scalar functions such as CAST and CONVERT, REPLACE, PATINDEX and CHARINDEX and others

Applying Additional Query Techniques (15%)

The next topic covers additional query techniques and accounts for 15% of the 70-433 exam. This area will test your ability to:

• implement subqueries such as simple, correlated, scalar, list, and table valued
• implement CTE (common table expression) queries including recursive and non-recursive queries
• apply ranking functions such as RANK, PARTITION BY, DENSE_RANK, OVER, ROW_NUMBER, and NTILE
• control execution plans like table hints and query hints
• manage international considerations including collations, defining custom errors, filtering data, sort order, nvarchar, and more

Working with Additional SQL Server Components (11%)

Working with additional SQL Server components such as database mail, full-text search, SMOs and PowerShell scripts among others, accounts for 11% of the total exam. To prepare for this section of the exam you must be familiar with:

• integrating database mail
• implementing full-text search (CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE, STOPLIST
• implementing scripts using PowerShell and SQL Server Management Objects (SMOs)
• implementing service broker solutions including services, queues, messages, message types, message validation,
• contracts and activation procedures
• tracking data changes including database audit specification and CHANGETABLE
  http://www.sqldbatips.com/showarticle.asp?ID=136

Working with XML Data (12%)

The next focus area takes up 12% of the 70-433 exam and centers on working with XML data. For this portion of the exam you will need to know how to 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) and manage XML data (XML datatype, XML indexes, XML schema collections). 

Gathering Performance Information (11%)

The final section of the 70-433 exam focuses on gathering performance information and takes up just 11% of the total exam. To prepare for this portion of the exam you will need to know how to capture execution plans (SHOWPLAN), gather trace information by using the SQL Server Profiler, collect output from the Database Engine Tuning Advisor and collect information from system metadata (Dynamic Management Views (DMVs) and catalog views).