SQLServer 數據庫開發頂級技巧
2024-08-31 00:59:32
供稿：網友
確保代碼中的數據類型與數據庫中的列類型保持一致
確保您的應用程序各層數據類型保持一致是非常重要的。例如，如果一列的數據類型為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就是一個很好的例子，可以看看。
盡量減少網絡會話
這個技巧主要針對從數據庫取數據的網絡應用程序。缺乏經驗的開發人員常常意識不到數據庫調用是代價很高的操作。對于小應用程序而言，這不是什么大問題。但是，由于很多網站變得非?；鸨瑢е聰狄郧в嫷挠脩敉瑫r在線，那么您就有必要提前考慮它的可擴展性與網頁加載時間的優化問題了。
我曾經看到過的網頁有多達15個數據庫調用，而大多數正在執行的存儲過程就是為了返回單獨的一行或一個值。需要牢記的是在SQL Server中一個單獨的存儲過程能夠返回多個結果集。在一個存儲過程中，您可以使用ADO.NET中的DataSet對象以及把DataTable對象組成一個集合。
