Kimberly Tripp spends a lot of time fixing poorly architected databases. As a result, she’s learned to appreciate developers who know how to customize the appearance and behavior of SQL to meet each client’s needs. 
“Every environment is different,” said Tripp, president and founder of Bellevue, Washington-based training and consulting firm SQLskills. “Although there are some best practices like never turn on auto shrink, rock-star developers know there are multiple ways to design and create an SQL database.” Click here to find SQL-developer jobs. Tripp asks some of the following interview questions to assess an SQL developer’s ability to change and adapt to any situation: Why would you use a stored procedure?
- What Most People Say: “Using precompiled code stored in the database improves performance.”
- What You Should Say: “While using precompiled code can be beneficial, it’s not always so. For instance, it can lead to parameter sensitivity and/or sniffing problems. I like to use centralized reusable logic so that I only have to change the code in one place. And I often use stored procedures to create a security layer between the users and data/database objects, reducing the need to use ad hoc queries or direct data modifications.”
- Why You Should Say It: Acknowledging that precompiled code is not always best, and that it can lead to performance problems, is the sign of a real pro with significant experience, because performance problems occur quite frequently with stored procedures in the real world. While inexperienced SQL developers erroneously expect administrators to find and fix performance problems, a seasoned developer will know how to design, test, and tune stored procedures for scale.
By the way, demonstrating the breadth of your experience with stored procedures in diverse environments is a great way to set yourself apart during an interview.
Upload Your ResumeEmployers want candidates like you. Upload your resume. Show them you're awesome.
Who’s responsible for testing and resolving performance and scalability issues?
- What Most People Say: “Administration and operations are responsible for testing the database. Because you never know how the code will perform until it’s in production.”
- What You Should Say: “Although you can’t foresee every glitch or issue until the database is in production, as a developer, I like to elicit a user’s expectations, data volumes and usage patterns from the outset. That way, I can create a realistic data sample and test the code to see how the database will perform and initiate corrections before it’s released.”
- Why You Should Say It: “The first answer is a cop out,” Tripp said. “It’s a developer’s job to understand how data selectivity and distribution will affect the way the code performs… You can nip major performance and scalability issues in the bud by practicing test-driven development and unit-testing a realistic data sample.”
What are some of the reasons why the estimated and actual rows in SQL Server may vary?
- What Most People Say: “It’s got to be statistics. Right?”
- What You Should Say: “Statistics could account for the variance: Failing to maintain or update SQL Server statistics can cause differences in row estimations. But it could also be more complicated cardinality estimation problems or parameter sniffing problems. I would look for clues in the query plan output. I would also investigate which version of SQL Server they’re running and their database compatibility mode in case the problem stems from cardinality estimation model changes.”
- Why You Should Say It: Being able to read execution plans and compare estimated rows and actual rows is a great start to identifying problems. Differences in the numbers can mean statistics are out of date, or that the code may need to be changed, because the parameters vary enough that the compiled plan doesn’t work well for the executing values (this is the real crux of parameter sniffing).
The second answer also shows an understanding of data distribution and plan caching; it demonstrates an in-depth understanding of SQL, and the ability to diagnose and resolve problems that arise in a variety of situations. Rock-star SQL developers know that there’s always more than one way to design a database, code its procedures, and tune the workload.
