Systems Engineering and RDBMS

Some SQL Server Interview Questions

Posted by decipherinfosys on November 18, 2008

While helping a client of ours to come up with a list of questions for hiring their Senior SQL Server DBA, we presented these questions to them in order to help them picking up the right candidate. Please note that these were in line with their needs – depending upon your needs, the questions can be a bit different like this client does not use SSIS, SSRS extensively and neither do they use SSAS so you won’t see many of those questions in here:

  1. Explain the differences between the recovery models and when you would choose a specific recovery model over the other.
  2. When tuning a particular query, you see that the “Bookmark Lookup” is one of the most costly operation (in SQL Server 2005, this is done via a clustered index seek operation and a RID (Row ID) look up). Explain Bookmark lookup and then explain how you would tune the query.
  3. Explain the difference between a temp table and a table variable – discuss in terms of scope, performance, programmability and where it resides.
  4. Explain how you can pass an array into a stored procedure.
  5. If you use functions on an indexed column, will the index get used? Why or why not. What are the potential solutions to resolve any such code issue.
  6. Explain the user/schema separation in SQL Server 2005 and how you can use it to implement security.
  7. When you use an aggregate operation, how are the NULL values treated? So, if there are three records with these values in a column: NULL, 2, 4, then what would be the result of AVG(col1)? Will it be 2 or 3?
  8. Typically, parallel execution plans are not desired in an OLTP application – if you see an execution plan going parallel, what are the things that you can do to help tune the query?
  9. Describe how you can integrate the results of perfmon and profiler to help troubleshoot an issue.
  10. Explain the difference between RAID 5, RAID 0+1 and RAID 1+0. Discuss how you will layout the system and user database files to achieve optimal performance.
  11. I have a query which uses the index view when I run the query in SSMS, however when the same query runs through the application, we see that the indexed view is not being used. What could be potential reasons?
  12. Describe different ways to load data in and out of a SQL Server user database and rank them in the order of speed.
  13. Describe different ways to copy a database from one instance to another. Cover what all things one needs to be careful about when doing such operations.
  14. Discuss different High Availability and DR solutions for a Windows/SQL Server Enterprise Edition implementation.
  15. Discuss the usefulness of configuration files in SSIS.
  16. Discuss the implementation of transactions, logging and looping in a SSIS package.
  17. Discuss the tuning methodology using waits and queues. Mention some of the common ones that you have seen in your tuning efforts and how you have resolved those issues.
  18. Explain the difference between a Primary Key and a Unique index.
  19. When you have a recursive many to many logical relationship within an entity, how many tables will you end up with? Take an example of the DRUG entity in a pharma model – One drug can be comprised of many drugs and vice-versa.
  20. Explain the difference between NOT IN and NOT EXISTS – first discuss whether there is any functional difference and then discuss performance differences.
  21. Explain the difference between UNION and UNION ALL.
  22. Explain the difference between Snapshot and Read Committed Snapshot Isolation levels.
  23. Explain what are Orphaned Sessions and Orphaned Users – how you will detect them and how you will resolve the issues arising because of them.
  24. Discuss why it is a good idea to index the foreign keys (besides faster joins).
  25. Discuss the Analytic functions in SQL Server – when would you use ROW_NUMBER() vs DENSE_RANK() ?
  26. How do you do database sizing and projections for database growth?
  27. What are some of the limitations of a UDF in SQL Server?
  28. Discuss how you do error handling in T-SQL code (when the candidate starts discussing TRY-CATCH among other things, ask for the caveats).
  29. Index design – when would you prefer a non clustered index over a clustered one for the Primary Key?
  30. What is a covered index and when is it beneficial? Discuss included columns as well.
  31. Explain what are histograms and how they can be used in tuning.
  32. Discuss the benefits of using Database Mail over SQL Mail (you guys do not use it right now but if the alerting piece is to be built for automated notifications, this can be used).
  33. Discuss SQLDiag – how is it useful for troubleshooting issues.
  34. If the perfmon counters for SQL Server are missing on a machine, what could be the potential reason?
  35. Can TOP (N) be used with updates and deletes as well or only with Selects? Is it better to use TOP(N) as compared to SET rowcount?
  36. Discuss how you would look for fragmentation in database objects and how you go about fixing it.
  37. Out of scan density, extent fragmentation and logical scan fragmentation, which ones are more critical values to watch out for?
  38. Discuss the performance benefits of a 64-bit install over a 32-bit install.
  39. Discuss how you would configure AWE.
  40. Discuss the usage of the OUTPUT clause in DML operations.
  41. When using database snapshots, what are some of the best practices?
  42. When doing a Left Outer Join, does it matter whether the filter condition on the outer table is in the join condition vs the where clause?
  43. In a composite index, discuss the rules when designing the column order in the index.
  44. Discuss Lock escalation vs Lock conversion. How can you capture whether you are having lock escalation issues in your system and what can be done to minimize it in an OLTP environment.
  45. Discuss some of the ways to write a recursion query in T-SQL.
  46. What is the COPY_ONLY option do in the backup process of a database? Why is it useful?
  47. Discuss the different backup and restore strategies for VLDBs (Very Large Databases).
  48. When can a database be marked as suspect and how can that be fixed?
  49. Explain different ways of collecting statistics on the objects in SQL Server – how can you monitor whether stats need to be re-computed?
  50. Discuss plan guides and a scenario where they can be useful.
  51. Explain the difference between indexes and statistics.
  52. What is a synonym and how it is useful for distributed applications.
  53. Discuss the different types of time outs – Session, Command, Connection, Transaction, Lock time out, Remote login time outs and timeouts due to query wait and query governor cost limit settings).
  54. Discuss filegroups and their benefits.
  55. Discuss the different modes of security in SQL Server.

This is not a comprehensive list of the questions but just some of them that you can use for your own purposes. If you have any questions about any of these, please feel free to contact us at: info@decipherinfosys.com and we will provide you the answer to those questions. In the future, we will add more questions to this list as well as create more blog posts for these questions – some of these questions are already covered through various blog posts on our site. And we will post questions for other areas as well like Oracle, virtualization, System Engineering, Performance tuning, capacity planning etc.

3 Responses to “Some SQL Server Interview Questions”

  1. CB said

    This is a really nice list you have comprised here. Its always hard to plan the perfect interview. (In this economy, it is also valuable for anyone seeking employment to practice their answers to interview questions.) Thanks!

    • Hi CB,

      Answers to many of these questions are available on our blog. One thing that we haven’t gotten around to yet is plugging in those links right next to each of those questions. It in on the list of things to do…

  2. Dioscoredes said

    By way of a confession. I have been an Oracle DBA for over 22 years and SQLServer since 6.5. Even though with Oracle and MSC qualifications, I am always troubled by my lack of “formal” knowledge as required by such very apposite interview questions you have listed. I understand the RDMBS “game plan” well but only retain just those techniques needed to do the job. Sites like “ask tom”, SQLSERVER central and google are all I need. Haven’t made a vendor service request call for years – (apart from Oracle RAC!) The Pareto relationship applies to techniques required and situations encountered. Eg never encountered a SQL db marked suspect (just doesnt happen in Oracle) but use stats and plans and sys. tables all the time. Personally I pitch my interview questions at the “fat part” of the Pareto distro with the odd curved ball as a test of nerve and ingenuity. The guys are not doing an exam, they are joining a team and I want to know how they will fit in. Is more important than pat responses and brute knowledge. However, I am going to pick through your list with much interest and use it to sharpen the cutlery at the same time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: