Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

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


By:   |   Read Comments (12)   |   Related Tips: 1 | 2 | More > Professional Development Certifications

Problem
Do you want to distinguish yourself as a database developer on the SQL Server 2008 platform? Consider studying for and passing the 70-433 exam. The Microsoft Learning site recommends the exam for those writing T-SQL code for database solutions. The exam emphasizes database development from the perspective of a DBA, but the skills in which you will certify your competency are relevant to other IT professionals or even non-IT professionals who regularly create database solutions. Shortly after passing the exam, you will be awarded a Microsoft Certified Technical Specialist certification for Microsoft SQL Server 2008 Database Development. This certification will also be added to the collection of previously earned certifications on your Microsoft Certified Professional transcript. Check out this tip to learn about the certification and how I passed the exam on the first try.
Solution
There are three steps to becoming a certified SQL Server database development expert. First, get a good grasp of the right content. In other words, know what content areas are covered in the 70-433 exam. Second, develop a study regime to take the test. This regime should include using the right study materials and exam preparation aids as well as the proper allocation of your own time. Adjust your preparation time and effort based on your achievement of a good understanding of the exam content as well as your achievement of high scores on practice tests. Third, purchase an exam voucher and schedule the exam. The exam voucher authorizes you to take the exam once or an optional second time. If you do not pass the exam on the first try or an optional re-take try, you will have to buy a new voucher for another attempt at passing the exam.

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

Preparing to pass any certification exam, including 70-433, can require a considerable investment of your time and effort. Unless your employer or some other party is paying for your exam voucher and preparation aids, you will need to spend your own money to prepare for and take the exam. Therefore, make sure that you are preparing for the SQL Server certification exam that best serves your needs and interests.

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.

Certified technical specialist for microsoft sql server 2008 database developement
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Rick Dobson Rick Dobson is a Microsoft Certified Technical Specialist and well accomplished SQL Server and Access author.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, February 18, 2013 - 10:32:10 PM - Rick Dobson Back To Top

I did a google search for "70-433 discount exam voucher" and a large number of links appear.  Maybe you could try this to find your best deal for re-taking the exam if the exam voucher that you initially purchased does not have an automatic, free re-take option. 

 

I hope this helps you.


Monday, February 18, 2013 - 1:25:51 PM - Calvin Brown Back To Top

Good afternoon, I have  a 2000  certification and  wish to proceed with obtaining 70-433 and 70-432. I want to schedule the 70-4433  first. How much is the voucher?  I took it  las t year in August and failed , about 9 questions away from passing. so how much is the voucher before I reschedule in March?


Tuesday, January 29, 2013 - 7:54:59 PM - Rick Dobson Back To Top

Congrats, Kyle, on passing the 70-461 exam.

 

I have a couple of new tips on the way to help folks who want to pass the 70-460 exam.  I hope these tips will help those who want to start buiding their SSIS skills to implement a data warehouse.


Tuesday, January 29, 2013 - 4:14:35 PM - Kyle Back To Top

I just completed the 70-461 Querying SQL Server 2012 exam, passing on the first try as well. It was a lot harder than I was expecting and I'm glad I spent as much time studying for it as I did. Rick's article here was part of that study process and I thank you for it Rick. Good job !!

One thing I'd like to say is the MeasureUp test exams are garbage. Don't waste your money on them. The questions are just so ridiculously easy and don't come close to what you'll see on the exam. Really, they should be ashamed of themselves.

To study for it I read Ben-Gan's TSQL Fundamentals cover to cover and did every single question in it. I then read the Training Kit book cover to cover and instead of doing the questions there I created my own databases and made up my own questions, for each of the subjects. Almost all examples you find are based on the sales, orders, order details, etc type of databases so I made up my own databases that are completely different structure. (eg: a survey, questions and answers db)  This way I was writing and the learning the same queries but in a completely different way.


Wednesday, November 21, 2012 - 2:40:58 PM - Rick Dobson Back To Top

I am glad that you and others derived value from this two-part series on SQL Server 2008 certification.

My first article in a new series on SQL Server 2012 certification is likely to appear soon.  As you are preparing for SQL Server 2008 certfiication, you should keep in mind that the issuance of new certifications for SQL Server 2008 will discontinue sometime during 2013.  The exact date when new issuance of SQL Server 2008 certifications will discontinue will appear in my first article on SQL Server 2012 certification.


Wednesday, November 21, 2012 - 12:05:51 PM - Ananth Back To Top

Great Information sir very useful


Wednesday, November 21, 2012 - 10:03:38 AM - Rick Cloud Back To Top

Rick, thanks so much for this article... I've just started prep for the 433 and will then move on to the 448(BI).


Friday, December 30, 2011 - 9:20:34 AM - Jason Yousef Back To Top

Rick, thanks again, do you have a similar reading list and hints for the 70-432 ?


Friday, December 30, 2011 - 7:15:28 AM - Rick Dobson Back To Top

Thanks very much for the feedback.  I wrote the article to help others pass the exam.  Don't forget to click all the links added by the MSSQLTips staff on the topics listed in the article.  These tips provide an easy way to start ramping up your skils in any areas where you need to learn the basics or grow your core knowledge of an area.  I am going to pray that you pass the exam with flying colors on the first try.

When you earn your SQL Developer certification, think about posting another comment.  It will give me and others a chance to congratulate you personally.


Thursday, December 29, 2011 - 11:49:53 AM - Ankit Shah Back To Top

I am SQL DBA and Preparing for 70-433 exam and found this article very helpful.

Thanks a lot


Tuesday, October 11, 2011 - 9:19:33 AM - Jason Yousef Back To Top

Congrats...


Monday, July 25, 2011 - 10:29:48 AM - javid Khan Back To Top

Great News. Thank you for the very Important information. I don't want DBA certification but SQL developer only certification. This will help me adding needed certification. Thanks a lot.

 


Learn more about SQL Server tools