1、 SQL SERVER性能優(yōu)化綜述 近期因工作需要，希望比較全面的總結(jié)下SQL SERVER數(shù)據(jù)庫性能優(yōu)化相關(guān)的注意事項，在網(wǎng)上搜索了一下,發(fā)現(xiàn)很多文章,有的都列出了上百條,但是仔細(xì)看發(fā)現(xiàn)，有很多似是而非或者過時(可能對SQL SERVER6.5以前的版本或者ORACLE是適用的)的信息，只好自己根據(jù)以前的經(jīng)驗和測試結(jié)果進(jìn)行總結(jié)了。我始終認(rèn)為，一個系統(tǒng)的性能的提高，不單單是試運(yùn)行或者維護(hù)階段的性能調(diào)優(yōu)的任務(wù)，也不單單是開發(fā)階段的事情，而是在整個軟件生命周期都需要注意，進(jìn)行有效工作才能達(dá)到的。所以我希望按照軟件生命周期的不同階段來總結(jié)數(shù)據(jù)庫性能優(yōu)化相關(guān)的注意事項。一、 分析階段一般來說，在系統(tǒng)分

2、析階段往往有太多需要關(guān)注的地方，系統(tǒng)各種功能性、可用性、可靠性、安全性需求往往吸引了我們大部分的注意力，但是，我們必須注意，性能是很重要的非功能性需求，必須根據(jù)系統(tǒng)的特點(diǎn)確定其實(shí)時性需求、響應(yīng)時間的需求、硬件的配置等。最好能有各種需求的量化的指標(biāo)。另一方面，在分析階段應(yīng)該根據(jù)各種需求區(qū)分出系統(tǒng)的類型，大的方面，區(qū)分是OLTP（聯(lián)機(jī)事務(wù)處理系統(tǒng)）和OLAP（聯(lián)機(jī)分析處理系統(tǒng)）。二、 設(shè)計階段設(shè)計階段可以說是以后系統(tǒng)性能的關(guān)鍵階段，在這個階段，有一個關(guān)系到以后幾乎所有性能調(diào)優(yōu)的過程數(shù)據(jù)庫設(shè)計。在數(shù)據(jù)庫設(shè)計完成后，可以進(jìn)行初步的索引設(shè)計，好的索引設(shè)計可以指導(dǎo)編碼階段寫出高效率的代碼，為整個系統(tǒng)的性能

3、打下良好的基礎(chǔ)。以下是性能要求設(shè)計階段需要注意的：1、 數(shù)據(jù)庫邏輯設(shè)計的規(guī)化數(shù)據(jù)庫邏輯設(shè)計的規(guī)化就是我們一般所說的式，我們可以這樣來簡單理解式：第1規(guī)：沒有重復(fù)的組或多值的列，這是數(shù)據(jù)庫設(shè)計的最低要求。 第2規(guī): 每個非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于一個組合式主關(guān)鍵字的某些組成部分。消除部分依賴，大部分情況下，數(shù)據(jù)庫設(shè)計都應(yīng)該達(dá)到第二式。第3規(guī): 一個非關(guān)鍵字段不能依賴于另一個非關(guān)鍵字段。消除傳遞依賴，達(dá)到第三式應(yīng)該是系統(tǒng)部分表的要求，除非一些特殊作用的表。更高的式要求這里就不再作介紹了，個人認(rèn)為，如果全部達(dá)到第二式，大部分達(dá)到第三式，系統(tǒng)會產(chǎn)生較少的列和較多的表，因而減少了數(shù)據(jù)冗余，

4、也利于性能的提高。2、 合理的冗余完全按照規(guī)化設(shè)計的系統(tǒng)幾乎是不可能的，除非系統(tǒng)特別的小，在規(guī)化設(shè)計后，有計劃地加入冗余是必要的。冗余可以是冗余數(shù)據(jù)庫、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是為了編程方便而增加，也可以是為了性能的提高而增加。從性能角度來說，冗余數(shù)據(jù)庫可以分散數(shù)據(jù)庫壓力，冗余表可以分散數(shù)據(jù)量大的表的并發(fā)壓力，也可以加快特殊查詢的速度，冗余字段可以有效減少數(shù)據(jù)庫表的連接，提高效率。3、 主鍵的設(shè)計主鍵是必要的，SQL SERVER的主鍵同時是一個唯一索引，而且在實(shí)際應(yīng)用中，我們往往選擇最小的鍵組合作為主鍵，所以主鍵往往適合作為表的聚集索引。聚集索引對查詢的

