- Topics Mentioned
- Certification(s):
- MCTS
- Exam(s):
- Microsoft Exams
As we went over in the SQL Server Database Administration Exam Guide last week, Microsoft SQL Server 2008 along with SQL Server 2008 R2 has brought some important changes to the platform since the 2005 version.
The Database Development certification path is designed for database developers that write applications, websites, and more that run with, or within SQL Server 2008 with at least two to three years of experience using a previous version of SQL Server (preferably the 2005 version) and one to two years of experience using the latest 2008 version.
MCTS (70-433) Database Development Exam
As discussed in my previous article, the Database Development Certification (based on MCTS exam 70-433) is one of three entry level SQL Server 2008 certifications that are currently available:
- MCTS: SQL Server 2008, Implementation and Maintenance
- MCTS: SQL Server 2008, Database Development
- MCTS: SQL Server 2008, Business Intelligence Development and Maintenance
All three of which map to different certification paths withing the SQL Server 2008 area:
- Database Administration
- Database Development
- Business Intelligence (BI)
The MCTS: SQL Server 2008, Database Development exam is for IT Professionals who work in environments in which SQL Server 2008 is part of the overall solution, those who manage systems that run SQL Server 2008, developers who build applications that use SQL Server 2008, and those who work with third-party applications that are built on or to work with SQL Server 2008. If you fall into any of these categories, this certification would likely give you a great edge in securing or expanding on your current job or portfolio.
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.
1. 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
2. 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
3. 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
4. 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
5. 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
6. 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).
7. 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).
SQL Server 2008 Database Development Training
As I always recommend to anyone preparing to take a certification exam, your training should come from a variety of sources. In general, it is always best to learn from at least two training solutions, such as books, in class, online, video training, etc. Plus your real world experience compliments any training your take on.
Train Signal offers a great training geared specifically towards the 70-433 exam. SQL Server 2008 Database Development Training created by Mel Haynes (MCIPT: Database Administrator 2008, MSCS, MCTS, MCP) has over 10 hours of hands-on SQL Server 2008 training.
Also, as with most Train Signal training courses, you get a free full version Transcender practice exam, which is an extremely useful tool to measure your ability before taking the exam.
Learn more about Train Signal’s SQL Server 2008 Database Administration Training and keep an eye out for my upcoming SQL Server 2008 certification articles which will outline the MCITP paths for database development as well as administration.
great post!