SQLChallenges for 2019
Each SQLChallenge will be released with a problem and solution video, ready to go! To get notified when a new SQLChallenge goes live, sign up at the bottom of this page.
Here’s my current list of released and potential challenges in 2019. I plan to publish a baker’s dozen (13), so there will be two in a single month. I still have more ideas than slots, so not all of these will make the cut. I reserve the right to add in new ideas and change the order of publication as inspiration strikes!
T-SQL: Tune the “Peak Year” Query (January 2019)
This is a query tuning problem. Who doesn’t like query tuning? Take the SQLChallenge here.
A Dynamic SQLChallenge (Feb 2019)
You need to write a single query loop that calls procedures across multiple databases in this SQL Challenge. What’s the most efficient way to do it? Take the SQLChallenge here.
Query Writing: Most Unique Names (March 2019)
Flex your T-SQL: you’re challenged to write four different (but related) queries in this query writing challenge.
Who Made That Schema Change? Create a DDL Trigger (April 2019)
In this challenge, you’ll get to take a crack at writing a DDL Trigger to track schema changes. We’ll cover why doing this can be useful for both developers and DBAs. Take the SQLChallenge.
Who Made That Schema Change? Create an Event Notification (May 2019)
This time, you need to use a different methodology to solve the schema-change-logging problem. We’ll cover the pros and cons of DDL Triggers and Event Notifications in this one. Take this SQLChallenge.
Make it Idempotent: The Rerunnable SQLChallenge (June 2019)
When writing TSQL, it’s often useful to make your TSQL idempotent — a fancy word, which in the case of databases really just means: you can run the command again and again and it will make sure that you have the intended effect, without causing problems by being run more than once. In this challenge, you’ll get multiple TSQL statements and be challenged to make them idempotent. Take the SQLChallenge here.
Planned / Possible Challenges
Git in There: a Version Control SQLChallenge
Get started with Git. In this SQLChallenge, you’ll be given some database code and challenged to complete a set of actions in your own Git repo.
T-SQL Writing: Compare Pivot vs CASE
Confession: I hated writing pivots for a long time. Now I finally like them. In this challenge, you’ll be tasked with writing a query two ways, and comparing the results to identify which query has the best performance. We’ll also talk about when you want to use pivot (or not), and why. (Note: these concepts also have problems in the homework in T-SQL School, but this challenge will be different examples and different code.)
T-SQL Writing: Use Unpivot with Pivot
What the heck is “unpivot”, and why would you want to unpivot and then pivot? Turns out, it’s actually kind of cool. In this challenge, you’ll find out why. (Note: these concepts also have problems in the homework in T-SQL School, but this challenge will be different examples and different code.)
T-SQL Writing: Recursion challenge
What’s the best performing way to write a query with recursion to solve this problem?
Tune a Nested Stored Procedure
Wait, these are different than “normal” procedures? Turns out they are, in a few important ways. Wrestle with them in this SQL Challenge.
Fix the Slow Insert
An insert statement is sloooooow against our sample database. Use the execution plan to track down why and speed up the insert.
Set up Transactional Replication
This isn’t everyone’s idea of fun — OK, it’s hardly anyone’s idea of fun. But it’s a good experience to have. NO, REALLY! We’ll also discuss the pros and cons of replication, and why it’s not going away anytime soon.
Restore to a Specific Point to Recover Data
For this challenge, you’ll get a set of backups and you are challenge to restore to a very specific point before data loss occurred, with steps to check your work. This should be easy, right? Right? (Spoiler: it will not be super easy.)
Challenge: Corrupt the Data
It’s surprisingly useful to know how to corrupt data sometimes. Makes testing high availability super interesting. In this challenge I’ll introduce you to a couple of tools that you can use to corrupt some data, then challenge you to corrupt some data in a way that the SQL Server will not detect.
Challenge: ‘Initialize’ the Database
You’ve got an automation scenario in this one: you need to set up tools so that when a database is created, it is “initialized” to a given configuration in relation to other databases on the instance. Your challenge is to write some custom code to get the job done.
Who gets SQLChallenges?
SQLChallenges are for existing subscribers only.