5、影響是比較大的，這個在下面索引的敘述。在有多個鍵的表，主鍵的選擇也比較重要，一般選擇總的長度小的鍵，小的鍵的比較速度快，同時小的鍵可以使主鍵的B樹結(jié)構(gòu)的層次更少。主鍵的選擇還要注意組合主鍵的字段次序，對于組合主鍵來說，不同的字段次序的主鍵的性能差別可能會很大，一般應(yīng)該選擇重復(fù)率低、單獨(dú)或者組合查詢可能性大的字段放在前面。4、 外鍵的設(shè)計外鍵作為數(shù)據(jù)庫對象，很多人認(rèn)為麻煩而不用，實(shí)際上，外鍵在大部分情況下是很有用的，理由是：外鍵是最高效的一致性維護(hù)方法，數(shù)據(jù)庫的一致性要求，依次可以用外鍵、CHECK約束、規(guī)則約束、觸發(fā)器、客戶端程序，一般認(rèn)為，離數(shù)據(jù)越近的方法效率越高。謹(jǐn)慎使用級聯(lián)刪除和級聯(lián)更新

6、，級聯(lián)刪除和級聯(lián)更新作為SQL SERVER 2000當(dāng)年的新功能，在2005作了保留，應(yīng)該有其可用之處。我這里說的謹(jǐn)慎，是因為級聯(lián)刪除和級聯(lián)更新有些突破了傳統(tǒng)的關(guān)于外鍵的定義，功能有點(diǎn)太過強(qiáng)大，使用前必須確定自己已經(jīng)把握好其功能圍，否則，級聯(lián)刪除和級聯(lián)更新可能讓你的數(shù)據(jù)莫名其妙的被修改或者丟失。從性能看級聯(lián)刪除和級聯(lián)更新是比其他方法更高效的方法。5、 字段的設(shè)計字段是數(shù)據(jù)庫最基本的單位，其設(shè)計對性能的影響是很大的。需要注意如下：A、數(shù)據(jù)類型盡量用數(shù)字型，數(shù)字型的比較比字符型的快很多。B、 數(shù)據(jù)類型盡量小，這里的盡量小是指在滿足可以預(yù)見的未來需求的前提下的。C、 盡量不要允許NULL，除非必要

7、，可以用NOT NULL+DEFAULT代替。D、少用TEXT和IMAGE，二進(jìn)制字段的讀寫是比較慢的，而且，讀取的方法也不多，大部分情況下最好不用。E、 自增字段要慎用，不利于數(shù)據(jù)遷移。6、 數(shù)據(jù)庫物理存儲和環(huán)境的設(shè)計在設(shè)計階段，可以對數(shù)據(jù)庫的物理存儲、操作系統(tǒng)環(huán)境、網(wǎng)絡(luò)環(huán)境進(jìn)行必要的設(shè)計，使得我們的系統(tǒng)在將來能適應(yīng)比較多的用戶并發(fā)和比較大的數(shù)據(jù)量。這里需要注意文件組的作用，適用文件組可以有效把I/O操作分散到不同的物理硬盤，提高并發(fā)能力。7、 系統(tǒng)設(shè)計整個系統(tǒng)的設(shè)計特別是系統(tǒng)結(jié)構(gòu)設(shè)計對性能是有很大影響的，對于一般的OLTP系統(tǒng)，可以選擇C/S結(jié)構(gòu)、三層的C/S結(jié)構(gòu)等，不同的系統(tǒng)結(jié)構(gòu)其性能的

