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).