SQL Server Developer T-SQL Functions Interview Questions

By: Jeremy Kadlec   |   Comments (2)   |   Related: More > Professional Development Interview Questions Developer

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.
  • False - These SQL Server languages have functions:
    • XQuery
    • SQL Server Integration Services
    • Common Language Runtime
    • Reporting Services
    • MDX
• 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?
  • CONVERT
  • Additional Information - Date/Time Conversions Using SQL Server

Question Difficulty = Moderate

• Question 1 - True or False - All of the T-SQL functions are mathematical.
  • False - Below outlines the types of T-SQL functions:
    • Manipulate strings.
    • System functions.
    • Dynamic Management Functions.
    • Security functions.
    • Cursor functions.
    • Meta data functions.
    • Ranking functions.
    • User defined functions can be created in T-SQL or the SQL Server CLR.
  • Additional Information - SQL Server 2000 Text Data Manipulation
• 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.
  • IndexDepth
  • IndexFillFactor
  • IndexID
  • IsAutoStatistics
  • IsClustered
  • IsFulltextKey
  • IsHypothetical
  • IsPadIndex
  • IsPageLockDisallowed
  • IsRowLockDisallowed
  • IsStatistics
  • IsUnique
  • Additional Information - Retrieving SQL Server Index Properties with INDEXPROPERTY
• Question 4 - What is a common use case for the OPENROWSET function?
  • Import from Excel to SQL Server or export data from SQL Server to Excel.
  • Additional Information - Export data from SQL Server to Excel
• 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.
  • False - The LEN() function will return an error message for Text, NText and Image columns in SQL Server.
  • Additional Information - How to get length of data in Text, NText and Image columns in SQL Server
• Question 2 - With the new error handling paradigm in SQL Server 2005 and beyond what are the new system commands available?
  • With the new TRY and CATCH commands, the following system functions are available:
    • ERROR_LINE
    • ERROR_MESSAGE
    • ERROR_PROCEDURE
    • ERROR_SEVERITY
    • ERROR_STATE
  • Additional Information - SQL Server 2005 Try and Catch Exception Handling and Standardized SQL Server Error Handling and Centralized Logging
• 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.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor’s degree from SSU and master’s from UMBC.

View all my tips