8、關(guān)鍵也有所不同。系統(tǒng)設(shè)計階段應(yīng)該歸納一些業(yè)務(wù)邏輯放在數(shù)據(jù)庫編程實(shí)現(xiàn)，數(shù)據(jù)庫編程包括數(shù)據(jù)庫存儲過程、觸發(fā)器和函數(shù)。用數(shù)據(jù)庫編程實(shí)現(xiàn)業(yè)務(wù)邏輯的好處是減少網(wǎng)絡(luò)流量并可更充分利用數(shù)據(jù)庫的預(yù)編譯和緩存功能。8、 索引的設(shè)計在設(shè)計階段，可以根據(jù)功能和性能的需求進(jìn)行初步的索引設(shè)計，這里需要根據(jù)預(yù)計的數(shù)據(jù)量和查詢來設(shè)計索引，可能與將來實(shí)際使用的時候會有所區(qū)別。關(guān)于索引的選擇，應(yīng)改主意：A、 根據(jù)數(shù)據(jù)量決定哪些表需要增加索引，數(shù)據(jù)量小的可以只有主鍵。B、 根據(jù)使用頻率決定哪些字段需要建立索引，選擇經(jīng)常作為連接條件、篩選條件、聚合查詢、排序的字段作為索引的候選字段。C、 把經(jīng)常一起出現(xiàn)的字段組合在一起，組成組合索

9、引，組合索引的字段順序與主鍵一樣，也需要把最常用的字段放在前面，把重復(fù)率低的字段放在前面。D、 一個表不要加太多索引，因為索引影響插入和更新的速度。三、 編碼階段編碼階段是本文的重點(diǎn)，因為在設(shè)計確定的情況下，編碼的質(zhì)量幾乎決定了整個系統(tǒng)的質(zhì)量。編碼階段首先是需要所有程序員有性能意識，也就是在實(shí)現(xiàn)功能同時有考慮性能的思想，數(shù)據(jù)庫是能進(jìn)行集合運(yùn)算的工具，我們應(yīng)該盡量的利用這個工具，所謂集合運(yùn)算實(shí)際是批量運(yùn)算，就是盡量減少在客戶端進(jìn)行大數(shù)據(jù)量的循環(huán)操作，而用SQL語句或者存儲過程代替。關(guān)于思想和意識，很難說得很清楚，需要在編程過程中來體會。下面羅列一些編程階段需要注意的事項：1、 只返回需要的數(shù)據(jù)返

10、回數(shù)據(jù)到客戶端至少需要數(shù)據(jù)庫提取數(shù)據(jù)、網(wǎng)絡(luò)傳輸數(shù)據(jù)、客戶端接收數(shù)據(jù)以及客戶端處理數(shù)據(jù)等環(huán)節(jié)，如果返回不需要的數(shù)據(jù)，就會增加服務(wù)器、網(wǎng)絡(luò)和客戶端的無效勞動，其害處是顯而易見的，避免這類事件需要注意：A、橫向來看，不要寫SELECT *的語句，而是選擇你需要的字段。B、 縱向來看，合理寫WHERE子句，不要寫沒有WHERE的SQL語句。C、 注意SELECT INTO后的WHERE子句，因為SELECT INTO把數(shù)據(jù)插入到臨時表，這個過程會鎖定一些系統(tǒng)表，如果這個WHERE子句返回的數(shù)據(jù)過多或者速度太慢，會造成系統(tǒng)表長期鎖定，諸塞其他進(jìn)程。D、對于聚合查詢，可以用HAVING子句進(jìn)一步限定返回的

11、行。2、 盡量少做重復(fù)的工作這一點(diǎn)和上一點(diǎn)的目的是一樣的，就是盡量減少無效工作，但是這一點(diǎn)的側(cè)重點(diǎn)在客戶端程序，需要注意的如下：A、 控制同一語句的多次執(zhí)行，特別是一些基礎(chǔ)數(shù)據(jù)的多次執(zhí)行是很多程序員很少注意的。B、 減少多次的數(shù)據(jù)轉(zhuǎn)換，也許需要數(shù)據(jù)轉(zhuǎn)換是設(shè)計的問題，但是減少次數(shù)是程序員可以做到的。C、 杜絕不必要的子查詢和連接表，子查詢在執(zhí)行計劃一般解釋成外連接，多余的連接表帶來額外的開銷。D、 合并對同一表同一條件的多次UPDATE，比如1.UPDATE EMPLOYEE SET FNAME=HAIWER WHERE EMP_ID= VPA30890F2.3.UPDATE EMPLOYEE

