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:

• SET SHOWPLAN_ALL ON
• SET SHOWPLAN_TEXT ON
• SET STATISTICS IO ON
• 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.

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:

UPDATE STATISTICS,
STATS_DATE,
DBCC SHOW_STATISTICS,
CREATE STATISTICS,
DROP STATISTICS,
sp_autostats,
sp_createstats,
sp_updatestats

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
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
Drivers available	ODBC, JDBC, ADO.NET	ODBC, JDBC, ADO.NET
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

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