Featured Stack Exchange answer!

As many people I talk to about the pursuit of knowledge know, I advocate for a minimum of learning three new things per day. So typically I’ll subscribe to SQL newsletters, podcasts, blogs, and any other medium of information I can find to learn as much as possible. Well, I was really surprised when I found out an answer I posted on Stack Exchange was featured in Brent Ozar’s newsletter!

Here below is the newsletter: (I have a feeling the link may break in the future though!)
Ozar Newsletter

So here’s a screenshot of the page:

And here’s the answer I had posted!

I just thought it was really interesting and neat to pop up randomly in a newsletter I’ve been reading for 7+ years and I had to share it.

My study guide to the 70-762: Developing SQL Databases

I couldn’t find a spreadsheet that would help track the topics, ETA, and other notes online, so I decided to compile all of my resources into a singular spreadsheet to help me keep track of studying and to help the community for those taking the 70-762. You can copy / make a copy of this and track yourself.

Spreadsheet here!

The tab Topic Resources has been taken from Daniel Calbimonte and his post on MSSQLTips. Huge thanks to him for taking the time to compile all of those resources on those topics!

In the Additional Resources, I’ve listed out links to things I thought are incredibly beneficial to my studying of the 70-762. This also lists out a Time to Study table where you can average how many hours of studying you plan to do on certain days of the week and then it gives you an estimate of the hours necessary to study based on what you estimate each topic will take you to learn. I’ve already filled in the hours for an estimated optimistic and pessimistic goal, as you fill in the actual hours worked you’ll notice it will adjust for actual time spent instead of only estimated time.

The Topic and Hours page lists out the topics as per the test page from Microsoft and breaks it out into a table that you can track hours you spend in each topic. This gives you a manageable approach to figuring out how much time this will take you to complete. I’ve filled in my own estimate of the optimistic and pessimistic guidelines, so feel free to adjust this as you need to match your own knowledge. I figured I would want at least 30 minutes to a hour on each topic even if it’s a brief overview just to refresh myself.

The last page is the planner. This is where I keep track of the date I studied, the high / mid / detail level topics I worked on, the amount of time I worked on it, and there are two extra fields. One for notes if there is something I need to come back to or perhaps I found some additional resources I want to touch base on later. And then there’s the Other Topic column. I’m still reading through the Internal Pros book, so when I log time it’s specific to reading that book rather than any of the categories I’ve listed. I decided when I begin working on the 70-762 study guide I’ll begin filling in time on the topics and making sure I understand the topics to meet the detailed level of the topics listed by Microsoft. This will make for an interesting PowerBI graph later to see how my time was spent studying. Maybe I can use that data to study more efficiently for 70-767!

Happy studying and see you on the other side!

Review of Itzik Ben-Gan’s Books

I’ll start by saying this isn’t a paid review nor do I have any association with SolidQ publishing.

T-SQL Fundamentals (third edition) was highly recommended as a read for the 70-761 exam along with his other two books, T-SQL Querying and Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012.

Looking at the three books, you can gather the fundamentals book is probably more base skill set and the exam is structured for obviously an exam. So are all three books worth a read? Which ones should you read and in what order?

The 70-461 book is unbelievably deep and well written. While it is ultimately meant to help pass an exam, I think you can take a lot of practical application from it. That said, I think if your goal was not to pass the 70-461 exam, the T-SQL Fundamentals and T-SQL Querying books would be a fine read on their own and you could pass up the exam book. If you had the training exam available for you to read for free however, I would definitely recommend to at least cherry pick the chapters you felt needed additional attention and study time!

T-SQL Fundamentals is definitely the book I’d read first though. This is also the book I wish someone had told me I needed to go and read when I first began work in databases. It touches on the basic key concepts for SQL Server then it gives practical examples and application use. The code is easy to follow and expertly written, no word is wasted and it’s extremely well edited. If you are a manager of junior to intermediate data professionals who primarily use SQL Server, you should make time for them to read this book. (Just my humble opinion.) This is what I’d consider to be, the single best compilation of the must need to know basics that any T-SQL developer will use. If you are on the senior end, it’s a fun read and you may re-remember some lost tidbits or perhaps you had a few holes in your knowledge that you needed patched. I still think it’s worth the read, including the other exam book. But this is definitely geared to the junior to intermediate folk with splatterings of expert advice.

Comparing to T-SQL Fundamentals, T-SQL Querying does not hold back, it’s a tome of information. This would be the book I’d want my team of senior SQL Server data professionals to read. T-SQL Querying may be too advanced for junior to intermediate readers however. I would recommend to try and read through samples or page through it in the store prior to purchasing. It’s a big book and extremely in depth with amazing detail. If you are in a senior SQL role and primarily use SQL Server, this is a great book for you. I’d recommend the Fundamentals first to make sure the first base layer of knowledge is in place, but it is skippable if you have enough experience. The exam booklet would be a great secondary read, otherwise I’d read T-SQL Querying last.

Passed the 70-761 exam, Querying with Transact-SQL!

Hurray, I passed 70-761! That means you can too!

I just thought I’d share briefly how I passed the exam. As a note, the below is how I studied and what I studied, it may or may not be what was on the exam as they change the exams and I cannot discuss what was on the exam directly. Nor can I answer any specifics to the exam, you can find all of this information on Microsoft’s website. I am going to share however, how I got to the point to where I felt comfortable taking and then passing the exam.

I thought the 70-761 was hard but fair, it was both easier and more difficult than I expected it to be. There were three key aspects for me that I’ll apply for the 70-762 exam that helped me to succeed. Figuring out when to schedule the exam, how to study for the exam, and how I should think and answer the questions during the exam.

