Home > Interview Q > TSQL Interview Questions – Part 1

TSQL Interview Questions – Part 1

December 9, 2010 Leave a comment Go to comments

These are the following common question that I faced in various TSQL interviews.

… I’ll be updating this post with more questions.

1. Difference between SQL Server 2000 & SQL Server 2005 features, or new features of 2005 vs 2000.
– Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [YouTube]
– Exception handling (TRY-CATCH block)
– CTE (Common Table Expressions)

2. What tools do you use for performance tuning?
Query Analyzer, Profiler, Index Wizard, Performance Monitor
Link: http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools

3. What is SQL Profiler? What it does? What template do you use?
More Info: http://www.extremeexperts.com/SQL/Articles/TraceTemplate.aspx

4. How can you execute an SQL query from command prompt?
More Info: http://msdn.microsoft.com/en-us/library/ms162773.aspx

5. Difference between DELETE & TRUNCATE statement? Which statement can be Rollbacked?
– With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
– TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
– Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
More differences on Delete & Truncate: http://wp.me/p12rgl-7 | YouTube.

6. What are extended stored procedures? Can you create your own extended stored-proc?
More Info: http://msdn.microsoft.com/en-us/library/ms175200.aspx

7. How can you execute a DOS command from SQL or through SQL query by using xp_cmdshell?

exec xp_cmdshell 'dir c:\*.exe'

More Info: http://msdn.microsoft.com/en-us/library/aa260689%28SQL.80%29.aspx

8. How will you insert result set of the above proc in a table?

insert into
exec xp_cmdshell 'dir c:\*.exe'

9. What are Cursors and their types? What type do you use most and which one is fast?
Fastest to slowest: Dynamic, Static, and Keyset.
More Info: http://msdn.microsoft.com/en-us/library/ms180169.aspx
Check how to use Cursors optimally: https://sqlwithmanoj.com/2011/02/07/avoid-cursors-or-use-them-optimally/

10. Why you should not use a cursor? What are its alternatives?
Alternatives: while loops with temp tables, derived tables, correlated sub-queries, CASE stmt
More Info: http://www.sql-server-performance.com/tips/cursors_p1.aspx

11. Difference between LEFT JOIN with WHERE clause & LEFT JOIN with no WHERE clause.
OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.
JOINs and its types: https://sqlwithmanoj.com/2009/03/12/sql-server-joins-and-types/

12. How will you migrate an SSIS package from Development to Production environment?
Do not include db connections and file paths in your workflow, instead create configuration files. This will help in deploying the pkg created in DEV server to Testing and finally to the PROD environment.
More Info: http://msdn.microsoft.com/en-us/library/cc966389.aspx

13. Multiple ways to execute a dynamic query.
EXEC sp_executesql, EXECUTE()
More Info: https://sqlwithmanoj.com/2011/03/22/execute-or-exec-vs-sp_executesql/

14. Difference between COALESCE() & ISNULL()
More Info: https://sqlwithmanoj.com/2010/12/23/isnull-vs-coalesce/

15. Difference between Temporary Table and Table Variable.
Check here: http://wp.me/p12rgl-r | YouTube

16. What should be the ideal combination with IN & UNION (ALL) in terms of performance?




17. What is an IDENTITY column and its usage in INSERT statements?
IDENTITY column can be used with a tables column to make it auto incremental, or a surrogate key.
Check my all blog posts related to IDENTITY: https://sqlwithmanoj.com/tag/identity/

18. Can you create a Primary key without clustered index?
Yes, you can create a Primary Key with a Non Clustered Index. But by default creation of PK automatically creates a clustered index upon the table, check here: https://sqlwithmanoj.com/2015/05/24/sql-myth-primary-key-pk-always-creates-clustered-index/

19. There are two tables one Master and another Feed table, both with 2 columns: ID & Price. Feed table gets truncated and re-populated every day.

Master Table		Feed Table
ID, Price		ID, Price
1    100		1    200
3    200		2    250
5    300		4    500
6    400		6    750
7    500 		7    800

Create a job with an optimal script that will update the Master table by the Feed table.
Use MERGE statement to INSERT any new record form the Feed table, update any existing record in Master table and DELETE existing records in Master table that is not present in Feed table.

