Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















SQL Server Developer T-SQL Interview Questions

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (11)   |   Related Tips: More > Interview Questions Developer
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 5: True or False - SQL Server can format the date in over 10 different patterns.

Question Difficulty = Moderate

 

  • 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 4: In SQL Server 2005, what new functionality was introduced with the GO command?

 

  • 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? 

 

 

  • 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?

 

  • 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?
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


Last Update: 2/12/2012


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Friday, March 07, 2008 - 7:16:24 AM - aprato Read The Tip

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 Read The Tip

no


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

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 Read The Tip

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 Read The Tip

Aprato, Yeah Agreed . Thanks for your valuble info


Thursday, March 17, 2011 - 12:36:30 PM - John Fox Read The Tip
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 Read The Tip

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 Read The Tip

> 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 Read The Tip

Alain,

Great point.  Let me update that question.

Thank you,
Jeremy Kadlec


Tuesday, June 11, 2013 - 2:38:41 PM - Rob Lambert Read The Tip

Wouldn't it be more accurate to say that TRY/CATCH replaces the use of an @@ERROR test?  I find myself using RAISERROR just as much as before, but the statements tend to be located in the CATCH blocks.


Friday, November 29, 2013 - 10:20:26 AM - drew.georgopulos@yahoo.com Read The Tip

 

i just wanted to observe that while scripting is definitely preferred over clicking, it is possible to trap ssms clicks in profiler so the commands invoked by clicks could be later harvested in a script, effectively turning profiler into a macro recorder :-)



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

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.