To schedule the exam, I broke out every topic covered in the description of the test and decided between the things I needed to learn, review, and skip. I then also broke down how much reading and how long each reading session would take. I gave myself an estimated time period for each of the topics I had to learn/review and kept a notebook tracker with the topics, days until the test, and how much reading was left. Based on how much time I thought it would take to learn, I allocated myself 30 minutes of time to study per day and divided by total time I thought I would spend studying by that, and scheduled the test out that many days. Some days I was able to study for 30 minutes while some days I couldn’t study at all. Other days like weekends, I could double down and put in a few hours at once. I would also record what I studied on what day with what resource and about how many hours I put into it.

Scheduling the exam got the fire under my rump to get me moving and motivation. If I didn’t spend the time studying, I’d walk out humiliated and short $160 with a longing regret of what I could have accomplished. So by finding the amount of time I needed and allocating that time for myself to study, I scheduled the exam for 50 days out for 2018-12-08! By actually scheduling the test, I was no longer able to procrastinate, it was now a priority.

On the same page of studying, make sure to study smart. Don’t spend two hours watching videos on joins when you have that down. Spend the time on things you are either unsure of or don’t understand. Topics that you are 100% sturdy on may not be bad to do a brief review to see if you missed anything, but I wouldn’t dedicate more time than reading a chapter in the 70-761 study guide.

For study material, I read the entire 70-461 exam prep book and then read specific sections in the 70-761 exam prep book. Especially on the XML / JSON / Temporal tables, I had heard you need to focus heavily on this from other forums. One note I’ve read is that Microsoft likes to really drill you on things that were new to the version of SQL you are testing for. So I focused heavily on studying things that were new from 2014 to 2016. I read most of and skimmed some chapters in the book T-SQL Fundamentals.

I used additional resources from Cathrine’s website  and MS SQL Tips . I reviewed the XML stairway series  on SQL Central and also watched every video that is recommended on the links I am providing.

JSON and Temporal tables were hard to find good resources for. I ended up reading the entirety of the MSDN tech notes on Temporal tables. I found learning XML and JSON the easiest by practicing problems I’d think of in Adventureworks, otherwise only reading the material did not stick as well. Anything I haven’t used regularly I made sure to read on, watch videos, and practice. I would also search stack overflow for problems associated to topics I wasn’t clear on and try to solve them or at least read up on them. If you aren’t feeling overly confident in an area, try looking for another online resource. If you can find sites that can test your knowledge in areas, that is a fantastic approach to realizing what areas you are lacking in information. Being able to explain something to someone is also a good indicator of how well you understand something.

I would also listen to podcasts and videos while running each morning, but I honestly don’t think that helped much for the test. For topics I found the hardest, I’d start by reading the prep chapter in the exam book. Then I’d look for some reputable and good videos on the topics, read an additional online blog or two, and then I would create practice problems. If I still felt unsure, I’d look for a podcast on the topic to listen to loosely during a free time like driving or running, and then I would also look for flash cards and quizzes online. The most beneficial study tool was using technology day in and day out, otherwise the 70-461 and 70-761 exam books were my bread and butter. Quizzes and flashcards helped me a lot to identify the areas I needed to focus on learning again.

Everyone else has said this about the test but I’ll say it too.

Use your test taking abilities.

If you can go back to a question that is taking you a long time, mark it for review and continue with the test and go back to it at the end. I used every second of the 2 1/2 hours of the exam. I don’t know if having more time would have necessarily changed my score, but I feel 30 more min would have made me felt more comfortable and less rushed.

If you can narrow down the answers you have to pick from, do that at the very least even if you are stumped by the question. From the narrowed down answers you can at least guess.

Don’t leave any answer blank and try to answer them all.

If I were to do it again, I’d probably do more flashcards on definitions of terms in my studying and will try that for 70-762. If I were really uncertain, I’d probably take the official pre-test. I would recommend the 70-461 book just because there is so much good information inside of it, I only bought the 70-761 because it had JSON and Temporal Tables. I’d suggest you could go with the 70-761 + T-SQL Fundamentals instead of the 70-461; or you can go with the 70-461 and the Temporal Tables section in the T-SQL Fundamentals while also adding some supplemental JSON training since that is lacking in the 461 exam book. My test will probably not be the same as you will have and you need to look over the content you can be tested on via the Microsoft site for the cert. Good luck and I’ll see you on the other side of 70-762 shortly!

My favorite resources:

https://www.cathrinewilhelmsen.net/2015/01/28/preparing-for-and-taking-exam-70-461-querying-microsoft-sql-server-2012/

https://www.mssqltips.com/sqlservertip/4644/sql-server-exam-70761-study-material-for-querying-data-with-transactsql/

https://www.amazon.com/T-SQL-Fundamentals-3rd-Itzik-Ben-Gan/dp/150930200X

https://www.amazon.com/Training-70-461-Querying-Microsoft-Server/dp/0735666059/ref=sr_1_1?ie=UTF8&qid=1513628070&sr=8-1&keywords=70%3D461

https://www.amazon.com/Exam-70-761-Querying-Data-Transact-SQL/dp/1509304339/ref=sr_1_1?s=books&ie=UTF8&qid=1513628083&sr=1-1&keywords=70-761

http://www.accelerated-ideas.com/70761-practice-test-questions.aspx#.WeeTWVtSypo

https://www.mssqltips.com/sqlservertip/4015/introducing-json-for-sql-server-2016/

https://www.mssqltips.com/sqlservertip/4073/sql-server-2016-advanced-json-techniques--part-1/

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-2016-temporal-tables/

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/