Interview Questions for junior SQL server developers

Source: Internet
Author: User
Tags sql server query ssis

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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.