SQL Server 高频面试题及详细答案（实战完整版）

SQLServer高频面试题及详细答案（实战完整版）一、基础概念类1.简单说下SQLServer数据库、数据文件、日志文件的区别和作用答案：SQLServer数据库主要由两类核心文件组成，分别是数据文件和日志文件，缺一不可。数据文件（mdf/ndf）：mdf是主数据文件，一个数据库只能有一个，用来存储数据表、视图、索引、存储过程等所有业务数据和数据库结构信息。ndf是辅助数据文件，可多个，用来分担主数据文件压力，做数据拆分存储。日志文件（ldf）：专门记录数据库所有的增删改操作日志，不存储业务数据。主要用于事务回滚、数据恢复、增量备份、故障还原，是保证数据一致性的核心文件。简单总结：数据文件存结果，日志文件存过程。2.主键、唯一键、普通索引的区别？各自使用场景答案：主键（PrimaryKey）非空且唯一，一张表只能有一个主键，SQLServer会默认为主键创建聚集索引。主要用于唯一标识每一条数据，一般用作表的ID字段，是数据表的核心唯一标识。唯一键（UniqueKey）唯一但可以为空，一张表可以有多个唯一键，默认创建非聚集索引。用于业务唯一约束，比如手机号、身份证号、用户账号，保证字段数据不重复。普通索引（Index）不限制唯一，仅用于提升查询速度，无约束作用。常用于经常作为查询条件、排序、分组的字段，比如订单时间、状态字段。3.聚集索引和非聚集索引的核心区别（面试高频）答案：聚集索引聚集索引的叶子节点就是整张表的真实数据，表数据会按照聚集索引排序存储。一张表只能有一个聚集索引，默认绑定主键。查询效率最高，直接定位整行数据。非聚集索引非聚集索引叶子节点只存储索引字段和聚集索引键，不存储完整数据。一张表可以有多个非聚集索引。查询时需要先查索引找到主键，再回表查询完整数据，多一次查找过程。核心总结：聚集索引：索引和数据绑定，物理排序；非聚集索引：索引和数据分离，逻辑排序。4.什么是覆盖索引？有什么好处？答案：覆盖索引指查询所需的所有字段，全部包含在非聚集索引中，不需要回表查询原始数据。比如建立索引(主键,姓名,年龄)，查询语句只查姓名和年龄，就可以直接通过索引拿到全部数据，无需访问数据表。好处：避免书签查找、减少IO、大幅提升查询性能，是日常优化中最常用的索引优化手段。二、SQL查询与实战语法5.union和unionall的区别答案：两者都是用来合并多条查询结果集，核心区别在去重和性能。union：自动去除重复数据，并且对结果集排序，性能较差。如果数据量大，去重比对非常消耗资源。unionall：直接拼接结果集，不去重、不排序，性能远高于union。工作规范：业务中能使用unionall绝不使用union，只有明确需要去重时才用union。6.innerjoin、leftjoin、rightjoin、fulljoin区别和使用场景答案：innerjoin：内连接，只返回两张表中匹配成功的数据，匹配不到直接过滤。常用于查询有关联关系的有效数据。leftjoin：左连接，左表数据全部保留，右表匹配不到显示null。工作中使用最多，常用于查询主表数据，关联附表信息。rightjoin：右连接，右表数据全部保留，左表匹配不到显示null，业务中使用极少。fulljoin：全连接，左右表所有数据全部保留，匹配不到显示null，适用于数据比对、数据对账场景。7.where和having的区别答案：执行顺序和作用对象完全不同。where：分组之前过滤原始数据，作用于单行数据，不能使用聚合函数（sum、count、max）。having：分组之后过滤分组结果，作用于分组后的数据集，可以使用聚合函数。执行顺序：where过滤数据→groupby分组→having过滤分组结果。8.子查询、关联查询哪个效率高？为什么答案：大部分场景下join关联查询效率优于子查询。原因：子查询会生成临时结果集，部分子查询会重复遍历数据，尤其是嵌套子查询，循环次数多。而join查询是基于索引匹配，数据库优化器会自动优化执行计划，匹配效率更高。特殊场景：简单的单行子查询和join效率基本一致，复杂统计场景子查询可读性更高。三、事务、锁与并发（重点面试题）9.什么是事务？事务的四大特性ACID答案：事务是一组不可分割的SQL操作单元，要么全部执行成功，要么全部失败回滚，用来保证数据操作的一致性。四大特性：原子性：事务整体不可拆分，成功全成功，失败全回滚。一致性：事务执行前后，数据整体状态合法、约束有效，不会出现脏数据。隔离性：多个事务并发执行时，互相隔离，互不干扰。持久性：事务提交后，数据永久写入数据库，断电重启数据不丢失。10.SQLServer事务隔离级别有哪些？各自问题答案：一共5种隔离级别，隔离程度由低到高：1.读未提交：可以读取未提交数据，存在脏读、不可重复读、幻读，基本不用。2.读已提交（默认级别）：只能读取已提交数据，解决脏读，存在不可重复读、幻读。3.可重复读：保证同一事务内多次读取数据一致，解决脏读、不可重复读，存在幻读。4.快照隔离：基于版本控制，读写不阻塞，无脏读、不可重复读、幻读，需开启快照功能。5.串行化：最高隔离级别，完全串行执行，解决所有并发问题，并发性能最差。11.脏读、不可重复读、幻读的区别答案：脏读：一个事务读取到另一个事务未提交的数据，对方回滚后，读取的数据失效，属于严重数据错误。不可重复读：同一个事务内，两次读取同一条数据，中间被其他事务修改并提交，两次结果不一致。侧重数据修改。幻读：同一个事务内，两次查询批量数据，中间被其他事务新增或删除数据，导致两次查询条数不一致。侧重数据新增/删除。12.SQLServer常见锁类型？行锁、页锁、表锁区别答案：行锁：锁定单行数据，粒度最小，并发最高，修改少量数据时触发。页锁：锁定一整页数据（一页8KB），包含多条数据，介于行锁和表锁之间。表锁：锁定整张数据表，粒度最大，并发最差，批量更新、无索引更新时容易触发。日常优化核心：尽量让数据库走行级锁，避免升级为页锁、表锁，防止阻塞其他业务。13.什么是死锁？如何避免和解决死锁答案：死锁是两个或多个事务互相持有对方需要的锁，同时等待对方释放资源，导致事务永久阻塞。常见产生原因：事务执行顺序不一致、事务过长、批量更新无顺序。避免方案：1.统一所有SQL的更新顺序，固定操作表的先后顺序；2.精简事务，减少事务执行时间，不写冗余逻辑；3.批量更新数据时，按照主键排序更新；4.开启死锁监控，设置死锁优先级。四、索引优化与慢查询14.哪些情况会导致索引失效（高频必考）答案：1.索引字段使用函数、运算、类型转换，比如year(createTime)、id+1；2.模糊查询左匹配，比如%张三、%张%，无法走索引；3.隐式类型转换，字符串字段传数字、数字字段传字符串；4.or连接条件中，部分字段无索引；5.数据量过少、数据倾斜严重，优化器认为全表扫描更快，自动放弃索引；6.组合索引不遵循最左匹配原则。15.组合索引最左匹配原则是什么答案：组合索引遵循从左到右依次匹配的规则，查询条件必须包含最左侧索引字段，后续字段才能生效。例如索引(a,b,c)：查询a、a+b、a+b+c可以走索引；只查b、只查c、b+c无法走组合索引。日常建索引，把高频查询、区分度高的字段放在最左侧。16.如何优化慢SQL？说一下完整优化思路答案：1.查看执行计划，定位全表扫描、键查找、索引缺失问题；2.检查是否索引失效，修正函数运算、模糊查询、类型转换问题；3.按需建立索引，优先高频查询、筛选度高的字段，建立组合索引、覆盖索引；4.优化SQL语句，避免嵌套子查询、避免select*、减少不必要的关联；5.大表分页、大表查询做分页限制，减少数据扫描范围；6.优化事务，减少锁阻塞，避免长事务导致查询卡顿。五、常用函数与实战技巧17.row_number、rank、dense_rank排序函数区别答案：row_number：连续不重复排名，1、2、3、4，无论数据是否重复，序号递增，适合分页、取前N条；rank：跳跃排名，重复数据名次一致，后续名次跳跃，比如1、1、3、4，适合成绩排名；dense_rank：连续排名，重复数据名次一致，后续名次不跳跃，比如1、1、2、3。18.日期函数getdate()、dateadd、datediff用法答案：getdate()：获取当前系统时间；dateadd(单位,增量,时间)：时间加减，比如加一天dateadd(day,1,getdate())；datediff(单位,开始时间,结束时间)：计算时间差，常用于统计天数、小时数。六、数据库设计与运维问题19.超大型数据表怎么优化？分表思路答案：单表数据量千万级以上会出现查询卡顿、索引失效，常用优化方案：1.水平分表：按时间、ID、用户维度拆分，比如按月拆分订单表；2.垂直分表：将大字段、不常用字段拆分单独表，减少单表数据体积；3.冷热数据分离：历史冷数据归档到备份表，