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:
- 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
- False - Below outlines the types of T-SQL functions:
- 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
- With the new TRY and CATCH commands, the following system functions are 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.
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