sql面试题sql语句_第二轮SQL面试问题

sql面试题sql语句

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.

在本文中，我们将讨论在第二轮技术阶段可能会问到SQL面试问题和答案。 这些访谈问题基于现实生活中的经验。 因此，在准备第二轮SQL技术面试时，它可能是非常有用的资源。

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.

许多公司进行技术面试，以评估候选人的知识。 在大多数情况下，这种招聘过程不仅会完成一次技术面试。 第一次面试主要侧重于您的能力，并试图弄清楚您是否符合工作所需的基本资格。 在被邀请参加第二轮技术面试后，您的问题将比前一个更加困难。 因此，您应该为此阶段准备深入的问题。 在本文中，您将找到第二阶段可能会问到的一些SQL面试问题和详细答案。

1.什么是锁升级？ (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.

通过SQL Server执行事务时，锁定管理器应锁定数据库对象以确保数据库的一致性。 但是，锁管理器保存的每个锁定对象都占用96字节的内存。 当处理大量的行时，可能需要大量的内存来锁定行。 为了最大程度地减少这种内存消耗，SQL Server使用一种称为锁升级的机制。 当行锁或页面锁超过限制时，锁升级机制会将行锁或页面锁转换为表锁，从而减少内存使用量。

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.

现在，我们将创建一个表并将10k行插入该表中，以说明SQL Server上锁升级的工作机制。

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.

在第二步中，我们将更新一行并检查哪些对象被锁定。

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.

当修改的行获得排他锁时，锁管理器在包含该行的数据页和表上放置意图排他（ IX ）锁。 SQL Server中的锁定层次结构从数据库级别开始，然后下降到行级别。

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.

正如我们在示例中看到的那样，数据库已获取了共享（ S ）锁，并且此锁类型指示有人使用了该数据库。 由于修改后的行（键）获得了排他（ X ）锁，因此表（对象）和页面已获得了意图共享（ IS ）锁。

We will now increase the number of rows that have been modified.

现在，我们将增加已修改的行数。

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.

锁定升级阈值至少为5000个锁定，但是可以根据多种因素更改此数字，并且它不能指示确切的数字。 锁管理器考虑一些参数（例如，行数，行大小和表的结构）来确定此数目。

2.为什么我们在SQL Server中使用表提示，您能举一个例子吗？ (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.

可以要求此SQL采访问题来测试您对查询提示的了解。 表提示用于更改默认工作参数的默认行为，以便我们可以处理不同的问题。 例如， TABLOCKX提示将独占锁放置到指定的表，直到事务将完成。 当我们看下面的示例时，行级互斥锁是默认行为，但是我们将使用TABLOCKX提示将此锁类型更改为表级。

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.

减少表的并发性是TABLOCKX提示的主要缺点。

3.什么是微不足道的执行计划？ (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.

SQL Query Optimizer不想处理为简单查询创建优化的查询计划，以避免时间和资源消耗。 例如，查询优化器将为以下查询生成一个简单的执行计划。

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.

我们可以在Select运算符的“ 语句优化级别”属性中看到创建的执行计划类型。

To disable generating trivial execution plans, we can use the trace flag 8757.

要禁用生成琐碎的执行计划，我们可以使用跟踪标志8757 。

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.

当我们禁用琐碎的执行计划的创建时，查询优化器已经创建了FULL优化的查询计划，并决定此查询需要索引，以便可以更快。

4.什么是行存储上的批处理模式功能？ (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.

可能会询问此SQL采访问题，以测试您对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.

SQL Server 2019版本中引入的此功能可帮助一次访问访问数百行，而不是逐行访问，并且该功能专为聚合和排序操作而设计。 现在，让我们看一下以下查询的实际执行计划。

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.

正如我们在select运算符属性上看到的那样，行存储中的批处理模式used属性为true。 它表示执行计划中的某些运算符已在行存储上执行了批处理模式执行。 现在，我们将打开聚簇索引扫描运算符的属性。

The clustered index scan operator has accessed the data using the batch mode on the row store.

聚集索引扫描操作员已使用行存储上的批处理模式访问了数据。

5. XACT_ABORT的功能是什么？ (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.

启用XACT_ABORT时，如果任何SQL语句在事务上出错，则整个事务将终止并回滚。 如果我们禁用XACT_ABORT，则当语句返回错误时，只有错误的查询才会回滚，其他查询会完成操作。

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.

在以下查询中，我们将启用XACT_ABORT ，然后尝试插入重复的行，因此将回滚整个事务。

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.

我们将在以下查询中禁用XACT_ABORT ，因此所有成功插入语句都会成功执行，除非会引发错误。

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.

在本文中，我们讨论了第二轮可能会问到的一些SQL采访问题。 这些第二轮面试问题将更加复杂和困难。 这一轮将更具挑战性，因为它将包括更多的技术性问题，而不是传统的面试问题。

翻译自: https://www.sqlshack.com/sql-interview-questions-for-the-second-round/

sql面试题sql语句