If you're looking for SQL Server Interview Questions for 5 years Experienced, you are in the right place. There are a lot of opportunities from many reputed companies in the world. According to research, The average salary for SQL Server ranges from approximately $69,682 pa. So, You still have the opportunity to move ahead in your career in SQL Server. Mindmajix offers Advanced SQL Server Interview Questions 2021 that helps you in cracking your interview & acquire a dream career as a SQL Server Developer.

If you want to build your career with a SQL Server certified professional, then visit our “SQL Server Online Training” Course. This course will help you to achieve excellence in this domain.

Best SQL Server Interview Questions and Answers For 5 Years Experienced

SQL Server vs PostgreSQL
Feature Microsoft SQL Server PostgreSQL
Operating System Windows Windows, IOS, Unix, and Linux
Licensing Free and commercial Open Source
Installation and Maintenance Expert advice needed Medium
Windows default drivers Yes No
Read-Only Views Yes Yes
Open Source Other than CodePlex/.NET Moderately
Modifying columns Can modify without dropping Cannot
Dropping tables and views Can drop tables and views Can't drop tables

Q1) What is blocking and how would you troubleshoot it?

Ans. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

Q2) What are the steps you will take to improve the performance of a poor performing query?

Ans. This is a very open-ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are:

  • SQL Server Profiler
  • Windows NT /2000 Performance monitor
  • Graphical execution plan in Query Analyzer.

Wish to learn more about SQL Server? Check out our comprehensive SQL Server Tutorial

Q3) You are being you being assigned a task to move 5 million rows from one server to another using T-SQL with a linked-server. What will you consider to avoid transaction log fill up at destination server?

Subscribe to our youtube channel to get new updates..!

Ans. We will prefer to use SET ROWCOUNT and a while loop to commit data in batches.

Q4) What is the optimal Disk configuration for a database server and what RAID configurations would you use if budget is not a constraint?

  1. RAID 1 for the OS / Applications
  2. RAID 1 for the page file
  3. RAID 10 for the Data file (or RAID 5 for few writes)
  4. RAID 1 (or 10) for the transaction log

Frequently Asked SQL Server Interview Questions & Answers

Q5) What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Ans. Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a  request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Q6) What is blocking and how would you troubleshoot it?

Ans. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

Q7) What are statistics, under what circumstances they go out of date, how do you update them?

Ans. Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. The query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

  1. If there is a significant change in the key values in the index
  2. If a large amount of data in an indexed column has been added, changed, or removed (that is if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
  3. The database is upgraded from a previous version

Look up SQL Server books online for the following commands:


Q8) Could you please some items which you may see in an execution plan indicating the query is not optimized.


  1. Index Scan or Table Scan
  2. Hash Joins
  3. Thick arrows (indicating large work tables)
  4. Parallel streams (Parallelism)
  5. Bookmark lookup (or key lookup)

Q9) What structure can you implement for the database to speed up table reads?


Follow the rules of DB tuning we have to:

  • properly use indexes ( different types of indexes)
  •  properly locate different DB objects across different tablespaces, files and so on.
  • 3 create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)
Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

List of Related Microsoft Certification Courses: