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

Introduction

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

So the following are the 7 topics that focus on the certification of MCTS for database developers (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 the following:

• 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 solutions, 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 modify 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 change DDL (data definition language) starts such as enabling/disabling, returning event data, and others
• generate 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 most significant portion of the 70-433 exam and requires that you are familiar with the following:

• 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, accounting 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, account for 11% of the total exam. To prepare for this section of the exam, you must be familiar with the following:

• 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

Working with XML Data (12%)

The next focus area is 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).