Do You Want to be a Certified SQL Server Database Development Expert

By: Rick Dobson   |   Updated: 2011-07-21   |   Comments (12)   |   Related: 1 | 2 | More > Professional Development Certifications

Problem

Solution

I was fortunate to pass the exam on my first try. This article presents several tips that I believe helped me to achieve this outcome. It is my sincere hope that the tips will help you achieve the same outcome with less effort than it took me.

Overview of Microsoft SQL Server 2008 Certifications

Microsoft offers SQL Server 2008 certifications for most IT professionals at two distinct levels for three different tracks. The Microsoft Certified Technical Specialist (MCTS) level is for intermediate level professionals who want to confirm they are expert at a core set of content and tasks. Each track has a unique set of content and tasks associated with it. The Microsoft Certified IT Professional (MCITP) certifications are for senior level professionals. Again, there is a distinct MCITP certification for each of the three core areas of competency. By earning either of the MCTS or MCITP certifications for more than one track, you widen the scope of skills in which you are confirming your competency. In order to gain the MCITP certification for any track, you must first earn the MCTS certification for the same track.

The three tracks for which MCTS and MCITP certifications are offered include: Implementation and Maintenance; Database Development; and Business Intelligence Development and Maintenance. You can learn more about the skills covered for the Implementation and Maintenance track at the MCTS level in the MSSQLTips article titled "Do You Want to be a Microsoft Certified SQL Server DBA Expert." The current article drills down on the skills covered by and techniques for earning your MCTS certification for database development. Subsequent articles will cover other tracks and/or levels.

There are two additional SQL Server certification levels beyond the MCTS and MCITP levels. These additional certifications target SQL Server professionals working in the most demanding database environments with exceptionally sophisticated requirements. The Microsoft Certified Master (MCM) certification builds on the MCITP level, but the preparation requirements and costs associated with the MCM program far exceed those for the MCITP. In addition, you must already hold both MCTS and MCITP certifications as a prerequisite for the MCM program. The SQL Server Microsoft Certified Architect (MCA) program is for experienced SQL Server consultants who have already earned an MCM certification. The MCA certification especially targets complex, enterprise SQL Server installations with corresponding database development issues. You gain this final SQL Server certification by demonstrating your advanced technical and management skills to a board of industry experts. The Microsoft Learning site offers additional details about both the MCM and MCA certifications.

What's Covered on the Exam

The Microsoft Learning site identifies seven key areas covered by the 70-433 exam. However, the relative weight in the exam varies by area. The following list itemizes the areas and gives the weight for each area as a percentage:

• Implementing tables and views (14)
• Implementing programming objects (16)
• Working with query fundamentals (21)
• Applying additional query techniques (15)
• Working with additional SQL Server components (11)
• Working with XML data (12)
• Gathering performance information (11)

The exam emphasizes new features and emerging topics, but it also covers traditional topics as well. The good news about this twin attention to new and traditional topics is that preparing for the exam will force a degree of familiarity with a wide variety of database development features, including some traditional ones that you rarely or never use as well as nearly all the new database development topics. You need to be fully conversant with table design issues as well as have an intimate knowledge of how to program and secure the use of stored procedure, trigger, and user-defined functions. In considering table design, you will need to know about the new data types for dates and times as well as the SQL Server 2008 support for geometric and geographic data. In addition, you may encounter questions on defining and managing indexes for tables and indexed views. Be sure to learn about filtered indexes. Also make sure you understand column versus table constraints as well as foreign key constraints. Make sure you know the differences between AFTER versus INSTEAD OF for DML triggers and acquire a complete understanding of the OUTPUT clause with INSERT, DELETE, UPDATE, and MERGE statements. Also, have a good working knowledge of DDL triggers.

