Junior SQL Server Developer Interview Questions

Problem

We are in the process of trying to hire a Junior SQL Server Developer to work on a few of our projects.  What expectations should we have on their skill set?  What are some fair questions to ask them during the interview process?  With a junior level developer, what should be the depth and breadth of the questions?

Solution

Hiring a Junior SQL Server Developer is a good way to grow your team and bring some new blood, enthusiasm and ideas to the team.  Hopefully you will also have the time for the junior team member to grow on their own and with the senior team members.  In terms of expectations, it really depends on the candidate and your needs.  For example, with SQL Server Integration Services (SSIS), if they have it listed on their resume as being able to build, deploy and maintain SSIS packages, they should be able to easily know how to create a project, work with the data flow, control flow and event handlers as well as some of the SSIS widgets in the toolbox.  With that being said the depth and breadth of questions should be balanced between the candidate’s resume and your needs.  Let’s jump into some questions to see if we can get the ball rolling for you in a few core SQL Server Developer areas.

In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer below the question to see how you did. Good luck!

Data Modeling

• Question 1 – What is the importance of primary keys and foreign keys in a SQL Server OLTP database design?
  • Primary Keys
    • Uniquely identify a row in a table with one or more columns
    • Column values cannot be null
    • There can only be one primary key on a table
    • Candidate for either a clustered or non-clustered index to improve performance
      • Additional information – Finding primary keys and missing primary keys in SQL Server
  • Foreign Keys
    • They physically define the relationship between tables by preventing data integrity issues in your database (e.g. The database prevents line items from being created without an existing order header).
    • They logically document the relationships between tables by showing how all data relates to each other. To someone new to your organization, this allows him/her to get a good understanding of how the business works (e.g. Every order taken must have a valid customer assigned).
    • Foreign Keys are native to SQL Server and are designed to prevent data integrity issues. Business logic developers should not be in the business of verifying table relationships.
    • If defined and indexed correctly, they can be leveraged by the SQL Server query engine to generate extremely efficient query plans.
      • Additional information – The Importance of SQL Server Foreign Keys
• Question 2 – What is an identity?  What is the value?  How can you capture the last identity value per column?
  • An identity is a property of a column where an seed and increment are defined.  The seed is the value that the column starts with and the increment is the value by which the identity grows.  In many circumstances the seed and increment values are 1 which means that the initial value is 1 and the identity grows by 1.
  • The value of the identity column is that the relational engine manages the values so you do not have to write logic to manage the identity values in multiple locations in the programming logic.
  • SQL Server provides three different functions for capturing the last generated identity value on a table that contains an identity column:
    • @@IDENTITY
    • SCOPE_IDENTITY()
    • IDENT_CURRENT(‘tablename’)
    • Additional information – Properly Capturing Identity Values in a SQL Server Database

T-SQL Coding

• Question 1 – What is the difference between DDL and DML commands?  Can you name some examples of each?
  • DDL commands are data definition language commands.  Examples are CREATE, ALTER and DROP.
  • DML commands are data manipulation language commands.  Examples are SELECT, INSERT, UPDATE and DELETE.
    • Additional information – Category (T-SQL)
• Question 2 – How do you code error handling logic in your stored procedures?
  • Use the TRY and CATCH commands with BEGIN and END blocks.
    • Additional information – SQL Server 2005 Try and Catch Exception Handling
  • The error handling can be consolidated by creating a single stored procedure to write the data to a centralized table.
    • Additional information – Standardized SQL Server Error Handling and Centralized Logging
  • Use the RAISERROR command.
    • Additional information – Getting started with SQL Server stored procedures

SQL Server Functions

• Question 1 – Name 3 or more aggregate functions and the value they provide in your coding.
  • Average – Returns the average of the values in the select list ignoring the NULL values.
  • BINARY_CHECKSUM – The checksum as a binary value for a single row or for particular columns in a table.
  • CHECKSUM – The checksum as a integer value for a single row or for particular columns in a table.
  • CHECKSUM_AGG – Returns the checksum of the values in a table as an integer.
  • COUNT – Returns the number of items in the select list as an integer data type including NULL and duplicate values.
  • COUNT_BIG – Returns the number of items in the select list as a big integer data type including NULL and duplicate values.
  • DISTINCT – Not include duplicate values in the SELECT list.
  • GROUPING – The GROUPING aggregate is always used with a GROUP BY and either the ROLLUP or CUBE function to calculate the group’s value.
  • MAX – The highest value in the SELECT list.
  • MIN – The lowest value in the SELECT list.
  • SUM – The sum of all the values in the SELECT list which are numeric data types ignoring the NULL values.
  • STDEV – The standard deviation for all of the values in the SELECT list.
  • STDEVP – The standard deviation for the population for all values in the SELECT list.
  • VAR – The variance of the population for all values in the SELECT list.
  • VARP – The variance of the population for all values in the SELECT list.
    • Additional information – SQL Server T-SQL Aggregate Functions
• Question 2 – What are some options to randomly capture a value from a column in a table?
  • RAND() function
  • NEWID() function
  • TABLESAMPLE function
    • Additional information – Retrieving random data from SQL Server with TABLESAMPLE

Indexing

