In this article, we will talk about SQL interview questions and answers that can be asked in the second-round technical stage. These interview questions are based on real-life experiences. Therefore, it can be a very useful source when preparing for the second round of SQL technical interviews.
Many companies conduct a technical interview to evaluate the knowledge of the candidates. Most of the time, this recruitment process does not just complete only one technical interview. The first interview mostly focuses on your capabilities and tries to figure out whether you meet the basic qualifications required for the job. After being invited for the second round of technical interviews, your questions will be very difficult than the previous one. For this reason, you should prepare in-depth questions for this stage. In this article, you will find some SQL interview questions that might be asked for the second stage and detailed answers.
1. What is the lock escalation?
When performing a transaction by SQL Server, the lock manager should lock database objects to ensure the consistency of the database. However, every locked object held by the lock manager consumes 96 bytes of memory. When working with a large number of rows, this circumstance can be required a significant amount of memory to locking the rows. To minimize this memory consumption, SQL Server uses a mechanism called lock escalation. When the row or page locks exceed a limit, lock escalation mechanism converts row or page locks into the table locks so it reduces the memory usage amount.
Now we will create a table and insert 10k row into this table to illustrate the working mechanism of the lock escalation on SQL Server.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE TestTable ( Id INT , Col1 VARCHAR(100), Col2 VARCHAR(100)); DECLARE @Counter AS INT =1 WHILE @Counter <= 10000 BEGIN INSERT INTO TestTable VALUES(@Counter,'Val1','Val2') SET @Counter =@Counter+1 END |
In this second step, we will update one row and examine which objects are locked.
|
1 2 3 4 5 6 7 |
BEGIN TRAN UPDATE TestTable SET Col1 = 'Val1' ,Col2='Val2' WHERE Id=1 SELECT resource_type,request_session_id ,request_type ,request_mode FROM sys.dm_tran_locks p WHERE resource_database_id = db_id() AND request_session_id =@@SPID ROLLBACK TRAN |
Lock manager places intent exclusive (IX) locks on the data page and tables that contain the rows when the modified rows acquired exclusive locks. Lock Hierarchy in SQL Server starts at the database level and goes down to row-level.
Database –> Table –> Page –> Row
As we can see in the example, the database has acquired a shared (S) lock and this lock type indicates somebody used this database. The table (object) and page have acquired intent shared (IS) lock because the modified row (key) acquired an exclusive (X) lock.
We will now increase the number of rows that have been modified.
|
1 2 3 4 5 6 7 |
BEGIN TRAN UPDATE TestTable SET Col1 = 'Val2' ,Col2='Val2' WHERE Id<6500 SELECT resource_type,request_session_id ,request_type ,request_mode FROM sys.dm_tran_locks p WHERE resource_database_id = db_id() AND request_session_id =@@SPID ROLLBACK TRAN |
As a result, when we increase the number of the rows that will be modified, the lock escalation mechanism will be triggered and the table will acquire an exclusive lock. This mechanism will eliminate the locked rows and increase memory consumption.
The lock escalation threshold is at least 5000 lock but this number can be changed according to several factors and it does not indicate the exact number. The lock manager considers some parameters such as the number of rows, row size, and the structure of the table to determine this number.
2. Why do we use the table hints in SQL Server and could you give one example about it?
This SQL interview question can be asked to test your knowledge about the query hints. Table hints are used to change the default behavior of the default working parameters so that we can handle the different issues. For example, TABLOCKX hint places an exclusive lock to the specified table until the transaction will be completed. When we look at the following example, the row-level exclusive lock is the default behavior but we will change this lock type to the table level with the TABLOCKX hint.
|
1 2 3 4 5 6 7 |
BEGIN TRAN UPDATE TestTable WITH(TABLOCKX) SET Col1 = 'Val1' ,Col2='Val2' WHERE Id=1 SELECT resource_type,request_session_id ,request_type ,request_mode FROM sys.dm_tran_locks p WHERE resource_database_id = db_id() AND request_session_id =@@SPID ROLLBACK TRAN |
To reduce the table’s concurrency is the main disadvantage of the TABLOCKX hint.
3. What is a trivial execution plan?
SQL Query Optimizer doesn’t want to deal with creating optimized query plans for simple queries to avoid time and resources consumption. For example, the query optimizer will generate a trivial execution plan for the following query.
|
1 |
SELECT Col1 ,Col2 FROM TestTable WHERE Id=2 |
We can see the created execution plan type in the Statement optimization level property of the Select operator.
To disable generating trivial execution plans, we can use the trace flag 8757.
|
1 2 |
SELECT Col1 ,Col2 FROM TestTable WHERE Id=2 OPTION (QUERYTRACEON 8757) |
When we disable the creation of the trivial execution plans, the query optimizer has created a FULL optimized query plan and decided that this query requires an index so that it can be faster.
4. What is Batch Mode on Rowstore feature?
This SQL interview question may be asked to test your knowledge of the new versions enhancements of the SQL server.
This feature introduced with the SQL Server 2019 version and it helps to access hundreds of rows in one fetch instead of row by row fashion and it is particularly designed for the aggregation and sort operations. Now, let’s look at the actual execution plan of the following query.
|
1 2 3 4 5 6 |
SELECT ProductID,SUM(LineTotal) , SUM(UnitPrice) , SUM(UnitPriceDiscount) FROM Sales.SalesOrderDetailEnlarged SOrderDet INNER JOIN Sales.SalesOrderHeaderEnlarged SalesOr ON SOrderDet.SalesOrderID = SalesOr.SalesOrderID GROUP BY ProductID |
As we can see on the select operator properties Batch mode on row store used property is true. It indicates that some of the operators in the execution plan have performed batch mode execution on the row store. Now, we will open the properties of the clustered index scan operator.
The clustered index scan operator has accessed the data using the batch mode on the row store.
5. What is the functionality of the XACT_ABORT?
When the XACT_ABORT is enabled, if any SQL statement has an error on the transaction, the whole transaction is terminated and rolled back. If we disable the XACT_ABORT, when a statement returns an error, only the errored query is rolled back and other queries complete the operations.
In the following query, we will enable the XACT_ABORT, and then we try to insert a duplicate row, so the entire transaction will be rollbacked.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SET XACT_ABORT ON IF OBJECT_ID(N'tempdb..#Test') IS NOT NULL BEGIN DROP TABLE #Test END CREATE TABLE #Test (Id INT PRIMARY KEY, Col1 VARCHAR(20)) BEGIN TRAN INSERT INTO #Test VALUES(1,'Value1') INSERT INTO #Test VALUES(2,'Value1') INSERT INTO #Test VALUES(3,'Value1') INSERT INTO #Test VALUES(4,'Value1') INSERT INTO #Test VALUES(4,'Value1') COMMIT TRAN GO SELECT * FROM #Test |
We will disable XACT_ABORT in the following query, so all insert statements will be performed successfully except one that raises an error.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SET XACT_ABORT OFF IF OBJECT_ID(N'tempdb..#Test') IS NOT NULL BEGIN DROP TABLE #Test END CREATE TABLE #Test (Id INT PRIMARY KEY, Col1 VARCHAR(20)) BEGIN TRAN INSERT INTO #Test VALUES(1,'Value1') INSERT INTO #Test VALUES(2,'Value1') INSERT INTO #Test VALUES(3,'Value1') INSERT INTO #Test VALUES(4,'Value1') INSERT INTO #Test VALUES(4,'Value1') COMMIT TRAN GO SELECT * FROM #Test |
Conclusion
In this article, we discussed a few SQL interview questions that can be asked in the second-round. These second-round interview questions will be more complicated and difficult. This round will be more challenging as it will include more technical questions rather than traditional interview questions.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023