12、SET LNAME=YANG WHERE EMP_ID= VPA30890F4.5.這兩個語句應(yīng)該合并成以下一個語句1.UPDATE EMPLOYEE SET FNAME=HAIWER,LNAME=YANG 2.WHERE EMP_ID= VPA30890FE、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是性能差別是很大的。F、 不要寫一些沒有意義的查詢，比如 SELECT * FROM EMPLOYEE WHERE 1=23、 注意事務(wù)和鎖事務(wù)是數(shù)據(jù)庫應(yīng)用中和重要的工具，它有原子性、一致性、隔離性、持久性這四個屬性，很多操作我們都需要利用事務(wù)來保證數(shù)據(jù)

13、的正確性。在使用事務(wù)中我們需要做到盡量避免死鎖、盡量減少阻塞。具體以下方面需要特別注意：A、事務(wù)操作過程要盡量小，能拆分的事務(wù)要拆分開來。B、 事務(wù)操作過程不應(yīng)該有交互，因為交互等待的時候，事務(wù)并未結(jié)束，可能鎖定了很多資源。C、 事務(wù)操作過程要按同一順序訪問對象。D、提高事務(wù)中每個語句的效率，利用索引和其他方法提高每個語句的效率可以有效地減少整個事務(wù)的執(zhí)行時間。E、 盡量不要指定鎖類型和索引，SQL SERVER允許我們自己指定語句使用的鎖類型和索引，但是一般情況下，SQL SERVER優(yōu)化器選擇的鎖類型和索引是在當(dāng)前數(shù)據(jù)量和查詢條件下是最優(yōu)的，我們指定的可能只是在目前情況下更有，但是數(shù)據(jù)量和

14、數(shù)據(jù)分布在將來是會變化的。F、 查詢時可以用較低的隔離級別，特別是報表查詢的時候，可以選擇最低的隔離級別（未提交讀）。4、 注意臨時表和表變量的用法在復(fù)雜系統(tǒng)中，臨時表和表變量很難避免，關(guān)于臨時表和表變量的用法，需要注意：A、如果語句很復(fù)雜，連接太多，可以考慮用臨時表和表變量分步完成。B、 如果需要多次用到一個大表的同一部分?jǐn)?shù)據(jù)，考慮用臨時表和表變量暫存這部分?jǐn)?shù)據(jù)。C、 如果需要綜合多個表的數(shù)據(jù)，形成一個結(jié)果，可以考慮用臨時表和表變量分步匯總這多個表的數(shù)據(jù)。D、其他情況下，應(yīng)該控制臨時表和表變量的使用。E、 關(guān)于臨時表和表變量的選擇，很多說法是表變量在存，速度快，應(yīng)該首選表變量，但是在實(shí)際使用

15、中發(fā)現(xiàn)，這個選擇主要考慮需要放在臨時表的數(shù)據(jù)量，在數(shù)據(jù)量較多的情況下，臨時表的速度反而更快。F、 關(guān)于臨時表產(chǎn)生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，我們做過測試，一般情況下，SELECT INTO會比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO會鎖定TEMPDB的系統(tǒng)表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用戶并發(fā)環(huán)境下，容易阻塞其他進(jìn)程，所以我的建議是，在并發(fā)系統(tǒng)中，盡量使用CREATE TABLE + INSERT INTO，而大數(shù)據(jù)量的單個語句使用中，使用S

16、ELECT INTO。G、 注意排序規(guī)則，用CREATE TABLE建立的臨時表，如果不指定字段的排序規(guī)則，會選擇TEMPDB的默認(rèn)排序規(guī)則，而不是當(dāng)前數(shù)據(jù)庫的排序規(guī)則。如果當(dāng)前數(shù)據(jù)庫的排序規(guī)則和TEMPDB的排序規(guī)則不同，連接的時候就會出現(xiàn)排序規(guī)則的沖突錯誤。一般可以在CREATE TABLE建立臨時表時指定字段的排序規(guī)則為DATABASE_DEFAULT來避免上述問題。5、 子查詢的用法子查詢是一個 SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語句或其它子查詢中。任何允許使用表達(dá)式的地方都可以使用子查詢。子查詢可以使我們的編程靈活多樣，可以用來實(shí)現(xiàn)