20. What are CUBE & ROLLUP sets?
CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.
More Info: https://sqlwithmanoj.com/tag/cube/

21. What new indexes are introduced in SQL Server 2005 in comparison to 2000?
– Spatial
More Info: http://msdn.microsoft.com/en-us/library/ms175049.aspx

22. What are XML indexes, what is their use?
More Info: http://msdn.microsoft.com/en-us/library/ms345121%28SQL.90%29.aspx

23. How many types of functions (UDF) are there in SQL Server? What are inline functions?
– Scalar functions
– Inline Table-valued functions
– Multi-statement Table-valued functions
More Info: https://sqlwithmanoj.com/2010/12/11/udf-user-defined-functions/

24. How will you handle exceptions in SQL Server programming?
By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY block and error handling in the CATCH block, https://sqlwithmanoj.com/2010/06/16/try-catch-exception-handling/.

25. How would you send an e-mail form SQL Server?
Configure Database mail here: https://sqlwithmanoj.com/2010/09/29/database-mail-setup-sql-server-2005/.



… more questions on next post [Part-2].

  1. December 10, 2010 at 7:10 pm

    More common questions on MSDN: http://code.msdn.microsoft.com/SQLExamples

  2. Saurabh Sharma
    December 13, 2010 at 7:39 am

    This really helps Manoj …

    Keep posting


  3. Chris
    May 20, 2011 at 10:33 am

    A very good site for SQL server interview questions organised by topic. If you feel you need to brush up interview questions related to specific topics, this website will be of great use. God bless the person who has contributed to this site. Very useful.


    • May 20, 2011 at 10:51 am

      Thanks Chris for your comments!!!

      This post contains all those questions that I came across in my TSQL interviews. I’ll try to make this post more informative and more questions.

  4. nidhin
    July 17, 2011 at 3:35 pm


    I am new to SQL SERVER.Please let me know how would i create a procedure in which
    if i give any date it should return the last Monday of that week(with out a case statement)


  5. August 24, 2011 at 3:59 am

    Job Interview Questions and Answers for Freshers, Developers, Testers, Project Managers, Sales Managers to get in Engineering Jobs, Technical Jobs, Sales Jobs, Marketing Jobs, Management Jobs etc.

    Prepare your Interview With Us


  6. Irvine
    September 20, 2011 at 5:53 am

    Answer to q 19:

    MERGE master1 AS T
    USING Feed AS S
    ON (T.id = S.id)
    THEN INSERT(id, price) VALUES(S.id, S.price)
    THEN UPDATE SET T.price = S.price
    OUTPUT $action, inserted.*;

  7. December 19, 2013 at 10:22 am

    Hi Manoj, nice compilation. I have a small correction for answer of question 11.

    The word “no” is missing before WHERE in the answer.

    OUTER LEFT/RIGHT JOIN with no WHERE clause can act like an INNER JOIN if not used wisely or logically.

  8. December 19, 2013 at 10:54 am

    Hi @venu, thanks for your comments.

    I think you misunderstood the answer.

    Actually if you put the LEFT/RIGHT JOIN table’s filter condition on WHERE clause then it could act like an INNER JOIN. So, try putting filter conditions before the WHERE clause with the LEFT/RIGHT JOIN’s ON clause.

    Let me know if you understand or I’ll provide an example.

  9. December 19, 2013 at 9:05 pm

    Hi Manoj, thanks for the clarification.

  10. March 16, 2014 at 12:26 pm

    Thanks for some good set of questions .. You can also refer the below link for some more Q&A

    T-SQL interview Questions & Answer

  11. Alam
    July 27, 2017 at 10:48 am

    Thank you so much sir for putting effor and sharing your knowledge 🙂 , God bless you sir

  1. January 2, 2011 at 10:31 pm
  2. February 8, 2011 at 11:26 am
  3. June 2, 2011 at 5:07 am
  4. July 4, 2011 at 3:44 am
  5. September 10, 2014 at 9:50 pm
  6. January 21, 2015 at 2:49 pm
  7. June 30, 2017 at 11:31 am

Leave a Reply to manub22 Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: