Top 10 SQL Server Interview Questions for 5 years Experienced

If you're looking for SQL Server Interview Questions for 5 years of 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 2023 that helps you in cracking your interview & acquire a dream career as a SQL Server Developer.

Best SQL Server Interview Questions and Answers For 5 Years Experienced

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

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.

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.

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

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.

3) 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?

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

4) 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
Wish to learn more about SQL Server? Check out our comprehensive SQL Server Tutorial

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

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.

MindMajix Youtube Channel

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

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.

Frequently Asked SQL Server Interview Questions & Answers

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

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:


8) 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)
Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!

9) 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 …)

10) Difference between SQL Server and Postgre SQL

SQL Server vs PostgreSQL
FeatureMicrosoft SQL ServerPostgreSQL
Operating SystemWindowsWindows, IOS, Unix, and Linux
LicensingFree and commercialOpen Source
Installation and MaintenanceExpert advice neededMedium
Windows default driversYesNo
Read-Only ViewsYesYes
Open SourceOther than CodePlex/.NETModerately
Modifying columnsCan modify without droppingCannot
Dropping tables and viewsCan drop tables and viewsCan't drop tables

List of Related Microsoft Certification Courses:

 SSIS Training Power BI Training
 SSRS Training SharePoint Training
 SSAS Training SQL Server DBA Training
 SCCM Training BizTalk Server Training
 Team Foundation Server Training BizTalk Server Administrator Training


Course Schedule
SQL Server TrainingJun 22 to Jul 07View Details
SQL Server TrainingJun 25 to Jul 10View Details
SQL Server TrainingJun 29 to Jul 14View Details
SQL Server TrainingJul 02 to Jul 17View Details
Last updated: 13 May 2023
About Author

Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.

read less