17、一些特殊的功能。但是在性能上，往往一個不合適的子查詢用法會形成一個性能瓶頸。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫作相關(guān)子查詢。相關(guān)子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入。關(guān)于相關(guān)子查詢，應(yīng)該注意：A、NOT IN、NOT EXISTS的相關(guān)子查詢可以改用LEFT JOIN代替寫法。比如：1.SELECT PUB_NAME2.FROM PUBLISHERS3.WHERE PUB_ID NOT IN4. (SELECT PUB_ID5. FROM TITLES6. WHERE TYPE = 'BUSINESS') 可以改寫成：1.

18、SELECT A.PUB_NAME2.FROM PUBLISHERS A LEFT JOIN TITLES B3.ON B.TYPE = 'BUSINESS' AND4. A.PUB_ID=B. PUB_ID5.WHERE B.PUB_ID IS NULL1.SELECT TITLE2.FROM TITLES3.WHERE NOT EXISTS4. (SELECT TITLE_ID5. FROM SALES6. WHERE TITLE_ID = TITLES.TITLE_ID)可以改寫成：1.SELECT TITLE2.FROM TITLES LEFT JOIN SALES3.

19、ON SALES.TITLE_ID = TITLES.TITLE_ID4.WHERE SALES.TITLE_ID IS NULLB、 如果保證子查詢沒有重復(fù) ，IN、EXISTS的相關(guān)子查詢可以用INNER JOIN 代替。比如：1.SELECT PUB_NAME2.FROM PUBLISHERS3.WHERE PUB_ID IN4. (SELECT PUB_ID5. FROM TITLES6. WHERE TYPE = 'BUSINESS')可以改寫成：1.SELECT DISTINCT A.PUB_NAME2.FROM PUBLISHERS A INNER JOIN TI

20、TLES B3.ON B.TYPE = 'BUSINESS' AND4. A.PUB_ID=B. PUB_IDC、 IN的相關(guān)子查詢用EXISTS代替，比如1.SELECT PUB_NAME2.FROM PUBLISHERS3.WHERE PUB_ID IN4. (SELECT PUB_ID5. FROM TITLES6. WHERE TYPE = 'BUSINESS')可以用下面語句代替：1.SELECT PUB_NAME2.FROM PUBLISHERS3.WHERE EXISTS4. (SELECT 15. FROM TITLES6. WHERE TYPE

21、 = 'BUSINESS' AND7. PUB_ID= PUBLISHERS.PUB_ID)D、不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFT JOIN或者EXISTS，比如有人寫這樣的語句：1.SELECT JOB_DESC FROM JOBS2.WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0應(yīng)該改成：1.SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE 2.ON EMPLOYEE.JOB_ID=JOBS.JOB_ID3.WHER

22、E EMPLOYEE.EMP_ID IS NULL1.SELECT JOB_DESC FROM JOBS2.WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0應(yīng)該改成：1.SELECT JOB_DESC FROM JOBS2.WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)6、 慎用游標(biāo)數(shù)據(jù)庫一般的操作是集合操作，也就是對由WHERE子句和選擇列確定的結(jié)果集作集合操作，游標(biāo)是提供的一個非集合操作的途徑。一般情況下，游標(biāo)實(shí)現(xiàn)的功能

23、往往相當(dāng)于客戶端的一個循環(huán)實(shí)現(xiàn)的功能，所以，大部分情況下，我們把游標(biāo)功能搬到客戶端。游標(biāo)是把結(jié)果集放在服務(wù)器存，并通過循環(huán)一條一條處理記錄，對數(shù)據(jù)庫資源（特別是存和鎖資源）的消耗是非常大的，所以，我們應(yīng)該只有在沒有其他方法的情況下才使用游標(biāo)。另外，我們可以用SQL SERVER的一些特性來代替游標(biāo)，達(dá)到提高速度的目的。A、字符串連接的例子這是論壇經(jīng)常有的例子，就是把一個表符合條件的記錄的某個字符串字段連接成一個變量。比如需要把JOB_ID=10的EMPLOYEE的FNAME連接在一起，用逗號連接，可能最容易想到的是用游標(biāo)：1. DECLARE NAME VARCHAR(20)2. DECLAR

24、E NAME VARCHAR(1000)3. DECLARE NAME_CURSOR CURSOR FOR4. SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID5. OPEN NAME_CURSOR6. FETCH NEXT FROM RNAME_CURSOR INTO NAME7. WHILE FETCH_STATUS = 08. BEGIN9. SET NAMES = ISNULL(NAMES+,)+NAME10. FETCH NEXT FROM NAME_CURSOR INTO NAME 11. END12. CLOSE

25、 NAME_CURSOR13. DEALLOCATE NAME_CURSOR可以如下修改，功能相同：1. DECLARE NAME VARCHAR(1000)2. SELECT NAMES = ISNULL(NAMES+,)+FNAME3. FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_IDB、 用CASE WHEN 實(shí)現(xiàn)轉(zhuǎn)換的例子很多使用游標(biāo)的原因是因為有些處理需要根據(jù)記錄的各種情況需要作不同的處理，實(shí)際上這種情況，我們可以用CASE WHEN語句進(jìn)行必要的判斷處理，而且CASE WHEN是可以嵌套的。比如:表結(jié)構(gòu):1.CREATE TABLE 料件表

26、(2.料號 VARCHAR(30),3.名稱 VARCHAR(100),4.主單位 VARCHAR(20),5.單位1 VARCHAR(20),6.單位1參數(shù) NUMERIC(18,4),7.單位2 VARCHAR(20),8.單位2參數(shù) NUMERIC(18,4)9.)10.11.GO12.13.CREATE TABLE 入庫表(14.時間 DATETIME,15.料號 VARCHAR(30),16.單位 INT,17.入庫數(shù)量 NUMERIC(18,4),18.損壞數(shù)量 NUMERIC(18,4)19.)20.21.GO其中，單位字段可以是0，1，2，分別代表主單位、單位1、單位2，很多計

27、算需要統(tǒng)一單位，統(tǒng)一單位可以用游標(biāo)實(shí)現(xiàn)：1.DECLARE 料號 VARCHAR(30),2. 單位 INT,3. 參數(shù) NUMERIC(18,4),4.5.DECLARE CUR CURSOR FOR6. SELECT 料號,單位 FROM 入庫表 WHERE 單位 <>07.OPEN CUR8.FETCH NEXT FROM CUR INTO 料號,單位9.WHILE FETCH_STATUS<>-110.BEGIN11. IF 單位=112. BEGIN13. SET 參數(shù)=(SELECT 單位1參數(shù) FROM 料件表 WHERE 料號 =料號)14. UPDAT

28、E 入庫表 SET 數(shù)量=數(shù)量*參數(shù),損壞數(shù)量=損壞數(shù)量*參數(shù),單位=1 WHERE CURRENT OF CUR15. END16. IF 單位=217. BEGIN18. SET 參數(shù)=(SELECT 單位1參數(shù) FROM 料件表 WHERE 料號 =料號)19. UPDATE 入庫表 SET 數(shù)量=數(shù)量*參數(shù),損壞數(shù)量=損壞數(shù)量*參數(shù),單位=1 WHERE CURRENT OF CUR20. END21. FETCH NEXT FROM CUR INTO 料號,單位22.END23.CLOSE CUR24.DEALLOCATE CUR 可以改寫成：1.UPDATE A SET 2.數(shù)量=

29、CASE A.單位 WHEN 1 THEN A.數(shù)量*B. 單位1參數(shù)3. WHEN 2 THEN A.數(shù)量*B. 單位2參數(shù)4. ELSE A.數(shù)量5.END, 6.損壞數(shù)量= CASE A.單位 WHEN 1 THEN A. 損壞數(shù)量*B. 單位1參數(shù)7. WHEN 2 THEN A. 損壞數(shù)量*B. 單位2參數(shù)8. ELSE A. 損壞數(shù)量9.END,10.單位=1 11.FROM入庫表 A, 料件表 B12.WHERE A.單位<>1 AND13. A.料號=B.料號C、 變量參與的UPDATE語句的例子SQL ERVER的語句比較靈活，變量參與的UPDATE語句可以實(shí)現(xiàn)一

