SQL Server Developer T-SQL Interview Questions

By: Jeremy Kadlec | Read Comments (9) | Print Jeremy is a co-founder of MSSQLTips, a SQL Server MVP and a regular contributor with over 200 published tips. Related Tips: More

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.  Check out the questions and answers to help gauge your skills.  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 since it is a single transaction.
    • 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 there are over 15 different date formats 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 and beyond? What command did they replace?  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
    • Standardized SQL Server Error Handling and Centralized Logging
    • Using the NOWAIT option with the SQL Server RAISERROR statement

• 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 database design perspective, bitwise operators can be used to store a 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 SQL Server Integration Services?  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

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Friday, March 07, 2008 - 7:16:24 AM - aprato

One thing about TRUNCATE.... truncate is actually a minimally logged command.  Page deallocations are recorded.  It's possibel to recover from a TRUNCATE if it's done quickly.  Here's an example   create table mytable (id int identity) go insert into mytable default values insert into mytable default values insert into mytable default values insert into mytable default values insert into mytable default values go begin tran tran1 truncate table mytable rollback select * from mytable

Tuesday, March 18, 2008 - 2:40:44 AM - Bals
no

Tuesday, March 18, 2008 - 1:09:54 PM - aprato

Of course it can.  Page deallocations are recorded.  From the 2005 BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/3d544eed-3993-4055-983d-ea334f8c5c58.htm  TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.   There's no guarantee it will work but if done quickly (like, right after the table is truncated as in the sample I supplied) it can actually be done.

Friday, April 04, 2008 - 7:33:35 AM - admin
aprato, Agreed TRUNCATE is a minimally logged operation as well as under the code scenario you provide and/or with vendor tool the data can be recovered.  Thank you for pointing out those items. Thank you, The MSSQLTips.com Team

Tuesday, April 08, 2008 - 3:26:22 AM - Bals
Aprato, Yeah Agreed . Thanks for your valuble info

Thursday, March 17, 2011 - 12:36:30 PM - John Fox

While it's great to ask technical questions, interviewers are well served finding someone who is smart and understands concepts (i.e. truncate v/s delete) rather than regurgitating something they may have studied up on before the interview or can find in a manual with a few minutes worth of effort. Many commands are a click away by doing them in SqlManager, and then creating a script (which is a better practice anyway, scripts are repeatable, clicking is not.) If someone in an interview can't answer a question, the correct follow up is 'how would you find out how to do it' or 'where would you get that information' or 'have you ever needed to know something like that'. The answers to those questions are often more telling than how many ways SQLServer can format a date.

Thursday, March 17, 2011 - 1:22:06 PM - Jeremy Kadlec

John,   Thank you for the feedback.  The original request for these tips were to give interviewers and interviewees a sampling of technical questions based on a topic\position\level\etc.   If you are interested in submitting a tip or two on some additional interviewing aspects we would be open to the submissions.   Feel free to send me an email – jeremyk@EdgewoodSolutions.com, to discuss further.   Thank you, Jeremy Kadlec

Monday, February 13, 2012 - 10:19:17 AM - Alain Krikilion
> A single DELETE command could fill up the transaction log if it is small and set to full recovery mode. Also with Simple recovery mode because the delete is 1 big transaction and is logged in the transaction log before the transaction log is cleaned up after the transaction is commited (it is not cleaned up directly after the end of the transaction but on the next checkpoint).

Monday, February 13, 2012 - 11:04:10 AM - Jeremy Kadlec
Alain, Great point.  Let me update that question. 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