Top 10 SQL Server Interview Questions for 5 years Experienced

Rating: 5.0Blog-star
Views: 126329
by Arogyalokesh
Last modified: July 16th 2021

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 2021 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.

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:

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

 

Subscribe

Be the first to catch the latest happenings of technology with us.

About Author

author
NameArogyalokesh
Author Bio

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.