Contents:
This section is dedicated to SQL Server interview questions and answers and because this subject is very broad at the beginning I will provide several links to subject specific SQL Server related interview questions and after that I will provide Generic SQL Server questions and answers. Please be aware that the best way to finish the interview is to test candidate's knowledge by performing a test. This is crucial it will truly test candidate's knowledge, level of efficiency, ability to analyze, understand tasks and work under pressure, don't leave it to chance. Perform proper test; it is worth your time.
Below are interview questions and answers categorized into different areas. If you are interested in SQL Server specific questions please go to the bottom of the page or click here
Core SQL Server interview questions and answers
SQL Interview questions and answers - Here we provide questions to SQL language (query skills) which is the core skill in SQL Server.
SQL Server Business Intelligence questions and answers
BI Interview questions and answers In BI we will cover main concepts without going into technical questions which are covered below.
SSRS Interview questions and answers In SQL Server Reporting Services (SSRS) we cover mainly development of reports, using expressions, filters, actions and so on. We also briefly speak about related skill data visualization.
SSIS Interview questions and answers SQL Server Integration Services (SSIS) is very good ETL tool and we cover development, data quality, business rules and briefly data warehouse concepts.
SSAS Interview questions and answers (coming soon) SQL Server Analysis Services (SSAS) we will ask cover development cycle, which will include dimensions, measure groups, cubes, dsv, calculated members and so on. For query specific questions please see MDX link.
MDX Interview questions and answers (coming soon) Multi Dimensional Expressions (MDX) is very powerful language and also not so easy sometimes to grasp so we will cover understanding which is important with this language and we will ask how to create certain queries.
As SQL Server is very broad I will provide here questions that will relate to different areas and will provide comments where this specific knowledge is applicable
Question: How much experience do you have in writing views, stored procedures and functions and when would you use them and when would not use them + one complex example of each one?
Answer: SQL Server Developers should answer that very well. Here is an answer example: Views allow to "join" tables/views which simplify development as they can be re-used, however multiple layers of views create dependancies. Stored procedures allow to re-use "logic" by passing different parameters which is different to re-using "joins (views)" they also simplify security as stored procedure has access to all objects within it so users just need to be given execute rights to SP, one draw back (misuse) is that trying to re-use Stored procedures like views is not very efficient as it will return ALL fields hence it can potentially do much more work than necessary. Functions allow to simplify calculation and can be re-used however they should not perform queries inside them as they are very inefficient (it is executed for every row), exception to that are for instance table values function which return "recordset".
Question: How much experience do you have in Microsoft SQL Server BI Tools: SSRS, SSIS, SSAS and when would you use them + examples.
Answer: This is most suitable for SQL Server BI Developers. SSRS is a quite powerful reporting tool used to deliver reports and dashboard but also allows user to create their own reports (report builder) and subscribe to reports (XLS, Emails for instance). SSIS is used to extract data from multiple source, perform transformations (apply buisness rules) and load it into specific place or central data storage (data warehouse). It is very often used to create "master" data (data warehouse) by extracting data from multiple systems, perfoming data cleansing (very often), apply specific rules and loading the data into a model (usually dimensional mode) so users can use it without having any technical skills (cube + excel for instance). SSAS (cube) is used to load vast amounts of data into separate storage where data is aggregated and structure in a way that results in fast response to queries, tracks history and gives access to many useful functions that speed up development of complex queries.
Question: What do you know about indexes?
Answer: This is a very broad question so I will try to keep it short. Indexes are the most common object which allows to improve query performance. Proper index allows to perform "seek" which is kind of like looking for specific word in a book. At the back of the book you have index where you can find a specific word and page number where this word appeared. If you didn't have that and you wanted to make it very inefficient you could read the entire book? Reading entire book is kind of equivalent of table scan (as opposed to seek). There is more to that but I think that describe main goal of index. The candidate should also mention and explain clustered index, non-clustered index, "include" option, give examples when index is not used (different collation, data types, functions) and if interested in latest version of SQL Server also mention the new SQL Server 2012 index "column based index".
PDF Download - ebook
I will make this page avialable in PDF format for download (ebook) soon.