2025年sqlserver考试题及答案

2025年sqlserver考试题及答案一、单项选择题（每题2分，共20分）1.以下关于SQLServer索引的描述中，正确的是（）。A.聚集索引会改变表中数据的物理存储顺序，每个表最多创建1个B.非聚集索引叶节点存储的是数据行的全部列值，每个表最多创建249个C.列存储索引仅适用于OLTP场景，适合高频点查操作D.筛选索引通过WHERE子句限定索引覆盖的数据范围，会增加全表扫描的开销答案：A2.某事务执行过程中，读取了其他事务尚未提交的数据，导致后续该事务回滚时出现数据不一致。这种现象属于（）。A.脏读B.不可重复读C.幻读D.丢失更新答案：A3.若要将数据库“SalesDB”的恢复模式从“简单”更改为“完整”，应使用的T-SQL语句是（）。A.ALTERDATABASESalesDBSETRECOVERYFULLB.ALTERDATABASESalesDBMODIFYRECOVERYFULLC.UPDATEDATABASESalesDBSETRECOVERY=FULLD.CHANGEDATABASESalesDBRECOVERYTOFULL答案：A4.以下关于SQLServer备份的描述中，错误的是（）。A.完整备份包含数据库的全部数据，是差异备份的基础B.事务日志备份仅在完整恢复模式或大容量日志恢复模式下可用C.文件和文件组备份适用于大型数据库，可单独恢复部分数据D.尾日志备份用于在数据库损坏时捕获尚未备份的日志，避免数据丢失答案：B（注：简单恢复模式下无法进行事务日志备份）5.在SQLServer中，用于查看当前数据库锁信息的动态管理视图（DMV）是（）。A.sys.dm_exec_sessionsB.sys.dm_os_wait_statsC.sys.dm_tran_locksD.sys.dm_db_index_physical_stats答案：C6.某表结构为“Employees(EmpIDINTPRIMARYKEY,EmpNameNVARCHAR(50),DeptIDINT,SalaryDECIMAL(10,2))”，需查询各部门中工资高于本部门平均工资的员工信息。以下最优的实现方式是（）。A.使用子查询计算各部门平均工资，外层查询关联比较B.使用窗口函数OVER(PARTITIONBYDeptID)计算平均工资C.使用临时表存储各部门平均工资，再JOIN查询D.使用游标逐行遍历计算答案：B7.以下关于SQLServer安全性的描述中，正确的是（）。A.服务器级主体包括登录名和数据库用户，需通过映射关联B.角色是权限的集合，服务器角色和数据库角色均可自定义C.行级别安全性（RLS）通过创建策略限制用户可访问的行，需使用存储过程实现D.透明数据加密（TDE）加密数据库文件，密钥存储在Windows证书存储中答案：D（注：A中数据库用户是数据库级主体；B中服务器角色不可自定义；C中RLS通过安全策略实现）8.执行以下T-SQL语句后，变量@Result的值是（）。DECLARE@ResultINT;WITHCTEAS(SELECT1ASNumUNIONALLSELECTNum+1FROMCTEWHERENum<5)SELECT@Result=SUM(Num)FROMCTE;A.15B.10C.21D.6答案：A（CTE提供1-5的序列，求和为15）9.某OLTP系统出现慢查询，执行计划显示“键查找”操作频繁。最可能的优化措施是（）。A.增加CPU核心数B.创建覆盖索引（包含查询所需的所有列）C.增大数据库缓存池大小D.将简单恢复模式改为完整恢复模式答案：B10.以下关于SQLServer高可用性方案的描述中，错误的是（）。A.故障转移群集实例（FCI）基于共享存储，实现服务器级冗余B.可用性组（AG）支持多副本，可配置可读副本分担查询压力C.日志传送适用于异地容灾，主备延迟较小D.数据库镜像（Mirroring）在SQLServer2012之后已被可用性组取代答案：C（日志传送的主备延迟通常较高）二、填空题（每题2分，共10分）1.SQLServer中，用于强制查询使用指定索引的提示是__________。答案：WITH(INDEX(索引名))2.事务的四大特性（ACID）是原子性、一致性、__________和持久性。答案：隔离性3.若要限制用户只能查询“Employees”表中“DeptID=10”的记录，可通过__________功能实现。答案：行级别安全性（RLS）或行级安全性4.动态管理视图sys.dm_db_index_physical_stats的__________参数用于指定数据库ID。答案：database_id5.完整备份的扩展名为.bak，事务日志备份的扩展名为__________。答案：.trn三、简答题（每题8分，共40分）1.简述聚集索引与非聚集索引的区别，并说明何时适合创建聚集索引。答案：聚集索引决定表中数据的物理存储顺序，叶节点直接存储数据行，每个表最多1个；非聚集索引叶节点存储键值和行定位器（如RID或聚集索引键），每个表最多999个（SQLServer2016+）。适合创建聚集索引的场景：列值经常被范围查询（如BETWEEN、>、<）；列值相对唯一且更新不频繁（避免索引碎片）；表经常按该列顺序访问（如按日期排序的日志表）。2.说明事务隔离级别“可重复读”与“串行化”的区别，并举例说明各自适用场景。答案：可重复读：确保在事务期间，多次读取同一行的结果一致，禁止其他事务修改该行（但允许插入新行），可能出现幻读。串行化：最高隔离级别，事务执行时相当于完全串行，禁止其他事务插入、修改、删除相关数据，避免幻读，但并发性能最低。适用场景举例：可重复读：财务系统中查询账户余额并计算利息，需保证余额在计算期间不变；串行化：库存系统中处理限量商品订单，需严格避免超卖（如秒杀活动）。3.简述索引碎片产生的原因及解决方法。答案：碎片产生原因：数据行更新导致页分裂（聚集索引）；非聚集索引键值频繁修改或插入（键值无序时更严重）；大量删除操作导致页内空间未被重用。解决方法：当碎片率（avg_fragmentation_in_percent）<30%时，使用REBUILDINDEX重组索引（轻量级操作，仅重新排列页内数据）；当碎片率≥30%时，使用REBUILDINDEX重建索引（重建整个索引结构，可回收空间）；定期维护（如通过作业调度），避免碎片累积影响查询性能。4.说明SQLServer中“简单恢复模式”与“完整恢复模式”的主要差异及适用场景。答案：差异：简单恢复模式：仅保留足够的日志用于崩溃恢复，日志自动截断，不支持事务日志备份；完整恢复模式：保留所有事务日志，支持完整备份+差异备份+事务日志备份的恢复策略，可恢复到任意时间点。适用场景：简单恢复模式：对数据恢复要求不高、允许丢失最后一次完整/差异备份后数据的场景（如测试库、临时数据）；完整恢复模式：对数据完整性要求高的生产环境（如订单系统、财务数据库），需支持时间点恢复。5.简述如何通过执行计划分析查询性能问题，并列举3个常见的性能瓶颈指标。答案：分析步骤：启用IncludeActualExecutionPlan（实际执行计划）或EstimatedExecutionPlan（预估执行计划）；查看操作符的成本占比（百分比），定位高成本操作；检查是否存在全表扫描（TableScan）、键查找（KeyLookup）、哈希连接（HashJoin）等高开销操作；分析索引使用情况（是否命中索引、是否缺少覆盖索引）。常见瓶颈指标：逻辑读（LogicalReads）：数值过高可能表示索引缺失；并行度（DegreeofParallelism）：过高可能导致资源竞争；等待时间（WaitTime）：如LCK_M_XX锁等待，可能由并发冲突引起。四、综合题（每题15分，共30分）1.某电商公司需设计“订单管理系统”数据库，需求如下：客户表（Customers）：客户ID（主键）、姓名、注册时间、会员等级（1-5级）；订单表（Orders）：订单ID（主键）、客户ID（外键）、下单时间、总金额、支付状态（0未支付，1已支付）；订单明细表（OrderDetails）：明细ID（主键）、订单ID（外键）、商品ID、数量、单价。要求：（1）写出创建3张表的T-SQL语句（需包含主键、外键、必要约束）；（2）为优化“查询某客户近30天内已支付订单的总金额及商品数量”的操作，建议创建哪些索引？并说明理由。答案：（1）创建表语句：CREATETABLECustomers(CustomerIDINTIDENTITY(1,1)PRIMARYKEY,CustomerNameNVARCHAR(50)NOTNULL,RegisterTimeDATETIMENOTNULLDEFAULTGETDATE(),MembershipLevelTINYINTCHECK(MembershipLevelBETWEEN1AND5));CREATETABLEOrders(OrderIDINTIDENTITY(1,1)PRIMARYKEY,CustomerIDINTNOTNULL,OrderTimeDATETIMENOTNULL,TotalAmountDECIMAL(10,2)NOTNULL,PaymentStatusTINYINTNOTNULLCHECK(PaymentStatusIN(0,1)),FOREIGNKEY(CustomerID)REFERENCESCustomers(CustomerID));CREATETABLEOrderDetails(DetailIDINTIDENTITY(1,1)PRIMARYKEY,OrderIDINTNOTNULL,ProductIDINTNOTNULL,QuantityINTNOTNULLCHECK(Quantity>0),UnitPriceDECIMAL(8,2)NOTNULL,FOREIGNKEY(OrderID)REFERENCESOrders(OrderID));（2）建议创建的索引及理由：在Orders表的CustomerID、OrderTime、PaymentStatus列上创建复合索引（包含TotalAmount）：CREATEINDEXIX_Orders_Customer_Payment_TimeONOrders(CustomerID,PaymentStatus,OrderTime)INCLUDE(TotalAmount);理由：查询需筛选指定客户（CustomerID）、已支付（PaymentStatus=1）、近30天（OrderTime）的订单，并获取总金额（TotalAmount）。复合索引可快速定位目标订单，INCLUDE子句避免键查找。在OrderDetails表的OrderID列上创建非聚集索引（包含Quantity）：CREATEINDEXIX_OrderDetails_OrderIDONOrderDetails(OrderID)INCLUDE(Quantity);理由：需关联订单表与明细表，通过OrderID关联时，索引可加速JOIN操作；INCLUDEQuantity避免回表查询数量。2.某系统出现慢查询，具体现象为：每天19:00-20:00期间，执行以下语句耗时从1秒增至10秒以上：SELECTc.CustomerName,o.TotalAmount,COUNT(od.DetailID)ASProductCountFROMCustomerscJOINOrdersoONc.CustomerID=o.CustomerIDJOINOrderDetailsodONo.OrderID=od.OrderIDWHEREo.OrderTimeBETWEEN'2025-01-01'AND'2025-01-31'ANDo.PaymentStatus=1GROUPBYc.CustomerName,o.TotalAmount;（1）请分析可能的性能瓶颈；（2）提出至少3种优化措施，并说明理由。答案：（1）可能的性能瓶颈：数据量增长：1月为销售旺季，订单及明细数据量大幅增加，导致JOIN和GROUPBY操作耗时增加；索引缺失：Orders表的OrderTime、PaymentStatus列可能未创建有效索引，导致全表扫描；锁竞争：19:00-20:00为业务高峰，其他事务可能对Orders或OrderDetails表进行更新，导致查询等待锁；内存不足：查询需要大量内存用于哈希聚合（HASHAGGREGATE），内存不足时可能触发磁盘临时表；统计信息过时：表数据变化后未更新统计信息，导致查询优化器提供错误的执行计划。（2）优化措施及理由：①创建