Microsoft introduced a number of productivity-boosting enhancements to T-SQL when it released SQL Server 2016, including several built-in string functions that can help developers write less code. Given all that, if you want to land a job as an SQL developer, you’ll need to show that you’re current with all updates, explained Doug Lane, an SQL expert, consultant and founder of SQL Theater. Since managers want to hear about the ways that you’ve used new SQL features to solve old problems and improve performance, Lane provided answers to interview questions related to SQL Server 2016 that may meet or exceed the expectations of evaluators.

"What is your favorite new feature in the latest version of SQL Server?"

Adequate Response: "I'm excited about the new language enhancements [or another new feature]." Better Response: "I love the new STRING_AGG function. In the past, we had to write XML or loops in order to get multiple table rows condensed into one line. I'm using the new function to concatenate email addresses and it's much simpler." Why You Should Say It: “Providing specific examples shows that you not only understand how a new feature has the potential to fix current problems, but how applying a new feature influences planning and architecture decisions.”

"Tell me about a time when your code blew up in production."

Adequate Response: "We did a code release and it slowed the server way down. The DBA rolled it back and we reverted to the old code until we could rewrite it." Better Response: "We had some code that worked well in test and pre-production, but we didn't check it against this one edge case. We identified that cause and I worked with the DBA to ensure it would perform well before releasing again." Why You Should Say It: While your exact answer may vary, hiring managers generally look for three things to determine if someone is a team-oriented, proficient developer:

• Do you take responsibility for your mistakes?
• Do you learn from them?
• Are you willing to collaborate with DBAs to solve problems?

"How do you measure improvement?"

Adequate Response: "I check the time it took for my code to execute." Better Response: "I collect metrics like CPU time, duration, reads, and executions. I can't always control duration, so I focus on reducing the amount of work SQL Server has to do. I also keep a log of metrics from before and after changes, so I can quantify how much better the query is after tuning." Why You Should Say It: Performance tuning is an essential skill. It’s also a good idea to mention the tools you use to collect and monitor performance metrics such as Brent Ozar's sp_BlitzCache, SentryOne Performance Advisor or Management Studio's time and IO statistics. What's important is getting the numbers before and after each iteration of the tuning process, and knowing enough about execution plans to see where improvements can be made. Bonus Tip: This is the perfect opportunity to highlight how you measure your own career improvements. For example, it's a plus to mention how many hours you've invested in sharpening your skills over the last year, and what you’ve learned.

"You recently upgraded to SQL Server 2016 from 2012 and now a query that was fast is running very slow. How can you get it to perform as well as it did before the upgrade?"

Adequate Response: "Change the database compatibility level back to 2012." Better Response: "It depends on how many other queries are affected and by how much. If it's just a few queries that are running a little slower, I might look at forcing a good plan if I can get one. I may also try using Trace Flag 9481 to force the query to use the old Cardinality Estimator. If queries as a whole are suffering for this database, I'd consider changing the compatibility level, but that's heavy-handed." Why You Should Say It: There’s nearly always more than one way to solve a problem in SQL Server. The interviewer wants to know if you understand the various options and the trade-offs. After all, you don't want to create more issues by making a change that has a wider footprint than the problem itself.

"Why can't you use a window function in a WHERE clause?"

Adequate Answer: "Because it will throw an error. You have to nest the window function in a subquery in order to reference it in a WHERE clause." Better Answer: "Because the order in which SQL Server logically processes clauses puts the SELECT after the WHERE. The window function is defined in the SELECT after the WHERE has already been processed. You have to get the window function evaluated before the WHERE. Putting the function in a subquery will accomplish that." Why You Should Say It: Virtually any experienced SQL Server 2016 developer has seen this error when trying to reference a row number in their WHERE clause. Developers need to not only learn how to mitigate errors, but also understand why those errors happen in order to avoid them. Having a deeper understanding of the query engine as a whole helps you write code that's less error prone and more efficient, starting from the first draft.