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

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

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