SQL Server Developer T-SQL Functions Interview Questions

Problem

In the latest installment of the MSSQLTips interview question series we are going to cover questions on T-SQL functions in SQL Server.  The questions are categorized as either easy, moderate or difficult.  Good luck and happy learning!

Solution

Question Difficulty = Easy

  • Question 1 – True or False – T-SQL is the only programming language in SQL Server with functions.
  • Question 2 – What are the positional functions in T-SQL and what value do they provide?
    • LEFT – Returns the left part of a character string with the specified number of characters.
    • RIGHT – Returns the right part of a character string with the specified number of characters.
    • SUBSTRING – Returns the portion of the string.
    • Additional Information – SQL Server 2000 Text Data Manipulation
  • Question 3 – What are the rounding functions in SQL Server and what value do they provide?
    • ROUND – Rounds a positive or negative value to a specific length.
    • CEILING – Returns the smallest integer greater than, or equal to, the specified numeric expression.
    • FLOOR – Returns the largest integer less than or equal to the specified numeric expression.
  • Question 4 – What are the two functions that can change the data type of a column or a variable for additional manipulation?
    • CAST and CONVERT
  • Question 5 – What is the function that will change the format of a date?

Question Difficulty = Moderate

  • Question 1 – True or False – All of the T-SQL functions are mathematical.
  • Question 2 – Name 5 or more string manipulation functions and their functionality.
    • CHARINDEX( findTextData, textData, [startingPosition] )
    • LEFT( character_expression , integer_expression )
    • LEN( textData )
    • LOWER ( character_expression )
    • LTRIM( textData)
    • PATINDEX( findTextData, textData )
    • REPLACE( textData, findTextData, replaceWithTextData )
    • REPLICATE( character_expression , integer_expression )
    • REVERSE( character_expression )
    • RTRIM( textData)
    • SPACE( numberOfSpaces )
    • STUFF( textData, start , length , insertTextData )
    • SUBSTRING( textData, startPosition, length )
    • UPPER( character_expression )
    • Additional Information – SQL Server 2000 Text Data Manipulation
  • Question 3 – Name 5 or more of the properties that can be used with the INDEXPROPERTY function.
  • Question 4 – What is a common use case for the OPENROWSET function?
  • Question 5 – What function can determine the number of rows inserted, updated or deleted?
    • The @@ROWCOUNT function can be used to determine the changes from the previous statement in stored procedure or script.

Question Difficulty = Difficult

  • Question 1 – True or False – The LEN() function returns the length for any data type.
  • Question 2 – With the new error handling paradigm in SQL Server 2005 and beyond what are the new system commands available?
  • Question 3 – What function can be used in a trigger to determine if a specific column was updated?
    • The UPDATE() function can be used in a trigger to determine if a column has been updated or not, then further logic can conditionally executed.
  • Question 4 – What is the difference between a deterministic and non deterministic function?
    • Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database.
    • Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
    • Source – SQL Server 2005 Books Online – Deterministic and Nondeterministic Functions
  • Question 5 – What are some of the functions that will assist in determining which user is executing a specific piece of code?
    • CURRENT_USER
    • HOST_ID
    • HOST_NAME
    • ORIGINAL_LOGIN
    • SESSION_USER
    • SYSTEM_USER
    • USER_NAME

Next Steps

  • As you prepare for a DBA or Developer interview be sure to have your bases covered by reviewing the SQL Server Interview Questions on MSSQLTips.
  • Functions offer a great deal of value to simplify and standardize T-SQL code.  Having a firm handle on the built-in and custom options across T-SQL, the CLR, Reporting Services, etc.
  • Do you have some common SQL Server interview questions that were not included in this tip that you ask as a portion of your interviews? Feel free to post them in the forum below.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *