SQL/SQL Server 常见面试题

1.怎样去除Table中的重复行？请列举至少三种方法，并分析效率。

例如有如下表Salary：

select * from Salary order by UserId

方法一：

select distinct * into #tempSalary from Salary
delete Salary
insert Salary select * from #tempSalary
drop table #tempSalary

方法二：

with cts as (select ROW_NUMBER() over (partition by UserID, Salary, Dept order by UserID) rn from Salary)
delete from cts where rn > 1

执行结果：

此方法为比较优雅的方法，涉及到row_number over，order by以及partition by。partition by是按照给出的column(s)为group进行分组，并且允许对组进行编号（编号注意顺序，即order by）。相同的记录会产生大于编号为1的记录编号。

例如上例中，单独执行：

select userid,ROW_NUMBER() over (partition by UserID, Salary, Dept order by UserID) rn from Salary

方法三：

• 使用checksum作为hash函数对记录计算哈希值作为新的一列H_ID;

alter table Salary
add H_ID as checksum(userid,salary,dept)

  TO 

• 按着1中的方法，对于H_ID重复的记录选取唯一插入到临时表；

select distinct userid,Salary,dept into #duplicatedSalary from Salary where H_ID in
(select H_ID from Salary group by H_ID having COUNT(*) > 1)

得到的临时表如下：

• 根据H_ID删除原表记录；

delete from Salary where H_ID in (select H_ID from Salary group by H_ID having COUNT(*) > 1)

删除后得到：

重复行已经都被删掉了。

• 将零时表数据插回原表；

insert Salary select * from #duplicatedSalary

• 删除哈希列以及零时表。

alter table Salary
drop column H_ID

drop table #duplicatedSalary

最后得到去除掉重复记录的结果：

    至此，第三种方法介绍全部结束。该方法在效率上肯定比第一个快很多，尤其是在数据量大的情况下，会体现出良好的优良性。因为它只删除重复记录，对非重复记录不做更改。

2.什么是索引，有哪几种索引，索引是怎么实现的？

    INDEX，是创建在数据库上的对象，它提供到数据的快速访问通道。索引存储利用了B树数据结构。分为聚集索引（物理索引，好比英文字典的索引）和非聚集索引（好比中文字典的偏旁部首索引，存储的实际是一张规则表，不能直接获得想要的值）。

3.什么是存储过程，为什么存储过程的调用会比较有效率？

• 直接定义存储过程：它是server上编译好的SQL片段。

效率高的原因：

• 第一，因为它是Server上已经编译好的片段，不需要再次编译，直接可以执行，所以更快；
• 第二，省去了从APP服务器/或者client传输sql语句到Server的时间；
• 第三，存储过程可以将sql的执行计划进行缓存，从而重用执行计划。

4.什么是Transaction，它有哪些特性？如果Transaction_A中嵌套有Transaction_B，当Transaction_A执行了一部分开始之星Transaction_B，Transaction_B执行成功，并提交了结果，而执行剩下部分的Transaction_A时候出现错误，需要对事物进行回滚，请问回滚之后，Transaction_B提交的改变会被回滚么？

    事务是数据库管理系统运行的基本单位，是用户定义的一个数据库操作序列，这些序列要么全做要么全部不做，是一个不可分割的工作单位。具有ACID特性：

• Atomicity，原子性，保证一组操作是原子的，不可分的的一个整体，对与数据库而言，要么全做，要么全不做；
• Consistency，一致性，使数据库从一个一致性状态变到另一个一致性状态。
• Isolation，隔离性，不能被其他事务干扰。
• Durability，永久性。一经提交，改变时永久的。

    根据事务的几大特性回答上述问题，Transaction_A部分失败需要回滚，虽然Transaction_B一经提交，但是它要等待Transaction_A的二次提交才真正提交。所以事务B的操作也会被回滚。Transaction分为普通事务和嵌套事务，其中嵌套事务的提交要等待所有的事务完成以后才能够全部真正提交。

5.sql语句调优有哪些手段？

    主要需要去查看SQL的编译后的执行计划，更具具体表的数据类型、大小特点，使用正确的连接类型。默认会让SQL Server自行选择执行计划，但是有些时候它会选择错误。这时候就需要人工来调优。例如两个表的数据量都很大，这时候做连接(join)的时候就需要使用hash join。

    JOIN类型有：

• HASH JOIN;
• NESTED LOOP
• MERGE

    其中，NESTED LOOP可用于较小的数据量时候，比如几十万条。而当数据量大到百万级以上时，当然需要HASH，HASH JOIN仅能进行等值连接。如果表中的数据有顺序特点，则可以考虑使用MERGE。

6.什么是Partition，它怎么使用？

    Partition是解决大数据存储和操作的一个良好手段。它把表按照规则进行分区存储，每个区存储的数据控制在易操作范围之中。例如数据仓库中存储着3年的数据，其中每一天的数据就多大几百万条，总数据量则达到了十亿的数量级。存储在一个表完全不合理，所以可以按照天做PARTITION，分区存储。

    Partition对外，即对用户是透明的，用户使用起来仍然是对一个表在操作。但实际我们在存储的时候已经做了分区优化。