SQL Server T-SQL Functions Interview Questions
September 23rd 2008 06:07
SQL Server 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. The questions are categorized as either easy, moderate or difficult with the question available to read, but the answers are hidden to challenge yourself. Once you have your answer formulated then highlight the answer to see how you have done. Good luck!
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.
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
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.
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 )
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
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.
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.
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
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.
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
Problem
In the latest installment of the MSSQLTips interview question series we are going to cover questions on T-SQL functions. The questions are categorized as either easy, moderate or difficult with the question available to read, but the answers are hidden to challenge yourself. Once you have your answer formulated then highlight the answer to see how you have done. Good luck!
Solution
Question Difficulty = Easy
Question 1 - True or False - T-SQL is the only programming language in SQL Server with 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.
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
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.
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 )
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
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.
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.
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
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.
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
| 60 |
| Vote |
Add To: 