The practical consequence of the emphasis on new features for either SQL Server 2008 or SQL Server 2005 is that you must have a hands-on feel for applying new database development functionality. This will generally cause your database applications to run faster and/or be more efficient at processing large data sources. I was personally impressed by the way preparation and actual test questions drilled down on topics like managing partitioned tables, aggregation with CUBE, ROLLUP, and GROUPING SETS operators, specifying and using Common Table Expressions, modification by MERGE statements, SQL Server Service Broker, new ranking features (ROW_NUMBER, RANK, DENSE_RANK, and NTILE), Change Tracking and Change Data Capture. The Full-Text Search capability is actually a traditional topic, but it was substantially revised with SQL Server 2008. It would be a big mistake to take the exam without a good hands-on feel for all these topics.

While XML is not really a new SQL Server topic, its widespread adoption by DBA developers might legitimately be characterized as still evolving. The exam asks slightly more than ten percent of its questions on XML processing topics, and the correct answers for these questions can be non-obvious if you do not have a thorough grasp of the appropriate content. In particular, learn all the variations of the FOR XML clause. This clause in a SELECT statement lets you output relational data in a variety of different XML formats. You should also have a working knowledge of the XML data type methods, such as query, value, exist, modify, and nodes. Exam questions can require an extremely detailed knowledge of the OPENXML function. You can use the OPENXML function in the FROM clause of a SELECT statement to output XML content in a classic tabular format. Learn XML Schema Collections and how to use them for converting un-typed XML to typed XML. Also, become familiar with the different types of XML indexes, potential conflicts between primary XML index declarations, as well as how to and what the benefits are of complementing primary XML indexes with secondary XML indexes.

Many SQL Server DBAs will have some familiarity with execution plans, SQL Server Profiler, Database Engine Tuning Advisor. If you are not intimately familiar with these tools use your preparation time for the exam to master the topics because questions on these topics can be very subtle if you have only a slight familiarity or no basic understanding of the topics. A working understanding of these topics will help you learn about how SQL Server is performing or can perform better. On a related topic, learn how dynamic management functions and views can help you gather performance and related information. As I was preparing for the exam, I found myself regularly referring to this subset: sys.dm_exec_requests; sys.dm_exec_sessions; sys.dm_exec_sql_text; sys.dm_exec_query_stats; sys.dm_os_wait_stats; sys.dm_exec_query_plan; sys.dm_db_index_usage_stats.

Resources to Help Pass the Exam

The 70-433 exam that I took was a series of 55 multiple choice questions. The time allowed to complete the questions was 135 minutes. You can mark questions for review. If there is any time left after you have completed all questions, you can review and modify your answers. Questions that you do not answer are counted as incorrect. To obtain a passing grade, you must answer 70 percent or more of the questions correctly. Based on a prior exam experience that I had with the Implementation and Maintenance exam (70-432), Microsoft can change the format of questions, test length, and specific questions asked. There is no guarantee that preparing with any particular set of study questions will assure you a passing grade. The way to guarantee a passing grade is to make sure that you KNOW THE TOPICS COVERED ON THE EXAM.

So what tools are available to help you learn the right content?

• The Microsoft Learning site includes a URL devoted exclusively to the 70-433 exam with a tab of general content categories as well as examples of specific content in each category. Unfortunately for examinees, the specific content listed does not exhaustively itemize potential exam topics.
• You can study with sets of practice test questions. The Microsoft Learning site identifies two Microsoft Certified Practice Test Providers. These providers are MeasureUp and Self Test Software. I strongly urge you to obtain product from one or both of these vendors because their products faithfully simulate the actual test experience, offer high quality test questions, provide versatile software for using their questions and provide quality answers and links for obtaining more detailed information about exam topics.
• Microsoft also releases the Microsoft SQL Server 2008 Database Development Training Kit. This kit includes a book, two CDs and a discount offer for an exam voucher. One CD contains a trial version of SQL Server 2008, and the other CD stores sample test questions with test simulation software sample code, and other material that complemented the content in the book.
• You can also purchase books with commentary on key topics and sample questions. In preparing for the exam, I tried the book titled "The Real MCTS SQL Server 2008 Exam 70-433 Database Design Prep Kit" by Valentine Boairkine and Herleson Pontes along with others.
• The MSDN and TechNet web sites include voluminous material on the content covered in the exam. This material can include overviews of broad areas, drill downs on specific topics within a broad area and code samples illustrating database development techniques.
• Finally, I recommend lots of hands-on experience with SQL Server 2008. Run the code samples from the MSDN site. Modify and extend the code samples until you understand thoroughly how the samples work.

