Written By: Edgewood Solutions Engineers

Problem
If you are preparing for a SQL Server Developer or DBA interview as the interviewer or interviewee, today's tip should offer value to you.  This tip has interview style questions graded as either easy, moderate or advanced related to T-SQL, the relational engine programming language.  To help gauge your skills, the question is available to read, but the answer is intentionally hidden.  Once you have thought about your answer, highlight the answer and see how you have done.  Good luck!

Solution

Question Difficulty = Easy

• Question 1: Is it possible to rename a database?  If so, how would you rename the database?
  • Yes - Databases can be renamed in similar manners as other relational database objects.
  • It is possible to rename a database by one of these options:
    • Issue the sp_renamedb system stored procedure.
    • Issue the sp_rename system stored procedure and specify 'database' as the parameter.
    • Use Management Studio by right clicking on the database and selecting the 'Rename' option.
  • Additional information - Renaming SQL Server database objects and changing object owners
• Question 2: Please name 5 commands that can be used to manipulate text in T-SQL code.  For example, obtain only a portion of the text, replace a text string, etc.
  • CHARINDEX( findTextData, textData, [startingPosition] ) - Returns the starting position of the specified expression in a character string. The starting position is optional.
  • LEFT( character_expression , integer_expression ) - Returns the left part of a character string with the specified number of characters.
  • LEN( textData ) - Returns integer value of the length of the string, excluding trailing blanks.
  • LOWER ( character_expression ) - Returns a character expression after converting uppercase character data to lowercase.
  • LTRIM( textData) - Removes leading blanks. PATINDEX( findTextData, textData ) - Returns integer value of the starting position of text found in the string.
  • REPLACE( textData, findTextData, replaceWithTextData ) - Replaces occurrences of text found in the string with a new value.
  • REPLICATE( character_expression , integer_expression ) - Repeats a character expression for a specified number of times.
  • REVERSE( character_expression ) - Returns the reverse of a character expression.
  • RTRIM( textData) - Removes trailing blanks. SPACE( numberOfSpaces ) - Repeats space value specified number of times.
  • STUFF( textData, start , length , insertTextData ) - Deletes a specified length of characters and inserts another set of characters at a specified starting point.
  • SUBSTRING( textData, startPosition, length ) - Returns portion of the string.
  • UPPER( character_expression ) - Returns a character expression with lowercase character data converted to uppercase.
  • Additional information - SQL Server 2000 Text Data Manipulation
• Question 3: What are the two commands to remove all of the data from a table?  Are there any implications with the specific commands?
  • The DELETE command.
  • The TRUNCATE command.
  • In terms of implications, a few different issues could occur:
    • With the DELETE or TRUNCATE command, you will lose all of your data in a table.
    • A single DELETE command could fill up the transaction log if it is small and set to full recovery mode.
    • A TRUNCATE command could cause issues for Log Shipping since it is a minimally logged operation.
  • Additional information - Deleting Data in SQL Server with TRUNCATE vs DELETE commands
• Question 4: What are the three ways that Dynamic SQL can be issued?
  • Writing a query with parameters.
  • Using EXEC.
  • Using sp_executesql.
  • Additional information - Execute Dynamic SQL commands in SQL Server
• Question 5: True or False - SQL Server can format the date in over 10 different patterns.
  • True - With the CONVERT command can be used in 18 different patterns such as MM/DD/YY, MM DD, YY, DD-MM-YY, etc.
  • Additional information - Date/Time Conversions Using SQL Server

Question Difficulty = Moderate

• Question 1: What are the new error handling commands introduced with SQL Server 2005, what command did they replace and how are the new commands used?
  • The new commands are TRY and CATCH.
  • Although they do not directly replace any specific command, in many respects the TRY and CATCH has been used over the RAISERROR command.
  • The TRY block is for the business logic and the CATCH logic is for capturing the error.
  • Additional information - SQL Server 2005 Try and Catch Exception Handling
• Question 2: In what version of SQL Server were synonyms released, what do synonyms do and when could you make the case for using them?
  • Synonyms were released with SQL Server 2005.
  • Synonyms enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In short, this means that the original object that is referenced in all of your code is really using a completely different underlying object, but no coding changes are necessary. Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.
  • Synonyms can offer a great deal of value when converting underlying database objects without breaking front end or middle tier code.  This could be useful during a re-architecture or upgrade project.
  • Additional information - How and why should I use SQL Server 2005 synonyms?
• Question 3: How can you delete duplicate records in a table with no primary key?
  • Use the SET ROWCOUNT command.  So if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
  • Additional information - Deleting duplicate rows when there is no primary key on a SQL Server database table
• Question 4: In SQL Server 2005, what new functionality was introduced with the GO command?
  • The ability to issue a batch N number of times based on the value following the GO command.
  • In some respects this functionality replaces using a WHILE loop.
  • Additional information - Executing a T-SQL batch multiple times using GO
• Question 5: What are bitwise operators and what is the value from a database design perspective?
  • The bitwise operators in SQL Server are:
    • & (Bitwise AND)
    • ~ (Bitwise NOT)
    • | (Bitwise OR)
    • ^ (Bitwise Exclusive OR)
  • From a data modeling perspective, bitwise operators can be used to store complex set of criteria as a single value as opposed to having numerous lookup tables or numerous columns used as a 'flag' or condition indicator.
  • Additional information - Using SQL Server Bitwise operators to store multiple values in one column

Question Difficulty = Advanced

• Question 1: What two commands were released in SQL Server 2005 related to comparing data sets from two or more separate SELECT statements? 
  • INTERSECT - Final result set where values in both of the tables match
  • EXCEPT - Final result set where data exists in the first dataset and not in the second dataset
  • Additional information - Comparing Multiple SQL Server Datasets with the INTERSECT and EXCEPT operators
• Question 2: How can you capture the length of a column when it is a Text, NText and/or Image data type?
  • Use the DATALENGTH command to capture the length.
  • The LEN command is invalid for Text, NText and Image data types.
  • Additional information - How to get length of data in Text, NText and Image columns in SQL Server
• Question 3: Is it possible to import data directly from T-SQL commands without using DTS or SSIS?  If so, what are the commands?
  • Yes - Six commands are available to import data directly in the T-SQL language.  These commands include:
    • BCP
    • Bulk Insert
    • OpenRowSet
    • OPENDATASOURCE
    • OPENQUERY
    • Linked Servers
  • Additional information - Different Options for Importing Data into SQL Server
• Question 4: What is the native system stored procedure to issue a command against all databases?
  • The sp_MSforeachdb system stored procedure accepts the @Command parameter which can be issued against all databases.  The '?' is used as a placeholder for the database name to issue the same command.
  • The alternative is to use a cursor to process specific commands against each database.
  • Additional information - Run The Same SQL Command Against All SQL Server Databases
• Question 5: From a T-SQL perspective, how would you prevent T-SQL code from running on a production SQL Server?
  • Use IF logic with the @@SERVERNAME function compared against a string with a RETURN command before any other logic.
  • Additional information - Preventing T-SQL code from running on a Production SQL Server

Next Steps

• As you prepare for your interview, be sure to cover the topics you expect to be asked about.
• The areas to cover should not only be technical areas, but also consider your soft skills and what you bring to the team.
• Check out the following tips on MSSQLTips.com
  • MSSQLTips.com Category: T-SQL
  • MSSQLTips.com Category: Interview Questions