30、些游標(biāo)一樣的功能，比如：在1.SELECT A,B,C,CAST(NULL AS INT) AS 序號2.INTO #T3.FROM 表4.ORDER BY A ,NEWID()產(chǎn)生臨時表后，已經(jīng)按照A字段排序，但是在A相同的情況下是亂序的，這時如果需要更改序號字段為按照A字段分組的記錄序號，就只有游標(biāo)和變量參與的UPDATE語句可以實(shí)現(xiàn)了，這個變量參與的UPDATE語句如下：1.DECLARE A INT2.DECLARE 序號 INT3.UPDATE #T SET4. 序號=CASE WHEN A=A THEN 序號+1 ELSE 1 END,5. A=A,6. 序號=序號D、如果必須使用

31、游標(biāo)，注意選擇游標(biāo)的類型，如果只是循環(huán)取數(shù)據(jù)，那就應(yīng)該用只進(jìn)游標(biāo)（選項FAST_FORWARD），一般只需要靜態(tài)游標(biāo)（選項STATIC）。E、 注意動態(tài)游標(biāo)的不確定性，動態(tài)游標(biāo)查詢的記錄集數(shù)據(jù)如果被修改，會自動刷新游標(biāo)，這樣使得動態(tài)游標(biāo)有了不確定性，因為在多用戶環(huán)境下，如果其他進(jìn)程或者本身更改了紀(jì)錄，就可能刷新游標(biāo)的記錄集。7、 盡量使用索引建立索引后，并不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強(qiáng)制指定索引，索引的選擇和使用方法是SQLSERVER的優(yōu)化器自動作的選擇，而它選擇的根據(jù)是查詢語句的條件以及相關(guān)表的統(tǒng)計信息，這就要求我們

32、在寫SQL語句的時候盡量使得優(yōu)化器可以使用索引。為了使得優(yōu)化器能高效使用索引，寫語句的時候應(yīng)該注意：A、不要對索引字段進(jìn)行運(yùn)算，而要想辦法做變換，比如SELECT ID FROM T WHERE NUM/2=100應(yīng)改為:SELECT ID FROM T WHERE NUM=100*2SELECT ID FROM T WHERE NUM/2=NUM1如果NUM有索引應(yīng)改為:SELECT ID FROM T WHERE NUM=NUM1*2如果NUM1有索引則不應(yīng)該改。發(fā)現(xiàn)過這樣的語句：1.SELECT 年,月,金額 FROM 結(jié)余表2.WHERE 100*年+月=2007*100+10應(yīng)該改為

33、：1.SELECT 年,月,金額 FROM 結(jié)余表2.WHERE 年=2007 AND3. 月=10B、 不要對索引字段進(jìn)行格式轉(zhuǎn)換日期字段的例子：WHERE CONVERT(VARCHAR(10), 日期字段,120)=2008-08-15應(yīng)該改為WHERE日期字段=2008-08-15 AND 日期字段<2008-08-16ISNULL轉(zhuǎn)換的例子：WHERE ISNULL(字段,)<>應(yīng)改為:WHERE字段<>WHERE ISNULL(字段,)=不應(yīng)修改WHERE ISNULL(字段,F) =T應(yīng)改為: WHERE字段=TWHERE ISNULL(字段,F)&

34、lt;>T不應(yīng)修改C、 不要對索引字段使用函數(shù)WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'應(yīng)改為: WHERE NAME LIKE 'ABC%'日期查詢的例子：WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0應(yīng)改為:WHERE 日期 >='2005-11-30' AND 日期 <'2005-12-1WHERE DATEDIFF(DAY, 日期,'2005-11-30')&

35、gt;0應(yīng)改為:WHERE 日期 <'2005-11-30WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0應(yīng)改為:WHERE 日期 <'2005-12-01WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0應(yīng)改為:WHERE 日期>='2005-12-01WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0應(yīng)改為:WHERE 日期>='2005-11-30D、不要對索引字段進(jìn)行多字段連接比如：