I believe that one of my best study techniques when I started to prepare for the exam was to print out content from the MSDN site and then underline relevant portions. The practice of underlining content helped me to internalize blocks of material. When I needed to refer back to a content topic from the MSDN web site, the underlined material helped me to focus on the more critical content. In the latter stages of my preparation, I found that I had printed so much content that I could not easily find material for which I wanted to review details. However, I was able in the latter stages of preparation to perform a computer search of MSDN and then quickly find specific content in a topic that I needed.

You can use the sample questions provided by vendors in any of several modes. One mode you will be sure to want to use especially towards the end of your preparation is one that simulates faithfully the actual test. This mode uses either 45 or 55 questions depending on the vendor, and the mode times you out if you have not completed all questions in 135 minutes. The actual test simulator mode also selects questions from broad categories in the percentages suggested by the Microsoft Learning site. You may also prefer the actual test simulator mode at the very beginning to identify areas that you need to study in further depth. However, after discovering the areas that you need to learn the most about, you may prefer to switch to a study mode. In the study mode, you can select any number of questions from any selection of categories. This mode allows you to drill down on areas of your weakness. In addition, you can control the amount of time that you devote to a practice exam session by using more or less questions than the standard 45 or 55 questions.

Using test questions from two or even more vendors is a great idea because it helps to keep the questions fresh. As you continue to study the questions from a vendor, you begin to memorize the questions and answers. However, when you take the real certification exam, it is highly likely you will encounter fresh questions that you have never reviewed previously. As a result, the more you study any particular set of test questions, the less representative test simulations become. When you are performing test simulations at the end of your preparation period, consider completing the exam 30 minutes or more before the allotted time. Again, this is because you are highly familiar with these questions and you should be able to answer them much more quickly than a fresh set of questions that you have never previously used.

You need a voucher to take the exam. PROMETRIC is a provider of vouchers for MCTS exams. By searching the web, you may be able to obtain a better deal than from Prometric. After you obtain your exam voucher from a vendor, you will need to schedule your exam at an authorized Prometric testing center. The popularity of the Microsoft certification exams over the years is such that it was not uncommon for me to have to drive to another city to take an exam when I was ready. One way to avoid this kind of inconvenience is to schedule the exam well in advance of when you are ready to take it. Then, you can adjust your preparation time so that you are ready to take the exam on schedule.

After finally passing the exam, you can access the Microsoft's MCP site. From that location, you can download your certificate or your Microsoft Certified Professional Transcript. Here's my transcript that shows the certifications which I earned and the exams that I successfully completed. Notice that it includes the certification for SQL Server 2008, Database Development.

Next Steps

• Microsoft Learning site page with an overview of Microsoft SQL Server certifications: http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx
• Microsoft Learning site page with details about topics covered and preparation aids for the 70-433 exam: http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-433
• Microsoft Learning site page for the Microsoft SQL Server 2008 Database Development Training Kit: http://www.microsoft.com/learning/en/us/Book.aspx?ID=13159
• Self Test Software site page for the 70-433 preparation materials: http://www.selftestsoftware.com/product.aspx?product_id=70-433
• MeasureUp site page for the 70-433 preparation materials: http://www.measureup.com/MCTS-SQL-Server-2008-Database-Development-C279.aspx
• PROMETRIC phone number for scheduling an exam: http://www.prometric.com/Microsoft/Americas.htm
• Microsoft's MCP member site where you can download your certificate after passing the exam and receiving instructions from Microsoft: https://mcp.microsoft.com/mcp

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips