Junior SQL Server developer interview questions
Written by: Jeremy kadlec
From:
Http://www.mssqltips.com/tip.asp? Tip = 1644
Problem
We are in the process of trying to hire
Junior SQL Server developer to work on a few of our projects. What
Expectations shocould we have on their skill set? What are some fair
Questions to ask them during the interview process? With a junior
Level developer, what shocould be the depth and breadth of the questions?
Solution
Hiring a junior SQL Server developer is
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
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 shocould 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 shocould 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
Few core SQL Server developer areas.
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
Existing order header ).
- They logically document the relationships
Between tables by showing how all data relates to each other.
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
Shocould 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
Column starts with and the increment is the value by which the identity
Grows. in duplicate 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
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 written lidated 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 particle columns in a table.
- Checksum-the checksum as a integer value for a single row or for particle columns in a table.
- Checksum_detail-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
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
Table does not have a clustered index it is referred to as a heap.
- To further clarify this lets take a look
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 cocould be either a clustered or
Non-clustered index. without having an index SQL Server wocould need
Read through all of the data in order to find the rows that satisfy
Query. If you have ever looked at a query plan the difference wocould 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
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, sorting, 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)
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
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
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 shoshould remain visible while scrolling" checkbox in
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
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
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 conducting
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
Shine.
