In today’s competitive world, technical certifications play an important role in advancing your career. It is a great way to validate and sharpen your technical skills, thus helping you in being more productive in your assignments. It also helps you to stand out from the crowd and get more visibility. A few months back I decided to appear the Microsoft Exam 70-461: Querying SQL Server 2012/2014 and was able to successfully pass the exam. I have been working with SQL Server for more than 10 years, but I must admit that preparing for this exam was a great experience and I got to learn so many new things which I did not get a chance to work on during my day to day work. Everyday someone in the community plans to take this certification exam and I have bumped into multiple questions in social forums and groups asking for tips and guidance to pass the 70-461 Exam. This prompted me to come up with this article and share my experience with the community and hopefully it will be helpful for anyone who is planning to take this certification.

Skills Required

The 70-461 exam comprise of questions from the below area:

• Create database objects (20–25%)
• Work with data (25–30%)
• Modify data (20–25%)
• Troubleshoot and optimize (25–30%)

And after appearing the exam I must admit that the questions were well spread across, as mentioned in the percentages above. No surprises there! My first suggestion will be to take this exam as an opportunity to learn more about SQL Server. No matter if you are a beginner or experienced SQL developer, there will be areas in which you do not have practical working experience – and those will be the areas where you need to first focus on. After going through the exam syllabus, I figured out that I need to practice more on few selected areas on which I do not get the opportunity to work often like XML Data type, Windowing functions, Transactions and Isolation Levels. Looking back, I think this decision paid off and I was able to successfully able to handle questions on these areas – though I was a bit surprised to find lot more questions from these areas than I had expected. Do not depend upon Dumps or any online questions to pass the exam, rather you should spend time understanding the concepts and gaining hands-on experience. Make sure that you practice writing SQL queries. Having just a theoretical knowledge won’t help you pass this exam. I would highly encourage you to download SQL Server Management Studio and install on your machine if you already don’t have it. You can then download a sample database and practice writing queries. There are lot of resources provided by Microsoft which will help you to answer some of the frequently asked questions about the certification exam. Make sure you read them here.

Practice Test

You can take the Microsoft Official Practice Test (not free), to help you prepare better for the actual exam. I was very satisfied with the quality of the questions in the Practice Test, and it helped me to identify areas where I had to focus upon. All the questions had detailed explanations for the answer options mentioned, which aided me in revising the skills required for this exam. Overall the practice tests did help me to prep up for the exam and gain confidence, but I must say that the actual 70-461 was a lot tougher than the practice exam. Microsoft provides special offers on the Certification exams – in the form of an additional retake or even a practice test. It might not be a bad decision to improve your exam odds, by adding an extra retake. It’s always a good feeling to have the cushion of an extra attempt to pass the exam. You can learn more about the certification and exam offers here.

Actual Certification Exam

The exam is a good mix of multiple choice questions, rearrange statements to form the correct stored procedure/Function/Triggers, select correct options from the drop down lists, case studies and more. The toughest part of the exam will be questions where you would need to actually write the T-SQL Code – query, stored procedure, triggers, function etc. All your hands-on experience in SQL will come to play here. If you find these set of 8-10 questions though, I would suggest not to spend much time on it upfront and get discouraged. Mark them for review and come back to it later, once you have completed rest of the questions. You will always have few questions, which you would be able to answer right away. Knock them down first!

Type of questions and formats

Before appearing for the exam, it is always helpful to know the question pattern and be familiar with the exam interface. I am listing below the different categories of questions you will find in the 70-461 exam, to help you better prepare for the exam: Build List There will be a scenario mentioned and 4-5 actions to perform in a sequence. You will need to arrange this list of actions in the correct order to achieve the goal. Basically, it will be a kind of Drag and Drop type question and you need to put your selections in the correct order. To tackle these types of questions it will be great if you can thoroughly practice various T-SQL syntax like Creating/Modifying Constraints and DML Triggers, Creating Stored Procedures and Functions, Error Handling and more. Case Studies There will be a Business case with multiple questions associated with it. Make sure you patiently read through the entire business requirement before proceeding to answer the questions. Flipping back and forth between the questions and the case details might take unnecessary time. A helpful tip here will be to read through the various options available in the answer section to quickly understand the intent of the case study. Drag and Drop There will be a number of questions which will need you to drag and drop correct answers from the given list of options. This might get tricky sometimes since there might be cases when the same answer option needs to be dragged multiple times to answer correctly. Multiple Choice Questions These will be the easiest of all the question types and you will need to select the correct option for the given question. A pro tip will be to ensure that you answer all such questions and not leave any unanswered. Remember that there are no negative marks for wrongly answered questions. If you are not aware of the right answer, try to eliminate wrong options and narrow down the set of probable answers to the question. Once you do that, you can try your luck by guessing the correct answer. Writing SQL Code Snippets These will be the toughest questions to crack. A text area will be provided to write the code to resolve a real-life problem. It might be either writing a stored procedure, trigger, user defined function or similar code snippets. SQL Keywords are provided in a separate tab to assist you with writing code. There will be no intelligence when you start writing your code in this text area, so I would say to answer these questions you would need thorough hands on knowledge. There were about 8-9 such questions and took me the most time to answer them correctly.

Study Materials

There are a lot of useful resources available online to prepare you for the exam: I would strongly recommend you to go through the book – ‘Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012′. This book covers all the topics and skills which will be required for you to pass this exam. Make sure that you are simultaneously doing a lot of hands-on querying as well. Microsoft Virtual Academy has great content and detailed training material on the various focus areas required for 70-461 – Querying Microsoft SQL Server 2012 Databases Jump Start If you are new to SQL Server, then I would suggest you to go through this SQL Server Tutorial in YouTube. I found it very beneficial to recap some of the areas where I had minimal hands on experience. You can check out these Pluralsight Courses to get detailed walkthrough on the various areas:

• SQL Server 2012 Querying (70-461) Part 1
• SQL Server 2012 Querying (70-461) Part 2
There is lot of information about this exam in the Microsoft Learning Page. Make sure to check it out. You can install SQL Server Management Studio 2017 here. It is an independent web installer and is decoupled from the underlying SQL Server engine.

Online Proctored Certification Exam

You have the option of taking the certification exam from the nearest Certification center or even from the comfort of your home or office. If you decide to take the Online Proctored exam, like I did, you will be monitored by an offsite proctor via your webcam and microphone. Make sure that you review the system requirements ahead of time and take the available system test from the same computer before the exam day. On the exam day, ensure that your room is distraction free. Keep in mind that during the exam you cannot leave your desk, or else your exam will be canceled. Make sure that you log-in 30 min before the exam scheduled start time so that you have 15 min for the initial verification and launch process by the offsite proctor. In my case, I was put in a queue for 10 min before I was able to get a proctor.

Microsoft Certification Badges

Microsoft has partnered with Acclaim and will provide you a Certification badge after passing the exam. Acclaim provides an easy way to manage and share your Microsoft Certifications. Having a digital badge for your certification is always helpful since you can embed it in your resume, your personal website or even an email. Not to forget to share the badge online across different social media platforms. This is a completely free service provided by Microsoft. You can find additional information about it here.

Conclusion

Querying Microsoft SQL Server 2012/2014 validates the skills required to create database objects, work with and modify data, and finally troubleshoot and optimize queries. I had a great learning experience while preparing for this exam. There are numerous benefits of earning a certification and advancing your career by acquiring comprehensive technical knowledge tops the list. Good Luck on your certification exams and do let me know in case you have any questions. I would be happy to help.