SQL/SQL Server 常見面試題

1.怎樣去除Table中的重複行？請列舉至少三種方法，並分析效率。

例如有如下表Salary：

select * from Salary order by UserId

方法一：

select distinct * into #tempSalary from Salarydelete Salaryinsert Salary select * from #tempSalarydrop 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 Salaryadd 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 Salarydrop 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對外，即對使用者是透明的，使用者使用起來仍然是對一個表在操作。但實際我們在儲存的時候已經做了分區最佳化。