36、WHERE FAME+ .+LNAME=HAIWEI.YANG 應(yīng)改為: WHERE FNAME=HAIWEI AND LNAME=YANG8、 注意連接條件的寫法多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的時候需要特別的注意。A、多表連接的時候，連接條件必須寫全，寧可重復(fù)，不要缺漏。B、 連接條件盡量使用聚集索引C、 注意ON部分條件和WHERE部分條件的區(qū)別9、 其他需要注意的地方經(jīng)驗表明，問題發(fā)現(xiàn)的越早解決的成本越低，很多性能問題可以在編碼階段就發(fā)現(xiàn)，為了提早發(fā)現(xiàn)性能問題，需要注意：A、程序員注意、關(guān)心各表的數(shù)據(jù)量。B、 編碼過程和單元測試過程盡量用數(shù)據(jù)量較大

37、的數(shù)據(jù)庫測試，最好能用實(shí)際數(shù)據(jù)測試。C、 每個SQL語句盡量簡單D、不要頻繁更新有觸發(fā)器的表的數(shù)據(jù)E、 注意數(shù)據(jù)庫函數(shù)的限制以及其性能10、 學(xué)會分辯SQL語句的優(yōu)劣自己分辨SQL語句的優(yōu)劣非常重要，只有自己能分辨優(yōu)劣才能寫出高效的語句。A、 查看SQL語句的執(zhí)行計劃，可以在查詢分析其使用CTRL+L圖形化的顯示執(zhí)行計劃，一般應(yīng)該注意百分比最大的幾個圖形的屬性，把鼠標(biāo)移動到其上面會顯示這個圖形的屬性，需要注意預(yù)計成本的數(shù)據(jù)，也要注意其標(biāo)題，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE

38、SCAN等，其中出現(xiàn)SCAN說明語句有優(yōu)化的余地。也可以用語句SET SHOWPLAN_ALL ON要執(zhí)行的語句SET SHOWPLAN_ALL OFF查看執(zhí)行計劃的文本詳細(xì)信息。B、 用事件探查器跟蹤系統(tǒng)的運(yùn)行，可疑跟蹤到執(zhí)行的語句，以及所用的時間，CPU用量以及I/O數(shù)據(jù)，從而分析語句的效率。C、 可以用WINDOWS的系統(tǒng)性能檢測器，關(guān)注CPU、I/O參數(shù)四、 測試、試運(yùn)行、維護(hù)階段測試的主要任務(wù)是發(fā)現(xiàn)并修改系統(tǒng)的問題，其中性能問題也是一個重要的方面。重點(diǎn)應(yīng)該放在發(fā)現(xiàn)有性能問題的地方，并進(jìn)行必要的優(yōu)化。主要進(jìn)行語句優(yōu)化、索引優(yōu)化等。試運(yùn)行和維護(hù)階段是在實(shí)際的環(huán)境下運(yùn)行系統(tǒng)，發(fā)現(xiàn)的問題圍更

39、廣，可能涉及操作系統(tǒng)、網(wǎng)絡(luò)以及多用戶并發(fā)環(huán)境出現(xiàn)的問題，其優(yōu)化也擴(kuò)展到操作系統(tǒng)、網(wǎng)絡(luò)以及數(shù)據(jù)庫物理存儲的優(yōu)化。這個階段的優(yōu)花方法在這里不再展開，只說明下索引維護(hù)的方法：A、 可以用DBCC DBREINDEX語句或者SQL SERVER維護(hù)計劃設(shè)定定時進(jìn)行索引重建，索引重建的目的是提高索引的效能。B、 可以用語句UPDATE STATISTICS或者SQL SERVER維護(hù)計劃設(shè)定定時進(jìn)行索引統(tǒng)計信息的更新，其目的是使得統(tǒng)計信息更能反映實(shí)際情況，從而使得優(yōu)化器選擇更合適的索引。C、 可以用DBCC CHECKDB或者DBCC CHECKTABLE語句檢查數(shù)據(jù)庫表和索引是否有問題，這兩個語句也能修復(fù)一般的問題。D、 五、 網(wǎng)上資料中一些說法的個人不同意見1、 “應(yīng)盡量避免在 WHERE 子句中對字段進(jìn)行 NULL 值判斷，否則將導(dǎo)致引