數(shù)據(jù)庫(kù)開發(fā)規(guī)范(SQL-SERVER篇)

SQLSERVER數(shù)據(jù)庫(kù)開發(fā)規(guī)范 第20頁(yè)共20頁(yè)數(shù)據(jù)庫(kù)開發(fā)規(guī)范（SQLSERVER篇）目錄第一章命名規(guī)范 41.命名標(biāo)志法 42.數(shù)據(jù)庫(kù)命名 43.數(shù)據(jù)庫(kù)月份庫(kù)、數(shù)據(jù)表日分庫(kù)命名規(guī)則 44.分段數(shù)據(jù)庫(kù)分庫(kù)命名規(guī)則 45.分段分日期數(shù)據(jù)庫(kù)分庫(kù)命名規(guī)則 46.表的命名 57.字段命名 58.存儲(chǔ)過程命名 59.觸發(fā)器命名, 610.索引命名 611.主鍵 612.外鍵 613.缺省值 615.函數(shù)的命名 616.其他數(shù)據(jù)庫(kù)對(duì)象命名規(guī)則 617.其他數(shù)據(jù)庫(kù)可編程性對(duì)象命名 618.數(shù)據(jù)庫(kù)保留字 619.禁止使用空格 6第二章常用數(shù)據(jù)類型 7第三章數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范 91.三范式 92.適當(dāng)?shù)娜哂?93.主鍵 94.索引 95.主鍵與聚集索引的關(guān)系 10第四章存儲(chǔ)過程編寫規(guī)范 111.注釋 112.書寫規(guī)范 123.性能相關(guān) 124.盡量使用索引 135.事務(wù)和鎖 146.其他注意事項(xiàng) 147.注意臨時(shí)表和表變量的用法 148.注意子查詢的用法 149.常用寫法 169.1.XML解析 169.2.檢查表是否有數(shù)據(jù) 169.3.檢查變量是否為空或?yàn)椤?169.4.動(dòng)態(tài)SQL 169.5.建表 179.6.建索引 179.7.建用戶 179.8.建全文索引 179.9.建鏈接服務(wù)器 189.10.SERVICEBROKER 189.11.分區(qū) 19命名規(guī)范命名標(biāo)志法使用下面的三種大寫標(biāo)識(shí)符約定。Pascal大小寫將標(biāo)識(shí)符的首字母和后面連接的每個(gè)單詞的首字母都大寫?？梢詫?duì)三字符或更多字符的標(biāo)識(shí)符使用Pascal大小寫。例如：BackColorCamel大小寫標(biāo)識(shí)符的首字母小寫，而每個(gè)后面連接的單詞的首字母都大寫。例如：backColor大寫標(biāo)識(shí)符中的所有字母都大寫。僅對(duì)于由兩個(gè)或者更少字母組成的標(biāo)識(shí)符使用該約定。例如：System.IOSystem.Web.UI可能還必須大寫標(biāo)識(shí)符以維持與現(xiàn)有非托管符號(hào)方案的兼容性，在該方案中所有大寫字母經(jīng)常用于枚舉和常數(shù)值。一般情況下，在使用它們的程序集之外這些字符應(yīng)當(dāng)是不可見的。數(shù)據(jù)庫(kù)命名數(shù)據(jù)庫(kù)名要求全部使用Pascal命名法例如： MFC MFC53 DataController數(shù)據(jù)庫(kù)月份庫(kù)、數(shù)據(jù)表日分庫(kù)命名規(guī)則<DatabaseName><Month><TableName><Day>DatabaseName按數(shù)據(jù)庫(kù)命名要求命名TableName按數(shù)據(jù)表命名規(guī)則命名Month,Day要求中間無任何連接符例如 MFCLOG200301 MFC_log_ClientCheckin20030109分段數(shù)據(jù)庫(kù)分庫(kù)命名規(guī)則<DatabaseName><Segment>DatabaseName按數(shù)據(jù)庫(kù)命名要求命名Segment是分段的編號(hào)，要求長(zhǎng)度一致并且3位或者以上例如 NIDCHyper021分段分日期數(shù)據(jù)庫(kù)分庫(kù)命名規(guī)則<DatabaseName><Segment><Day>DatabaseName按數(shù)據(jù)庫(kù)命名要求命名Segment是分段的編號(hào)，要求長(zhǎng)度一致并且3位或者以上Day要求中間無任何連接符例如GatheredLog00120110227MassLog00320110227表的命名<SystemName>_<TableType>_<Name>SystemName為表所屬的系統(tǒng)名，此處要求采用Pascal命名法TableType為數(shù)據(jù)表的類別，此處要求全部使用小寫，在我們的庫(kù)中有如下幾種數(shù)據(jù)表類型：tb數(shù)據(jù)表,stat統(tǒng)計(jì)表,dict字典表,sys系統(tǒng)信息表,re關(guān)系表,log日志表Name為數(shù)據(jù)庫(kù)表的名稱，此處要求使用Pascal命名法 例如： MFC_tb_Unit場(chǎng)所信息表 MFC_stat_UnitDailyStatus場(chǎng)所狀態(tài)日統(tǒng)計(jì)表 MFC_re_UserArea用戶地區(qū)關(guān)系表 MFC_log_Customer顧客日志表MFC_dict_Sex性別字典表字段命名字段命名統(tǒng)一使用Pascal標(biāo)志法，單詞中間不用下劃線。應(yīng)盡量使用簡(jiǎn)短而又能說明字段實(shí)際意義的詞組組合，為保證不與系統(tǒng)字段重復(fù)，應(yīng)盡量至少使用兩個(gè)單詞。同樣含義的字段應(yīng)盡量使用已有字段的物理名。例如： CertificateCode證件號(hào) CertificateType 證件類別 AlertClassName報(bào)警類別名存儲(chǔ)過程命名[<SystemName>]<FunctionModule>_<TableName>_<FunctionName> SystemName是系統(tǒng)名，此處要求使用Pascal命名法，對(duì)于跨系統(tǒng)使用的存儲(chǔ)過程要求此段，其他非跨系統(tǒng)的存儲(chǔ)過程不要求。FunctionModule為功能模塊名，此處要求使用Pascal標(biāo)志法 TableName為數(shù)據(jù)庫(kù)已有表名，命名規(guī)則同上面的表命名要求 FunctionName為存儲(chǔ)過程的功能說明，此處要求使用Pascal標(biāo)志法。 常用的功能有： GetList取多條記錄 GetModel去單條記錄 GetListByCondition根據(jù)Condition條件取單條記錄 Add插入或修改單條記錄 Delete刪除記錄 Insert插入單條記錄 BatchInsert批量插入多條記錄 BatchUpdate批量更新多條記錄 Update更新單條記錄例如： Communication_MFC_re_UnitStatus_GetList DataAnalysis_NIDC_tb_PersonGroup_Delete DataAnalysis_MFC_tb_CrimeOnEsc_Add觸發(fā)器命名,TR_<TableName>[_<Operation>]如果只是針對(duì)單個(gè)操作類型的觸發(fā)器，則要求說明操作類型：例如： TR_MFC_tb_Argot TR_MFC_tb_Argot_Insert索引命名IX_<TableName>_<ColumnName>例如：IX_MFC_log_Customer_EndTime主鍵PK_<TableName>。TableName同表命名規(guī)則 例如 PK_MFC_Log_Customer外鍵FK_<TableName1>_<TableName2>例如：FK_MFC_log_Customer_MFC_tb_Unit缺省值DF_<TableName>_<ColumnName>例如：DF_MFC_log_Customer_UserName 視圖的命名用Pascal標(biāo)志法，和表一致；<SystemName>_view_<Name>視圖的命名除中間用’view’鏈接以外與表一致例如： MFC_view_Strategy函數(shù)的命名采用存儲(chǔ)過程同樣的命名規(guī)則其他數(shù)據(jù)庫(kù)對(duì)象命名規(guī)則其他數(shù)據(jù)庫(kù)對(duì)象，比如約定、隊(duì)列、服務(wù)、路由等采用表名相同的命名法。其他數(shù)據(jù)庫(kù)可編程性對(duì)象命名其他數(shù)據(jù)庫(kù)可編程性對(duì)象采用存儲(chǔ)過程相同的命名法。數(shù)據(jù)庫(kù)保留字不要使用數(shù)據(jù)庫(kù)保留字，給數(shù)據(jù)對(duì)象命名；禁止使用空格在數(shù)據(jù)庫(kù)對(duì)象命名時(shí)，禁止使用空格。常用數(shù)據(jù)類型下面是我們?cè)贁?shù)據(jù)庫(kù)設(shè)計(jì)中常用的幾種數(shù)據(jù)類型：數(shù)據(jù)類型類型描

述int整型int數(shù)據(jù)類型可以存儲(chǔ)從-231(-2147483648)到231(2147483647)之間的整數(shù)。存儲(chǔ)到數(shù)據(jù)庫(kù)的幾乎所有數(shù)值型的數(shù)據(jù)都可以用這種數(shù)據(jù)類型。這種數(shù)據(jù)類型在數(shù)據(jù)庫(kù)里占用4個(gè)字節(jié)bigint整型從-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型數(shù)據(jù)。這種數(shù)據(jù)類型在數(shù)據(jù)庫(kù)里占用8字節(jié)空間numeric精確數(shù)值型numeric數(shù)據(jù)類型與decimal型相同（要求在存儲(chǔ)過程或其他語(yǔ)句中必須表名數(shù)據(jù)長(zhǎng)度及精度）datetime日期時(shí)間型datetime數(shù)據(jù)類型用來表示日期和時(shí)間。這種數(shù)據(jù)類型存儲(chǔ)從1753年1月1日到9999年12月31cursor特殊數(shù)據(jù)型cursor數(shù)據(jù)類型是一種特殊的數(shù)據(jù)類型，它包含一個(gè)對(duì)游標(biāo)的引用。這種數(shù)據(jù)類型用在存儲(chǔ)過程中，而且創(chuàng)建表時(shí)不能用Uniqueidentifier特殊數(shù)據(jù)型Uniqueidentifier數(shù)據(jù)類型用來存儲(chǔ)一個(gè)全局唯一標(biāo)識(shí)符，即GUID。GUID確實(shí)是全局唯一的。這個(gè)數(shù)幾乎沒有機(jī)會(huì)在另一個(gè)系統(tǒng)中被重建。可以使用NEWID函數(shù)或轉(zhuǎn)換一個(gè)字符串為唯一標(biāo)識(shí)符來初始化具有唯一標(biāo)識(shí)符的列char字符型char數(shù)據(jù)類型用來存儲(chǔ)指定長(zhǎng)度的定長(zhǎng)非統(tǒng)一編碼型的數(shù)據(jù)。當(dāng)定義一列為此類型時(shí)，你必須指定列長(zhǎng)。當(dāng)你總能知道要存儲(chǔ)的數(shù)據(jù)的長(zhǎng)度時(shí)，此數(shù)據(jù)類型很有用。例如，當(dāng)你按郵政編碼加4個(gè)字符格式來存儲(chǔ)數(shù)據(jù)時(shí)，你知道總要用到10個(gè)字符。此數(shù)據(jù)類型的列寬最大為8000個(gè)字符varchar字符型varchar數(shù)據(jù)類型，同char類型一樣，用來存儲(chǔ)非統(tǒng)一編碼型字符數(shù)據(jù)。與char型不一樣，此數(shù)據(jù)類型為變長(zhǎng)。當(dāng)定義一列為該數(shù)據(jù)類型時(shí)，你要指定該列的最大長(zhǎng)度。它與char數(shù)據(jù)類型最大的區(qū)別是，存儲(chǔ)的長(zhǎng)度不是列長(zhǎng)，而是數(shù)據(jù)的長(zhǎng)度nvarchar統(tǒng)一編碼字符型nvarchar數(shù)據(jù)類型用作變長(zhǎng)的統(tǒng)一編碼字符型數(shù)據(jù)。此數(shù)據(jù)類型能存儲(chǔ)4000種字符，使用的字節(jié)空間增加了一倍Nvarchar(max)統(tǒng)一編碼字符型最多為230–1(1?073?741?823)Unicode字符，占用2×字符數(shù)＋2字節(jié)的空間Varchar(max)字符型最多為231–1(2?147?483?647)字符，一般用來定義XML的入?yún)?，每字?字節(jié)＋2字節(jié)額外開銷varbinary(max)二進(jìn)制數(shù)據(jù)類型可變長(zhǎng)度二進(jìn)制數(shù)據(jù)。n的取值范圍為1至8,000。max指示最大存儲(chǔ)大小是2^31-1個(gè)字節(jié)。存儲(chǔ)大小為所輸入數(shù)據(jù)的實(shí)際長(zhǎng)度+2個(gè)字節(jié)。數(shù)據(jù)庫(kù)設(shè)計(jì)規(guī)范三范式數(shù)據(jù)庫(kù)設(shè)計(jì)中應(yīng)盡可能遵守三范式。所謂三范式即：沒有重復(fù)的組或多值的列，這是數(shù)據(jù)庫(kù)設(shè)計(jì)的最低要求。非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于一個(gè)組合式主關(guān)鍵字的某些組成部分。消除部分依賴，大部分情況下，數(shù)據(jù)庫(kù)設(shè)計(jì)都應(yīng)該達(dá)到第二范式。一個(gè)非關(guān)鍵字段不能依賴于另一個(gè)非關(guān)鍵字段。消除傳遞依賴，達(dá)到第三范式應(yīng)該是系統(tǒng)中大部分表的要求，除非一些特殊作用的表。適當(dāng)?shù)娜哂嗟峭耆凑找?guī)范化設(shè)計(jì)的系統(tǒng)幾乎是不可能的，除非系統(tǒng)特別的小，在規(guī)范化設(shè)計(jì)后，有計(jì)劃地加入冗余是必要的。冗余可以是冗余數(shù)據(jù)庫(kù)、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是為了編程方便而增加，也可以是為了性能的提高而增加。從性能角度來說，冗余數(shù)據(jù)庫(kù)可以分散數(shù)據(jù)庫(kù)壓力，冗余表可以分散數(shù)據(jù)量大的表的并發(fā)壓力，也可以加快特殊查詢的速度，冗余字段可以有效減少數(shù)據(jù)庫(kù)表的連接，提高效率。比如一些日志表的歷史統(tǒng)計(jì)信息，我們可以通過作業(yè)定期在數(shù)據(jù)庫(kù)負(fù)載較小的凌晨8點(diǎn)對(duì)數(shù)據(jù)日志數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，并建立冗余的統(tǒng)計(jì)表記錄下來。主鍵主鍵是必要的，SQLSERVER的主鍵同時(shí)是一個(gè)唯一索引，而且在實(shí)際應(yīng)用中，我們往往選擇最小的鍵組合作為主鍵，所以主鍵往往適合作為表的聚集索引。聚集索引對(duì)查詢的影響是比較大的，這個(gè)在下面索引的敘述。在有多個(gè)鍵的表，主鍵的選擇也比較重要，一般選擇總的長(zhǎng)度小的鍵，小的鍵的比較速度快，同時(shí)小的鍵可以使主鍵的B樹結(jié)構(gòu)的層次更少。主鍵的選擇還要注意組合主鍵的字段次序，對(duì)于組合主鍵來說，不同的字段次序的主鍵的性能差別可能會(huì)很大，一般應(yīng)該選擇重復(fù)率低、單獨(dú)或者組合查詢可能性大的字段放在前面。索引索引分為聚集索引和非聚集索引。每個(gè)數(shù)據(jù)表只能建立一個(gè)聚集索引，聚集索引決定了數(shù)據(jù)在表中的物理順序，同時(shí)非聚集索引依賴聚集索引存在。每一個(gè)非聚集索引B樹的頁(yè)節(jié)點(diǎn)都存有對(duì)應(yīng)的聚集索引鍵。因此聚集索引和非聚集索引的選擇應(yīng)該遵守如下規(guī)范：應(yīng)盡量選擇符合唯一約束的字段建立聚集索引盡量選擇占用空間較小的字段建立聚集索引，一般要求聚集索引小于900字節(jié)根據(jù)數(shù)據(jù)量決定哪些表需要增加索引，數(shù)據(jù)量小的可以只有主鍵。同時(shí)對(duì)數(shù)據(jù)量比較大的表(>1000行)應(yīng)結(jié)合數(shù)據(jù)表的使用情況建立非聚集索引以提高數(shù)據(jù)庫(kù)查詢的反應(yīng)效率。但是過多的非聚集索引也會(huì)影響數(shù)據(jù)表記錄的插入及更新速度，一般要求非聚集索引的個(gè)數(shù)不超過兩位數(shù)。因此應(yīng)該針對(duì)各數(shù)據(jù)表的實(shí)際情況設(shè)計(jì)索引。若某列的值大部分是a，少數(shù)是別的值（如b,c,d…），且經(jīng)常以該列的其它值(如b,c,d…)為查詢條件，可以考慮對(duì)(如b,c,d…)建立篩選索引。把經(jīng)常一起出現(xiàn)的字段組合在一起，組成組合索引，組合索引的字段順序與主鍵一樣，也需要把最常用的字段放在前面，把重復(fù)率低的字段放在前面，同一索引中的組成列最好不要超過3列。根據(jù)使用頻率決定哪些字段需要建立索引，選擇經(jīng)常作為連接條件、篩選條件、聚合查詢、排序的字段作為索引的候選字段。若表主要用來查詢，則可按需要建立索引，若對(duì)表操作主要是UPDATE，則盡可能少建索引。主鍵與聚集索引的關(guān)系在數(shù)據(jù)庫(kù)設(shè)計(jì)中，我們經(jīng)常容易混淆主鍵和聚集索引的關(guān)系。因?yàn)槿绻覀兘⒅麈I的時(shí)候沒有特別說明，SQLSERVER會(huì)默認(rèn)在主鍵上建立聚集索引。同時(shí)由于聚集索引同時(shí)也是唯一索引，而且主鍵一般為較小的鍵。所以我們經(jīng)常將主鍵作為聚集索引。但是這并不表示主鍵和聚集索引等同。存儲(chǔ)過程編寫規(guī)范統(tǒng)一和規(guī)范的代碼書寫風(fēng)格對(duì)保證軟件的開發(fā)質(zhì)量、提高團(tuán)隊(duì)的開發(fā)效率以及將來的維護(hù)及其擴(kuò)展都至關(guān)重要。注釋為了增強(qiáng)可讀性及美觀性，在存儲(chǔ)過程頭部和存儲(chǔ)過程中間應(yīng)盡量按照如下演示的存儲(chǔ)過程做好注釋。USEUSE[MFC_HOTEL]GO/*用途：根據(jù)用戶ID查詢轄區(qū)場(chǎng)所統(tǒng)計(jì)--項(xiàng)目名稱：--說明：這里對(duì)存儲(chǔ)過程進(jìn)行詳細(xì)說明--時(shí)間：2012-09-24--編寫者:修改記錄:--編號(hào) 修改時(shí)間 修改人 修改原因 修改標(biāo)注--0012012-10-11這里說明修改原因001測(cè)試語(yǔ)句EXECWeb_UnitMange_MFC_tb_Unit_GetTreeList@LocationStatus=2*/CREATEPROCEDURE[dbo].[Web_UnitMange_MFC_tb_Unit_GetTreeList]( @GuildIDXMLVARCHAR(MAX)=NULL, @LocationStatusINT=0--0-全部;1-已標(biāo)注;2-未標(biāo)注)AS BEGIN--存儲(chǔ)過程應(yīng)盡量保持這種縮進(jìn)風(fēng)格，增強(qiáng)美觀性和可讀性 SETNOCOUNTON--每個(gè)存儲(chǔ)過程中關(guān)閉統(tǒng)計(jì) --這里介紹每個(gè)代碼塊的功能，增強(qiáng)代碼可讀性 IFISNULL(@GuildIDXML,'')<>'' BEGIN EXECsp_xml_preparedocument@HandleOUTPUT,@GuildIDXML /*SELECTGuildIDINTO#TempGuildID修改前的代碼段注釋保留*/ INSERTINTO#TempGuildID--001這里標(biāo)注相應(yīng)修改的位置 SELECTGuildID FROMOPENXML(@Handle,N'/ROOT/ROW')WITH(GuildIDint) EXECsp_xml_removedocument@Handle END END書寫規(guī)范數(shù)據(jù)庫(kù)服務(wù)器端的觸發(fā)器和存儲(chǔ)過程是一類特殊的文本，為方便開發(fā)和維護(hù)，提高代碼的易讀性和可維護(hù)性。規(guī)范建議按照分級(jí)縮進(jìn)格式編寫該文本。編寫存儲(chǔ)過程時(shí)應(yīng)遵守以下縮進(jìn)規(guī)則，如下示例IF1<>1 BEGIN--每個(gè)IF條件后的程序塊縮進(jìn) SELECTU.[GuildID]--各字段盡量對(duì)其 ,U.[UnitCode]--每個(gè)查詢字段要寫明表別名或表名 ,U.[UnitID] ,U.[AreaCode] FROM[MFC_HOTEL].[dbo].[MFC_tb_Unit]UWITH(NOLOCK) INNERJOINMFC_HOTEL.dbo.MFC_tb_AreaAWITH(NOLOCK) ONU.AreaCode=A.AreaCode--JOIN條件縮進(jìn)增強(qiáng)層次感 WHEREA.IsActive=0 --FROM,JOIN,WHERE對(duì)齊 ENDELSE RETURN不要使用SELECT*需要哪些字段，查詢哪些字段,盡可能少的返回結(jié)果集行的數(shù)量。在多表關(guān)聯(lián)時(shí)，列名前需要加上別名（或表名），表名前加Owner（dbo）。如果涉及到跨數(shù)據(jù)庫(kù)，就需要加上數(shù)據(jù)庫(kù)名稱。例如：AdventureWorks.dbo.Contact；存儲(chǔ)過程也一樣；SQL保留字要大寫對(duì)SQL的保留字，都需要大寫。例如：SELECT，UPDATE，INSERT，WHERE，INNERJOIN，AND，OR等。過多使用GOTO語(yǔ)句會(huì)使得代碼可讀性降低查詢列表和條件中的字段全部需要指定所屬的表，可以使用表名別名簡(jiǎn)化。表名別名要簡(jiǎn)短，但意義要盡量明確，避免使用A、B、C等過于簡(jiǎn)單的別名。通常，使用大寫的表名作為別名，使用AS關(guān)鍵字指定表或字段的別名。性能相關(guān) Where子句盡量避免使用函數(shù)；避免在ORDERBY子句中使用表達(dá)式；限制在GROUPBY子句中使用表達(dá)式；慎用游標(biāo)；避免隱式類型轉(zhuǎn)換，例如字符型一定要用’’，數(shù)字型一定不要使用’’；查詢語(yǔ)句一定要有范圍的限定，避免全表掃描操作；慎用DISTINCT關(guān)鍵字；慎用OR關(guān)鍵字，可以用UNIONALL替代；除非必要，盡量用UNIONALL而非UNION使用EXISTS(SELECT1)替count(*)來判斷是否存在記錄；SETNOCOUNTON語(yǔ)句把SETNOCOUNTON語(yǔ)句放到存儲(chǔ)過程和觸發(fā)器中，作為第一句執(zhí)行語(yǔ)句。例如：CREATEPROCEDURE[dbo].[UP_GetOrgChildren]ASBEGIN SETNOCOUNTON 關(guān)閉數(shù)據(jù)庫(kù)提示輸出。盡量使用索引IN/OR子句使用IN、OR、NOTIN應(yīng)盡量避免使用，這可能會(huì)導(dǎo)致SQLSERVER不使用索引而選擇全表掃描，可以索引查找的，可以正常使用。!=或<>操作符子句使用!=或<>操作符應(yīng)盡量避免使用，可以用索引查找的，可以正常使用。不要對(duì)索引字段進(jìn)行運(yùn)算例如：SELECTIDFROMTWHERENUM/2=100應(yīng)改為:SELECTIDFROMTWHERENUM=100*2SELECTIDFROMTWHERENUM/2=NUM1如果NUM有索引應(yīng)改為:SELECTIDFROMTWHERENUM=NUM1*2如果NUM1有索引則不應(yīng)該改。不要對(duì)索引字段進(jìn)行格式轉(zhuǎn)換日期字段的例子：WHERECONVERT(VARCHAR(10),日期字段,120)='2008-08-15'應(yīng)該改為WHERE日期字段>='2008-08-15'AND日期字段<'2008-08-16'不要對(duì)索引字段使用函數(shù)日期查詢的例子：WHERELEFT(NAME,3)='ABC'或者WHERESUBSTRING(NAME,1,3)='ABC'應(yīng)改為:WHERENAMELIKE'ABC%'日期查詢的例子：WHEREDATEDIFF(DAY,日期,'2005-11-30')=0應(yīng)改為:WHERE日期>='2005-11-30'AND日期<'2005-12-1'WHEREDATEDIFF(DAY,日期,'2005-11-30')>0應(yīng)改為:WHERE日期<'2005-11-30'WHEREDATEDIFF(DAY,日期,'2005-11-30')>=0應(yīng)改為:WHERE日期<'2005-12-01'WHEREDATEDIFF(DAY,日期,'2005-11-30')<0應(yīng)改為:WHERE日期>='2005-12-01'WHEREDATEDIFF(DAY,日期,'2005-11-30')<=0應(yīng)改為:WHERE日期>='2005-11-30'不要對(duì)索引字段進(jìn)行多字段連接例如：WHEREFAME+'.'+LNAME='H.Y'應(yīng)改為:WHEREFNAME='H'ANDLNAME='Y'Like的使用對(duì)索引列避免使用like‘%xx’，應(yīng)該使用like‘xx%’。設(shè)計(jì)數(shù)據(jù)結(jié)構(gòu)時(shí)就應(yīng)該考慮這個(gè)問題，不要出現(xiàn)必須要采用like‘%xx’才能滿足業(yè)務(wù)需要的情形。事務(wù)和鎖事務(wù)是數(shù)據(jù)庫(kù)應(yīng)用中和重要的工具，它有原子性、一致性、隔離性、持久性這四個(gè)屬性，很多操作我們都需要利用事務(wù)來保證數(shù)據(jù)的正確性。在使用事務(wù)中我們需要做到盡量避免死鎖、盡量減少阻塞。具體以下方面需要特別注意：使用NOLOCK提示查詢優(yōu)化器在繁忙的系統(tǒng)中，對(duì)改善并發(fā)問題，是個(gè)不錯(cuò)的選擇；在存儲(chǔ)過程，觸發(fā)器，以及SQL簇中，盡可能按照相同的循序來訪問相關(guān)的表。這樣可以減少死鎖的機(jī)會(huì)； 事務(wù)盡可能短在事務(wù)中涉及到數(shù)據(jù)修改量，盡可能小，提高事務(wù)中每個(gè)語(yǔ)句的效率，利用索引和其他方法提高每個(gè)語(yǔ)句的效率可以有效地減少整個(gè)事務(wù)的執(zhí)行時(shí)間。事務(wù)操作過程不應(yīng)該有交互，因?yàn)榻换サ却臅r(shí)候，事務(wù)并未結(jié)束，可能鎖定了很多資源。盡可能低的設(shè)置鎖，以及隔離的級(jí)別。盡量不要指定鎖類型和索引，SQLSERVER允許我們自己指定語(yǔ)句使用的鎖類型和索引，但是一般情況下，SQLSERVER優(yōu)化器選擇的鎖類型和索引是在當(dāng)前數(shù)據(jù)量和查詢條件下是最優(yōu)的，我們指定的可能只是在目前情況下更有，但是數(shù)據(jù)量和數(shù)據(jù)分布在將來是會(huì)變化的。其他注意事項(xiàng)在相關(guān)表存在的數(shù)據(jù)庫(kù)下創(chuàng)建存儲(chǔ)過程和函數(shù)有設(shè)置默認(rèn)值限制的字段不允許設(shè)置為可以為空合理對(duì)大表進(jìn)行分區(qū) 視圖嵌套使用不能超過3層對(duì)數(shù)據(jù)量比較大的日志表，應(yīng)按日期，ID段分庫(kù)分表注意臨時(shí)表和表變量的用法在復(fù)雜系統(tǒng)中，臨時(shí)表和表變量很難避免，關(guān)于臨時(shí)表和表變量的用法，需要注意：如果語(yǔ)句很復(fù)雜，連接太多，可以考慮用臨時(shí)表和表變量分步完成。如果需要多次用到一個(gè)大表的同一部分?jǐn)?shù)據(jù)，考慮用臨時(shí)表和表變量暫存這部分?jǐn)?shù)據(jù)。如果需要綜合多個(gè)表的數(shù)據(jù)，形成一個(gè)結(jié)果，可以考慮用臨時(shí)表和表變量分步匯總這多個(gè)表的數(shù)據(jù)。其他情況下，應(yīng)該控制臨時(shí)表和表變量的使用。關(guān)于臨時(shí)表和表變量的選擇，很多說法是表變量在內(nèi)存，速度快，應(yīng)該首選表變量，但是在實(shí)際使用中發(fā)現(xiàn)，這個(gè)選擇主要考慮需要放在臨時(shí)表的數(shù)據(jù)量，在數(shù)據(jù)量較多的情況下，臨時(shí)表的速度反而更快。臨時(shí)表使用CREATETABLE+INSERTINTO的方式注意子查詢的用法子查詢是一個(gè)SELECT查詢，它嵌套在SELECT、INSERT、UPDATE、DELETE語(yǔ)句或其它子查詢中。任何允許使用表達(dá)式的地方都可以使用子查詢。子查詢可以使我們的編程靈活多樣，可以用來實(shí)現(xiàn)一些特殊的功能。但是在性能上，往往一個(gè)不合適的子查詢用法會(huì)形成一個(gè)性能瓶頸。如果子查詢的條件中使用了其外層的表的字段，這種子查詢就叫做相關(guān)子查詢。相關(guān)子查詢可以用IN、NOTIN、EXISTS、NOTEXISTS引入。關(guān)于相關(guān)子查詢，應(yīng)該注意：NOTIN、NOTEXISTS的相關(guān)子查詢可以改用LEFTJOIN代替寫法。例如：SELECTBEA.[AddressID],BEA.[AddressTypeID]FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH(NOLOCK)WHEREBusinessEntityIDNOTIN(SELECTBusinessEntityID FROM[AdventureWorks2012].[Person].[BusinessEntity]WITH(NOLOCK))可以改寫成SELECTBEA.[AddressID],BEA.[AddressTypeID]FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH(NOLOCK)LEFTJOIN[AdventureWorks2012].[Person].[BusinessEntity]BEWITH(NOLOCK) ONBEA.BusinessEntityID=BE.BusinessEntityIDWHEREBE.BusinessEntityIDISNULL如果保證子查詢沒有重復(fù)，IN、EXISTS的相關(guān)子查詢可以用INNERJOIN代替。SELECTBEA.[AddressID],BEA.[AddressTypeID]FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH(NOLOCK)WHEREBusinessEntityIDIN(SELECTBusinessEntityID FROM[AdventureWorks2012].[Person].[BusinessEntity]WITH(NOLOCK))可以改寫成：SELECTBEA.[AddressID],BEA.[AddressTypeID]FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH(NOLOCK)INNERJOIN[AdventureWorks2012].[Person].[BusinessEntity]BEWITH(NOLOCK) ONBEA.BusinessEntityID=BE.BusinessEntityID不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFTJOIN或者EXISTSSELECTBEA.[AddressID],BEA.[AddressTypeID]FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH(NOLOCK)WHERE(SELECTCOUNT(*)FROM[AdventureWorks2012].[Person].[BusinessEntity]WITH(NOLOCK))=0可以改寫成：SELECTBEA.[AddressID],BEA.[AddressTypeID]FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH(NOLOCK)LEFTJOIN[AdventureWorks2012].[Person].[BusinessEntity]BEWITH(NOLOCK) ONBEA.BusinessEntityID=BE.BusinessEntityIDWHEREBE.BusinessEntityIDISNULL常用寫法XML解析CREATETABLE#Temp( FieldNamenvarchar(50), FieldValuenvarchar(256), Memonvarchar(100))DECLARE@docHandleintEXECsp_xml_preparedocument@docHandleOUTPUT,@XmlINSERTINTO#Temp(FieldName,FieldValue,Memo)SELECTFieldName, FieldValue, Memo FROMOPENXML(@docHandle,N'/ROOT/ROW') WITH ( FieldNamenvarchar(50), FieldValuenvarchar(256), Memonvarchar(100) )EXECsp_xml_removedocument@docHandle檢查表是否有數(shù)據(jù)IFEXISTS(SELECT1FROM#Temp)檢查變量是否為空或?yàn)椤疘FISNULL(@Input,'')<>''動(dòng)態(tài)SQL不帶輸出參數(shù)值的寫法DECLARE@SQLNVARCHAR(MAX),@InputINTSET@Input=1SET@SQL=N' SELECTUnitCode FROMdbo.MFC_tb_UnitWITH(NOLOCK) WHEREUnitID='+CONVERT(NVARCHAR(8),@Input)EXEC(@SQL)帶輸出參數(shù)值的寫法DECLARE@SQLNVARCHAR(MAX),@InputINT,@OutputNVARCHAR(20)SET@Input=1SET@SQL=N' SELECT@Output=UnitCode FROMdbo.MFC_tb_UnitWITH(NOLOCK) WHEREUnitID=@Input 'EXECsp_executesql@SQL,N'@InputINT,@OutputNVARCHAR(20)OUTPUT',@Input,@OutputOUTPUTPRINT@Output建表CREATETABLE[dbo].[NB_re_RoleDepartment]( [RoleID][int]NOTNULL, [DepartmentID][int]NOTNULL,CONSTRAINT[PK_NB_RE_ROLEDEPARTMENT]PRIMARYKEYCLUSTERED([RoleID]ASC, [DepartmentID]ASC)WITH(IGNORE_DUP_KEY=OFF,DATA_COMPRESSION=PAGE)ON[PRIMARY])ON[PRIMARY]這其中DATA_COMPRESSION=PAGE頁(yè)壓縮選項(xiàng)在SQLSERVER2008或之后的版本才能使用建索引CREATENONCLUSTEREDINDEX[IX_MFC_tb_Process_UserID]ON[dbo].[MFC_tb_Process]([UserID]DESC)WITH(DATA_COMPRESSION=PAGE)ON[PRIMARY]這其中DATA_COMPRESSION=PAGE頁(yè)壓縮選項(xiàng)在SQLSERVER2008或之后的版本才能使用建用戶--創(chuàng)建數(shù)據(jù)庫(kù)用戶mfccIFNOTEXISTS(SELECT*FROMsys.server_principalsWHEREname=N'mfcc')CREATELOGIN[mfcc]WITHPASSWORD=N'852369',DEFAULT_DATABASE=[master],DEFAULT_LANGUAGE=[簡(jiǎn)體中文],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFFGOEXECsys.sp_addsrvrolemember@loginame=N'mfcc',@rolename=N'sysadmin'GO建全文索引BEGINTRY --屏蔽全文錯(cuò)誤 --建全文索引目錄 IFNOTEXISTS(SELECTnameFROMsys.fulltext_catalogsWHEREname='ChatQQ20121018') --全文目錄不存在 ANDEXISTS(SELECT1FROMsys.tablesWHEREname='NIR_log_ChatQQ20121018') --表存在 BEGIN CREATEFULLTEXTCATALOGChatQQ20121018WITHACCENT_SENSITIVITY=ONAUTHORIZATION[dbo] END; --建全文索引 IFNOTEXISTS(SELECT1FROMsys.fulltext_indexesWHEREobject_id=object_id('NIR_log_ChatQQ20121018')) --全文索引不存在 ANDEXISTS(SELECT1FROMsys.tablesWHEREname='NIR_log_ChatQQ20121018') --表存在 ANDEXISTS(SELECT1FROMsys.fulltext_catalogsWHEREname='ChatQQ20121018') --全文目錄存在 BEGIN CREATEFULLTEXTINDEXONNIR_log_ChatQQ20121018([Content])KEYINDEXPK_NIR_log_ChatQQ20121018ONChatQQ20121018 ENDENDTRYBEGINCATCHENDCATCHSQLSERVER2008及以后全文索引目錄是一個(gè)虛擬的概念，不需要制定PATH建鏈接服務(wù)器--增加鏈接服務(wù)器execsp_addlinkedserver'MFC208','','SQLOLEDB','192.168.9.208\nsmc6_5'--MFC208是鏈接服務(wù)器的數(shù)據(jù)庫(kù)邏輯名(別名)--增加鏈接服務(wù)器關(guān)聯(lián)登錄用戶execsp_addlinkedsrvlogin'MFC208','false',null,'mfcc','852369'--MFC208是鏈接服務(wù)器關(guān)聯(lián)到遠(yuǎn)程的用戶mfcc,密碼是852369SERVICEBROKERUSEMFCGO--建立消息類型CREATEMESSAGETYPE[XMLMessageType]VALIDATION=WELL_FORMED_XMLGO--建立約定CREATECONTRACT[XMLMessageContract]([XMLMessageType]SENTBYINITIATOR)GO--建立隊(duì)列CREATEQUEUE[dbo].[Queue_Argot]WITHSTATUS=ON,RETENTION=OFFON[PRIMARY]GO--建立服務(wù)CREATESERVICE[Service_Argot]ONQUEUE[dbo].[Queue_Argot]([XMLMessageContract])GO--建立表刪除新增觸發(fā)器,并發(fā)送消息到隊(duì)列CREATETRIGGER[dbo].[TR_MFC_tb_Argot]ON[dbo].[MFC_tb_Argot]FORINSERT,DELETEASBEGIN IF@@ROWCOUNT=0 RETURN SETNOCOUNTON --將要發(fā)送的數(shù)據(jù)生成xml數(shù)據(jù) DECLARE @messagexmlIFEXISTS(SELECT1FROMINSERTED)BEGINSET@message=(SELECTOperation='INSER