SQL Server Developer T-SQL Functions Interview Questions

By: Jeremy Kadlec | Read Comments (2) | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

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.

Related Tips: More | Become a paid author

Last Update: 9/22/2008

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Monday, December 20, 2010 - 11:18:24 AM - Ani	Read The Tip
With the new error handling paradigm in SQL Server 2005 and beyond what are the new system commands available?

Monday, December 20, 2010 - 12:05:39 PM - Jeremy Kadlec	Read The Tip
Ani, Great question.  I would check out these tips: http://www.mssqltips.com/tutorial.asp?tutorial=164 http://www.mssqltips.com/tip.asp?tip=1027 http://www.mssqltips.com/tip.asp?tip=1152 Thank you, Jeremy Kadlec

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor is the best SQL product on the market, by far. All of Idera's tools are great, but this is the icing on the cake!" It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. Need SQL Server help and not sure where to turn? Reach out to the Edgewood consultants for a Health Check. Get SQL Server Tips Straight from Kevin Kline. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Are you waiting on SQL Server? Learn about these DMV's.