• Question 1 – What are the different indexing options available and what columns do you typically index?  What is the value of indexing columns?
  • From a simple standpoint SQL Server offers two types of indexes clustered and non-clustered. In its simplest definition a clustered index is an index that stores the actual data and a non-clustered index is just a pointer to the data. A table can only have one Clustered index and up to 249 Non-Clustered Indexes. If a table does not have a clustered index it is referred to as a Heap.
  • To further clarify this lets take a look at what indexes do and why they are important. The primary reason indexes are built is to provide faster data access to the specific data your query is trying to retrieve. This could be either a clustered or non-clustered index. Without having an index SQL Server would need to read through all of the data in order to find the rows that satisfy the query. If you have ever looked at a query plan the difference would be an Index Seek vs a Table Scan as well as some other operations depending on the data selected.
    • Additional information – Understanding SQL Server Indexing
  • Indexes are typically on these columns:
    • Primary keys
    • Foreign keys
    • Columns in WHERE, GROUP BY, ORDER BY, etc.
      • Single value, range of values, etc.
• Question 2 – Can tables be over indexed?  What are the performance implications?
  • Yes – Tables can be over indexed.  Indexes may be not used at all and may just be excessive storage.
  • The performance impact relates to unnecessary storage, but more importantly excessive operations by SQL Server when an INSERT, UPDATE or DELETE command is issued.  SQL Server needs to maintain all indexes even if they are not being used.
    • Additional information – Avoid Index Redundancy in SQL Server Tables and Deeper insight into unused indexes for SQL Server

Business Intelligence

• Question 1 – Name the three main tabs in the Visual Studio interface for SSIS Packages and the associated purpose.
  • Control flow – Logical flow from one piece of the package to the next.
  • Data flow – Database source and destination as well as data manipulation objects such as Merge, Lookup, Copy Column, Sorting, Pivot, etc.
  • Event handlers – Event based error handling.
  • Additional information – Category (SQL Server Integration Services)
• Question 2 – What are some of the Reporting Services best practices that you follow?
  • Report Property options:
    • SnapToGrid – For finer control of object sizes, set the SnapToGrid property to False. Once done, you can resize rows, columns, textboxes, etc. to more exact dimensions.
    • InteractiveSize – To achieve one long scrollable web page, change height property (expanc InteractiveSize) to 0. Note: With reports that are very long, this can adversely affect report rendering times. Experiment, use judgement, and obtain feedback from users. Educate your users as to the pros and cons of this.
    • PageSize – The default is 8.5in, 11in. This is a standard portrait letter size page size. For landscape printing, swap the width and height (to 11in, 8.5in). Coordinate these changes with the InteractiveSize. (i.e. If you want to print landscape, but see one long scrollable page in the web browser before printing, set the InteractiveSize to a width of 11in, and a height of 0.)
    • Description – With Reporting Services (RS) in Native mode (not Sharepoint Integration mode), text you write in this property is displayed in the Report Manager and the WSS2 Reporting Services report viewer web part. For RS in Sharepoint Integration mode the entry has no affect. (For Sharepoint Integrated installations, you can add a field to the reporting document library and put the description there.)
  • Table properties:
    • DataSetName – Got have one, even if you are doing something creative like displaying some sort of header table. Almost every report I create has a dataset called ‘header’. I use this with a table to display the report logo graphic and some identifying fields (i.e. report runtime, username, report title, etc). This dataset is just a placeholder without any real content. Sometimes I use select statements like “Select getdate()” or “Select 1”.
    • NoRows – Depending on data selection criteria and/or parameters chosen by users, your report may yield no results (no rows). (Or your backend server might be down…yikes!) This option allows you to display a custom message to users in the event there is no data to display. (i.e. “The parameters you chose contains no data…please change your parameter choices and try rerunning the report”.)
    • FixedHeader – Set to “True”, this is the same as the “Header should remain visible while scrolling” checkbox in the Table Properties pop up window. It’s really a slick feature.
  • Textbox properties within a table:
    • BackgroundColor – Background color of the textbox
    • BorderStyle – I prefer “Solid” most of the time
    • Color – Means font color
    • Format – Used for various number, percentage, date, text formatting. For Dates without time use ‘d’. For integers, use ‘N0′. For fixed decimals use N and then a number. ‘N4′ is a number with 4 decimals. Use ‘P1′ for a percentage with 1 decimal place. Check BOL for more formatting codes.
    • Visibility – Another favorite, but deserves it’s own tip. Another day…
    • CanGrow – ‘True’ most of the time, but I like to use ‘False’ if I don’t want lines to wrap. In that event, I add the field to the Tooltip in the properties window of the textbox.
  • In the Properties section:
    • RepeatOnNewPage – Does what it implies.
    • Visibity – Use for drill downs.
    • Additional information – SQL Server Reporting Services Development Best Practices

Next Steps

• If you are preparing for an interview as either an interviewer or interviewee, consider these questions as building blocks for the process.
• As is the case with all interviews, be sure to balance your needs with the candidates experience.  If you are looking to hire a junior level professional, be sure to keep that in mind as you conduct the technical interview.
• Make sure to balance the technical responses that a candidate provides with their intangible skills such as communication, aptitude to learn and dedication.  The candidates current skills may not always meld completely, but they may have the aptitude to learn and reap long term benefits for the team.  They just may need the opportunity to shine.