SQL Server資料庫開發頂級技巧

 無論你的專業水平如何，從其他IT專家那裡學習新的技巧與最佳實務常常都是有益的。本文包含了我遇到過的SQL Server開發的進階技巧。希望其中的一些技巧能夠對您的資料庫開發及管理工作有所協助。

　　確保代碼中的資料類型與資料庫中的列類型保持一致

 

　　確保您的應用程式各層資料類型保持一致是非常重要的。例如，如果一列的資料類型為NVARCHAR（50），那麼，您應該在代碼查詢與預存程序中使用相同類型的局部變數。

 

　　同樣，資料層中的ADO.NET代碼也應該指定相同的資料類型與長度。為什麼這很重要呢?因為如果資料類型與查詢匹配，SQL Server需要先進行資料類型的隱式轉換，以使它們能夠匹配。

 

　　也有一些情況，即使為參照列設定了索引，SQL Server卻不能使用此索引。因此，變數與列類型一致的情況下，您的查詢可能會使用Index Scan而不是Index Seeking，這樣需要執行的時間就更長了。

 

    在批處理中進行大規模更新

 

　　開發人員有時需要對一張表中的一列或多列中的全部或大部分列進行資料修改。通常，對小表而言這並不是一個什麼問題。

 

　　然而，如果表很大的話，您的更新語句將鎖定整張表，使它無法使用，甚至都不能讀取。更有甚者，對一張頻繁變化的表進行更新可能使整個應用程式或網站癱瘓。有時，在極端情況下，一個大的、單個事務將導致交易記錄急劇增長，並最終耗盡資料庫伺服器磁碟空間。

 

　　因此，好的策略是進行分批大規模更新，並結合頻繁的交易記錄備份。以我的經驗看，最好一批10,000至50,000工作量。當您開始考慮應用批量處理時，確定閾值很困難，因為這取決於諸多因素比方說如何使I/O更快，如何使表高效利用等等。

 

　　您可以考慮一個準則。在ADO.NET中，典型的命令逾時時間是30秒左右。當開始更新時，其他進程一直處於等待狀態直到更新結束。因此如果期望更新時間超過20-25秒，您最好進行一個批處理更新。否則，將以應用程式逾時而結束。

 

　　下面這段簡單的代碼展示了如何更新表中的一列，應用的批量大小為10,000：

 

　　WHILE （ 0 = 0 ）

 

　　BEGIN

 

　　UPDATE TOP （ 10000 ）

 

　　Person

 

　　SET Status = 2

 

　　WHERE Status = 1

 

　　IF @@ROWCOUNT = 0

 

　　BREAK

 

　　END

 

　　應用FOR-EACH預存程序

 

　　有些時候您可能需要對某一特定類型的所有對象執行相同的操作。例如，您可能需要對資料庫中的所有表分配特定的許可權。開發人員經常通過指標設定這樣的任務，但是SQL Server中兩個簡單的預存程序可以更容易實現：sp_msForEachTable 與 sp_msForEachDB。

 

　　每個預存程序作為一個參數執行命令。在命令中，您把表名或資料庫名作為一個問號標誌預留位置嵌入到參數中。命令運行時，SQL Server把問號標誌替換為表名或資料庫名，並執行。

 

　　例如，下面的代碼在Server上除TempDB外，對每個資料庫進行全備份：

 

　　EXEC sp_msforeachdb 'IF ''?'' <> ''tempdb'' BACKUP DATABASE ?

 

　　TO DISK=''c:/backups/?.bak'' WITH INIT'

 

　　這是另外一個如何應用這些預存程序的例子。下面的代碼在禁用外鍵後，刪除資料庫所有表中的資料。當然了，當使用這些代碼時，您需要謹慎的練習。

 

　　EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

 

　　EXEC sp_MSForEachTable '

 

　　IF OBJECTPROPERTY（object_id（''?''）, ''TableHasForeignRef''） = 1

 

　　DELETE FROM ?

 

　　else

 

　　TRUNCATE TABLE ?

 

　　'

 

　　EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

 

　　建立資料庫版本

 

　　對開發人員而言，如同對您的應用程式版本化一樣，對資料庫執行數字版本化是一個很好的方法。

 

　　執行版本化並不需要很大的工作量，您只需建立一個包含版本號碼列及時間戳記列的版本表即可。當部署那些指令碼時，您將更好的分配每個指令碼集合的版本號碼，並對版本表進行更新，檢查錯誤與資料庫對比將變得更加容易。您甚至可以對指令碼進行編號，這樣一來如果資料庫中建立的編號不比指令碼中建立的編號高的話，指令碼就不執行。範例資料庫AdventureWorks中的AWBuildVersion就是一個很好的例子，可以看看。

 

　　盡量減少網路會話

 

　　這個技巧主要針對從資料庫取資料的網路應用程式。缺乏經驗的開發人員常常意識不到資料庫調用是代價很高的操作。對於小應用程式而言，這不是什麼大問題。但是，由於很多網站變得非常火爆導致數以千計的使用者同時線上，那麼您就有必要提前考慮它的可擴充性與網頁載入時間的最佳化問題了。

 

　　我曾經看到過的網頁有多達15個資料庫調用，而大多數正在執行的預存程序就是為了返回單獨的一行或一個值。需要牢記的是在SQL Server中一個單獨的預存程序能夠返回多個結果集。在一個預存程序中，您可以使用ADO.NET中的DataSet對象以及把DataTable對象組成一個集合。