SQL Server Developer T-SQL Functions Interview Questions


By:   |   Updated: 2008-09-22   |   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.
  • 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.
    • 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.
  • 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.


Last Updated: 2008-09-22


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, December 20, 2010 - 12:05:39 PM - Jeremy Kadlec Back To Top

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


Monday, December 20, 2010 - 11:18:24 AM - Ani Back To Top

With the new error handling paradigm in SQL Server 2005 and beyond what are the new system commands available?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools