數據庫設計和編碼規范

Version1.0

目錄

1簡介................................................

1.1讀者對象..................................................................

1.2目的.......................................................................

2數據庫命名規范......................................

2.1規范總體要求..............................................................

2.2數據庫對象命名規范........................................................

2.3變量命名規范..............................................................

3數據庫設計規范......................................

3.1選擇有效的設計工具.......................................................

3.2表的設計..................................................................

3.2.1遵守范式要求.....................................................

3.2.2字段設計.........................................................

3.2.3適當的合理的冗余.................................................

3.2.4注意大類型的字段設計............................................

3.3表關系和約束設計..........................................................

3.3.1主鍵設計.........................................................

3.3.2外鍵設計.........................................................

333檢查約束............................................................

3.4索引的設計................................................................

3.4.1聚集索引和非聚集索引.............................................

3.4.2索引的初始創建原則...............................................

3.4.3索引的注意事項...................................................

3.4.4索引的后期維護工作...............................................

3.5物理存儲設計..............................................................

3.5.1Id志文件另外存放.................................................

3.5.2存儲空間的設計...................................................

4T?SOL編碼規范......................................

4.1書寫基本規范..............................................................

4.2使用可搜索參數（WHERE使用原則）.........................................

4.3少用觸發器和禁用游標.....................................................

4.4聯合查詢盡可能使用UNIONALL..............................................................

4.5盡可能避免的地方..........................................................

4.6避免返回和使用多余的數據.................................................

17操作符優化................................................................

4.8數據庫事務處理原則........................................................

4.9最少次數的訪問表..........................................................

4.10避免隱含的數據類型轉換...................................................

1

4.表變星、臨時表和公用表達式的用法…

2

4.正確地判斷記錄是否存在............

4.3

4.注意自定義標量函數的影響..........

44.4

避免編寫復雜的語句..........

5TSQL

4.6應用程序層防止執行大塊的TSOL語句

4.

7對數據庫大表的處理方案............

8SPEXECUTESQL代替EXEC..............................

存儲過程的一些建議................

如何進行質量控制............

5J

規范的制定、認可和實施................

52

討論和檢查工作........................

53

--對制定的規范不斷完善..................

54

--討論和制定公共模板....................

5.4.1SELECT語句...................

5.4.2JOIN語句......................

5.4.3子查詢.........................

544INSERT語句...................

5.4.5UPDATE語句..................

5.4.6DELETE語句..................

5.4.7CASE語句.....................

5.4.8IF語句.........................

5.4.9WHILE語句...................

EXISTS語句...................

變量聲明......................

變量賦值......................

創建表及約束索引..............

存儲過程......................

帶輸出參數的存儲過程.........

視圖...........................

物化視圖......................

自定義標量函數................

自定義一值函數（多語句）.....

自定義表值函數（內聯）.......

索引整理......................

數據庫事務格式................

1.2目的

2一個合理的數據庫結構設計是保證系統性能的基礎。一個好的規

范讓新手容易進入狀態且少犯錯，保持團隊支持順暢，系統長久

使用后不至于紊亂，讓管理者易于在眾多對象中，獲取所需或理

清問題。

3同時，定義標準程序也需要團隊合作，討論出大家愿意遵循的規

范。隨著時間演進，還需要逐步校訂與修改規范，讓團隊運行更

為順暢。

4數據庫命名規范

4.1團隊開發與管理信息系統講究默契，而制定服務器、數據庫對象.變量等

命名規則是建立默契的基本。

4.2命名規則是讓所有的數據庫用戶，如數據庫管理員、程序設計人員和程序

開發人員，可以直觀地辨識對象用途。而命名規則大都約定俗成，可以依

照公司文化.團隊習慣修改并落實。

4.3規范總體要求

1.避免使用系統產品本身的慣例，讓用戶混淆自定義對象和系統對象或關鍵詞。例如，存儲過程不

要以sp_或xp_開頭，因為SQLSERVER的系統存儲過程以sp_開頭，擴展存儲過程以xp_開頭。

2.不要使用空白符號、運算符號、中文字、關鍵詞來命名對象。

3.名稱不宜過于簡略，要讓對象的用途直觀易懂，但也不宜過長，造成使用不方便。

4.不用為數據表內字段名稱加上數據類型的縮寫。

5.名稱中最好不要包括中劃線。

6.禁止使用［拼音］+［英語］的方式來命名數據庫對象或變量。

4.4數據庫對象命名規范

我們約定，數據庫命名規范

對象包括表、視圖

（查詢\存儲過程

（參數直詢入函

數、約束。對象名

字由前綴和實際名

字組成，長度不超

過30.避免中文和

保留關鍵字，做到

簡潔又有意義。前

綴就是要求每種對

象有固定的開頭字

符串，而開頭字符

串宜短且字數統

一。可以討論一下

對各種對象的命名

規范，通過后嚴格

按照要求實施。例

如：

對象

數據庫名：［項目英文名稱］+DB

數據庫

數據文件:［數據庫名稱］+_Data.mdf

日志文件:［數據庫名稱］+_Log』df

口志文件：［數據庫名稱］+_Log』df

前綴T+［表名］;單詞首寫字母為大寫，其余全部小寫。

表

示范:TCustomer

示范：TCustomer

不需要前綴，直接用英文單詞或縮寫，單詞首字母為大寫，其余為小

表字段

寫。例如：UserName,如果是兩個單詞的首寫字母縮寫，統一用大寫，

比如:UserID

不要用ID,一律用表名+ID（如果表名太長的話，采用縮寫用各單詞的

主鍵所在字段

首寫字母線合）

用P_前綴+［功能描述］（首單詞大寫，其余下寫）

存儲過程

例如:P_GetAllCorps

例如：P_GetAllCorps

用前綴V_+［視圖名稱］

視圖

例如：V_Account

例如：V_Account

前綴F_+［功能描述］（首單詞大寫，其余下寫）

自定義標量函數

例如：F_GetEWSourceName

例如：F_GctEWSourccNamc

前綴TF_+［功能描述］（首單詞大寫，其余下寫）

自定義表值函數

PK」表名］

主鍵

例如：PK_TExAccounl

例如：PK_TExAccount

用FK」主表名］一字段表表示（考慮到名字會比較長，突出主表）

外鍵

例如：FK_TOidci_OidciID

例如：FK_TOrder_OrderID

用DF」表名］」字段名］表示

默認值約束

例如：DF_TOrder_Type

例如：DP_TOrdcr_Typc

用CK」表名］」字段名］表示

檢查約束

例如：CK_TCustomer_Mail

例如：CK_TCustomcr_Mail

用UQ」表名L［字段名］表示

唯一性約束

例如：UQ_TCustomer_Code

例如：UQ_TCusiomer_Code

用DX」表名］」字段名］表示

聚集索引

例如：DX_TCachct_ID

例如：DX_TCachet_ID

用IX」表名］」字段名］表示(字段名較多時，取前面兩個即可)

其它索引

例如：IX_TCachet_CName_CorpID

例如：IX_TCachel_CName_CorpID

4.5變量命名規范

1.數據列參數

命名格式為@+［列名稱］。

2.示例：@EmployeeID@employee_id

3.非數據列參數

在參數無法跟列名稱進行關聯時，使用能夠反映該參數功能的英文單詞或單詞組合，采用Pascal樣

式命名。

5示例：@WorkType@work_type

6數據庫設計規范

6.1好的數據庫架構設計對系統運行的性能起著很大的作用，所以要在開始

時就要引起重視。為了保證數據庫設計的高效必須安排時間對設計結果進

行評審，這一環節必不可少。

6.2選擇有效的設計工具

數據庫設計工具：、、

PowerDesigner,ERStudioRoseMicrosoftVisioo

項目開始前要確定使用哪種設計工具。(另有開發插件:RedGate系列(SQLPrompt))

選擇的工具要便于討論便入生成腳本導入數據庫。

設計通過后要形成文檔，并且這個結構設計文檔要存檔，簽入VSS基線庫中。

6.3在進行數據庫設計時，應隨時進行數據字典的維護。（字段要求寫說明）

6.4表的設計

6.4.1表設計在數據庫設計中占據有十分重要的地位。表是實際存儲

數據的對象。除了要注重表結構設計，字段的設計之外還要注

意表之間關系的設計。

6.4.2遵守范式要求

通常，合理的規范化會最小化數據異常和減少數據的冗余。為了更新數據的正確與快速，在設計的初

始階段多采用三范式設計數據庫表。

第一范式強調的是列的原子性即列不能夠再分成其他幾列。

643第二范式包含兩層意思，一是表必須有一個主鍵；二是非主鍵

列必須完全依賴于主鍵，且不能只依賴于主鍵的一部分。（盡量

少使用復合主鍵）

644第三范式需要確保數據表中的所有非主鍵列直接與主鍵列相關,

而不能直接依賴于非主Z:E冽。

6.4.5字段設計

1盡量避免可為空的列。

2.雖然在個別情況下，允許空值可能是有用的，但是應盡量少用。這是因為需要對它們進行特殊處理,

從而會增加數據操作的復雜性和增加CPU額外的邏輯判斷。很多情況下可以考慮用默認值0或空

字符串(")來代替NULL值。所以字段應該有NOTNULL的限制。

3.Unicode的選擇。

nvarchar和nchar相應比varchar和char要占用更多的存儲空間。設計的原則是：如果確保存儲

的內容只是純英文和數字，用如果含有中文字符或其它多國語言，用

char/varcharonchar/nvarcharo

存儲空間補充說明

字段長度要精

確，遵守"必

須、夠用”的

原則。

精確的長度設

計既能完整的

描述數據，又

可以節省存儲

空間。積小成

大，當數據表

中的數據有很

多記錄的時候，

這種存儲空間

的優勢就能體

現得十分明

顯。存儲空間

越緊湊，分配

的頁面就越少，

在同樣大小的

內存空間中就

可以存儲更多

的頁面，這樣

操作數據的效

率就會提高。

例如能用

char(lO)的就

不要用

char(20),提

高存儲的利用

率和系統性能，

但同時也要兼

顧擴展性和可

移植性。

字段類型

bigint8字節-2A63(-9,223,372,036,854,775,808)至lj2A63-I

(9,223,372,036,854,775,807)

int4字節-2A3I(-2,147,483,648)至U2人31-1(2,147,483,647)

smallint2字節-2A15(-32,768)到2A15-1(32,767)

tinyint1字節0至lj255

dccimal(9,2)5字節decimal(9,2)前面的9為精度,后面為小數位。當精度位于

decimal。9,2)9字節1~9之間時，占5字節v當精度位于10~19之間時，占9字

節。注意,numeric在功能上等價于decimala

money8字節-922,337,203,685,477.5808到922,337,203,685,477.5807

smallmoney4字節-214,748.3648至lj214,748.3647

datetime8字節精確到3.33亳秒。例如：2014-03-0717:25:39.450

存儲范圍：1753年1月1日到9999年12月31日

存儲范圍：1753年1月1日到9999年12月31日

sinalldatetinie4字節精確到分鐘，例如:2014-03-0717:24:00

存儲范圍是：1900年1月1日到2079年6月6日

存儲范圍是：1900年1月1日到2079年6月6日

uniqueidentifier16字節uniqueidentifier數據類型可存儲16字節的二進制值，其

作用與全局唯一標識符(GUID)一樣。(CHAR(36))

bit1字節取值范圍：0或lo

char(n)N字節

varchar(n)實際存儲的每個字符占1字節

nchar(n)2xN字節

nvarchar(n)實際存儲的每個字符占2字節

在存儲空間一樣的情況下，字符串數據類型需要字符串匹配操作，這通常比整數匹配操

作的開銷要大。所以盡量選擇整數作為字段類型。

646適當的合理的冗余

降低范式標準的一個重要原因是為了在檢索數據時少連接表從而提供一個性能優勢。或是預先匯總計算

結果并存放起來，或是將相同字段內容一式多份地放在多個表中，這樣數據的冗余會增加開發人員的工

作量和業務判斷。(最好是對有冗余的字段要另外用文檔統一說明)

6.4.7完全按照規范化設計的系統幾乎是不可能的，除非系統特別的

小，在規范化設計后，有計劃地加入冗余是必要的。冗余可以是

冗余數據庫,冗余表或者冗余字段，不同粒度的冗余可以起到

不同的作用。冗余可以是為了編程方便而增加，也可以是為了

性能的提高而增加。從性能角度來說，冗余數據庫可以分散數

據庫壓力，冗余表可以分散數據量大的表的并發壓力，也可以

加快特殊查詢的速度，冗余字段可以有效減少數據庫表的連接,

提高效率。

6.4.8數據庫設計階段，對必要的冗余處理可以事先安排設計，如果

在代碼實現階段發現一些必要的冗余字段可以及早提出來考

慮。

649注意大類型的字段設計

如果設計過程中發現表中存在大類型(可存儲2G)的字段時，要慎重考慮，因為這樣的字段會造成單

一數據頁存放不了幾條記錄。而過多的頁面也會在查詢掃描時帶來性能影響。

一般的做法是將XML、IMAGE、VARCHAR(MAX).NVARCHAR(MAX)或TEXT類型的字段切割

到另外的數據表，而后與主數據表一對一連接。因為這些大型數據訪問緩慢，修改時可能造成記錄鎖定

較久.且在大多數的使用狀態下，查詢一般字段內容時可能根本用不到這些字段.這些列的存在會增加

表的頁面數，不分割出去容易會影響其它字段的修改和查詢。

6.5VARCHAR(MAX).NVARCHAR(MAX)字段如果實際長度在8000以

下，這個值將被作為常規的變長數據類型來對待，如果超過8000個字節,

SQLServer將該值作為TEXT來存儲處理。如果該表數據量比較大時,

一定要考慮大字段分離設計原則。

6.6少用TEXT和IMAGE,二進制字段的讀寫是比較慢的。

6.7表關系和約束設計

6.7.1正確處理表間關系。一對多、一對一、多對多等關系。主外鍵

關系是保證數據完整性的一個重要機制。維護數據的正確性。

盡量采用提供的約束，如主外鍵、檢查、默認值、不可NULL

等。盡可能不要通過程序或存儲過程、觸發器等機制來運行，畢

竟SQLSERVER約束是在內部以優化過的二進制程序代碼來

實現的，而其它方式效率當然不如直接設置的約束高。還有，能

夠確定具有唯一值的字段上盡量加上唯一性約束。

6.7.2一些約束在客戶端判斷的確是可以減少服務器的資源，但是不

能完全保證數據的錯誤產生。而且用數據庫使用域和參照完整

性有時候還能幫助優化器減少查詢執行時間。域和參照完整性

幫助優化器分析有效的數據值而不需要物理訪問數據，這減少

了查詢時間。

6.7.3主鍵設計

?所有的表必須設置主鍵。主腱跟聚焦索引沒有什么關系，但主鍵必須要有索引。主鍵的選擇原則：

2.字段值唯一。

3.不可NULLo

4.字段大小盡量最小。

5.字段值不常變更。

6不建議用復合主鍵。

主健值過大會影響外表的大小。如果主鍵是聚集索引，由于所有非聚集索引都會存儲聚集索

引的鍵值，所以主鍵值過大，還將導致其他索引結構的效率不佳（頁面數1

主鍵關乎著數據的F確性與完整性.而聚焦索引是從數據的運行效率出發.雖然主鍵跟聚集索引是

兩回事，但基于主鍵的上述特性，所以主鍵往往適合作為表的聚集索引，這也是微軟的默認做法。但一些

沒有意義的ID做聚集索引的意義不大，這時候需要在創建表的時候給主鍵指定為唯一的非聚集索引。

~主鍵約束（非聚集索引）：

ALTERTABLE[dbo].[TCustomer]ADDCONSTRAINTPK_TCustojnerPRIMARYKEYNONCLUSTERED（ID）;

選擇GUID做為主鍵時在系統對接、移值和代碼編寫下都提供了很大的方便，但它是建立在犧牲性

能的基礎上。

1.在實際運用中，如果對于用36字符的GUID當作主鍵時，應當注意的問題如下：

2.GUID是無序的，所以不適合用來做聚集索引。否則會引起頻繁的頁面移動而產生大量的碎片。

GUID類型的存儲可以由char(36)改為uniqueidentifier類壁16

個字節)，以節省存儲空間。

對于有關聯的表之間，考慮程序方便可用使用GUID做為主鍵，但

對于獨立的表，還是以INT類型的字段做為主鍵來設計。所以

設計階段要分清哪些必須用GUID來做主鍵。

332外鍵設計

外鍵的存在會在處理數據時帶來麻煩，但實際上這點恰恰是它的好

處。外鍵的存在就最高效的一致性維護方法。所以在表設計時

要考慮主外鍵的設計。如果決定使用外鍵約束，那么所有人必

須遵守嚴格執行。外鍵是最高效的一致性維護方法，數據庫的

一致性要求，依次可以用外鍵、CHECK約束、規則約束、觸發

器、客戶端程序，一般認為，離數據越近的方法效率越高。

3.3.3檢查約束

約束除了主外鍵約束、唯一性約束和默認值約束外，還有一類叫檢查約束。

檢查約束是一個識別SQLServer表中每行可接受的列值的規則，檢查約束幫助實施域的完整性，域完整

性定義了數據庫表中列的有效值，檢查約束可以驗證單列的域完整性，也可以驗證多列的域完整性，在

單個列上可以有多個檢查約束，如果插入或更新的數據違反了險查約束，數據庫引擎將暫時停止

INSERT和UPDATE操作。

CREATETABLEdbo.TEmployee(

IDINT,

CodeVARCHAR(20),

SexCHAR(l)CONSTRAINTText_Sex_CKCHECK(Sex='F'ORSex='M'),

Sex列創建相應的約束，其值只能是‘F'或'M'值。

ExperienceINTCONSTRAINTText_Experience_CKCHECK(Experience>=0)

--Experience列創建相應的約束，其值必須＞-0

):

6.8索引的設計

681索引是一把雙刃劍，它通常可以加快數據檢索數據的同時，往

往又會帶來額外的資源開銷（在insert,update和delete使

用時1有時候這個開銷代價甚至超過了查詢優化帶來的好處。

所以，索引的創建是門藝術，要在工作中不斷的積累經驗和不

斷的總結。一般來說，建立索引要看數據使用的方式，也就是說

那些訪問數據的SQL語句經常使用，針對這些經常使用的SQL

語句創建有效的索引還是值得的，但過多的索引又是對于

OLTP（在線事務）數據庫是不利的。

6.8.2聚集索引和非聚集索引

每個表只能有一個聚集索引，因為目錄只能按照一種方法進行排序。

聚集索引和數據是混為一體的，而非聚集索引是與數據獨立分開的。

其實，我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查"安"字，就會很自然地翻開字典

的前幾頁，因為"安"的拼音是"an",而按照拼音排序漢字的字典是以英文字母"a"開頭并以"z"

結尾的，那么"安"字就自然地排在字典的前部。同樣的，如果查"張"字，那您也會將您的字典翻到最

后部分，因為"張"的拼音是"zhang"。也就是說，字典的正文部分本身就是一個目錄，您不需要再去

查其他目錄來找到您需要找的內容。

我們把這種正文內容本身就是一種按照一定規則排列的目錄稱為“聚集索引”。

如果您認識某個字，您可以快速地從自典中蟄到這個字。但您也可能會遇到您不認識的字，不

知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據"偏旁部首"查到您要

找的字（非聚集索引查找），然后根據這個字后的頁碼直接翻到某頁來找到您要找的字（書簽查找X但

您結合"部首目錄"和"檢字表"而查到的字的排序并不是真正的正文的排序方法，比如您查"張"字,

我們可以看到在查部首之后的檢字表中"張"的頁碼是672頁，檢字表中"張"的上面是"馳"字，但頁

碼卻是63頁，"張"的下面是"弩"字，頁面是390頁。很顯然，這些字并不是真正的分別位于"張"字

的上下方，現在您看到的連續的"馳、張、顰"二字實際上就是他們在非聚集索引中的排序，是字曲正

文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先

找到目錄中的結果，然后再翻到您所需要的頁碼。

通過以上例子，我們可以理解到什么是“聚集索引"和"非聚集索引"。

-聚集索引查找，沒有書簽查找開銷

SELECT*FROM[dbo].[TOrder]

WHEREOrderlD=1

ORDERBYOrderlD;

-非聚集索引查找

SELECTUserID,OrderlDFROM[dbo].[TOrder]

WHEREUserID=1

ORDERBYUserID;

~非聚集索引查找+書簽查找

SELECTUserID,OrderlD,OrderPriceFROM[dbo].[TOrder]

WHEREUserID1

ORDERBYUserID;

6.8.3索引的初始創建原則

如果處在數據庫項目的開始，而且不確定如何對索引建模，可以使用不加思考或默認索引模式作為開

始。一旦能夠根據實際事務信息重新評估數據庫后，再調整索引。所以在系統的初始上線階段一般只考

慮創建最少的、最必要的索引。

1.所有表要有聚集索引，如果沒有合適的字段，那么暫時在主鍵上創建聚集索引。

2.所有外鍵上創建索引。

3.可預知的用來頻繁查找的字段上創建索引。

6.8.44.小表可以不需要特意去創建索引。有主鍵就好。

6.8.5索引的注意事項

1.一個經常插入更新的表不要加太多索引，因為索引影響插入和更新的速度。

2.所有非聚集索引包含聚集索引鍵值，創建非聚集索引時不要再包含進來。

3.如果知道索引鍵的所有值都是唯一的，那么確保把索引定義成唯一索引。唯一索引除了可以保證數

據的正確性外還可能幫助優化器生成更高效的執行計劃。因為在唯一索引中每行都是唯一的，一旦

找到一行,SQLServer不必進一步查找其他匹配的行。

4.索引不只是帶來查詢優化，對于更新操作，索引有時候優化查詢帶來的好處會超過索引維護的開銷。

所以索引有某些情況下會縮短整個數據更新的時間。因為有時候，表掃描帶來的開銷會遠大于更新

操作本身的開銷。（先查找后更新）

5.盡可能地選擇那些小數據類型的列來創建索引，大的索引睚值增加了索引頁面的數量，從而增加了

索引所需要的內存和磁盤活動數量。

6.經常有范圍杳詢（between,>,<,>=,<=）或用來作條件i反回很多列和orderby、groupby發

生的列，可考慮建立聚集索引；（分區字段是時間類型的話，適合聚集索引）

7.非聚集索引在需要從一個大表上讀取少量的行時最有用。當匹配返回的記錄數過多時，需要用到的

書簽查找（鍵查找）的開銷將會變得很大。所以像性別這樣的字段不要創建非聚集索引。低選擇性的

列只能配合其它字段創建復合非聚集索引。

8.多個字段創建組合索引時要盡量使關鍵查詢形成索引覆蓋其第一個列一定是使用最頻繁的列；但

包含的列不能太多，不能有大類型的字段。

9.缺乏合適的索引也是造成阻塞、死鎖的原因。

10.頻繁更新的列不適合創建聚集索引。

686主鍵就是聚集索引，極端錯誤的，是對聚集索引的一種浪費。雖

然SQLSERVER默認是在主鍵上建立聚集索引的。顯而易見,

聚集索引的優勢是很明顯的，而每個表中只能有一個聚集索引

的規則，這使得聚集索引變得更加珍貴。使用聚集索引的最大

好處就是能夠根據查詢要求，迅速縮小查詢范圍，避免全表掃

描。（尤其是分區表，適合時間做聚集索引）

687索引的后期維護工作

1.索引創建后不就是完事了的，一定要定期觀察索引在實際工作環境中的使用情況。及時阻止索

引對系統帶來的負面影響。總的來說應該考慮如下幾點：

2.去掉使用率低的索引。

3.合理的改善索引，使索引更有效的被利用到。

4.創建缺失的必要的索引。

考慮索引碎片的問題.索引碎片率過大時，杳詢彳異不到優化.

由于表上有過度地插入、修改和刪除操作，索引頁被分成多塊就形成了索引碎片，如果索引碎片嚴

重，那么掃描索引的時間就會變長，甚至導致索引不可用，因此數據檢索操作就慢下來了。如果碎片

小于10%~20%,碎片不太可能會成為問題，如果索引碎片在20%~40%,碎片可能成為訶題，但是

可以通過索引重組來消除索引解決，大規模的碎片（當碎片大于40%）,可能要求索引重建。

一查看某個表的碎片情況（整理數據的碎片，是整理聚集索引的碎片）

一結果看LogicalFramentation字段

DBCCSHOWCONTIG（'[dbo].[TLog]'）WITHFAST,TABLERESULTS,ALL_IXDEXES,N0_IXF0MSGS;

6.9總之，索引的后期跟蹤是不斷持續的過程。為了搭建高性能的系統環境,

就必須定期有效的跟蹤索引。

6.10物理存儲設計

6.10.1除了重視邏輯對象的設計，還需要考慮數據庫的物理設計。在

并發要求很高、并發用戶數很多的情況下，這一設計對數據庫

的性能起到十分關鍵的作用。

6.10.2數據庫物理文件一般不要存放在C盤，因為系統重裝對C盤破

壞最大。

6.10.3日志文件另外存放

6.10.4查詢數據庫的頁，可以看到，由于頁的ID不連續，所以數據文

件內部的讀寫是隨機的。而日志文件的讀寫是順序的，所以兩

者放在同一個硬盤上，會造成硬盤驅動器一會隨機，一會順序,

效率會比較低。將數據文件和日志分離存儲在不同的物理硬盤

±o這樣的好處是確保數據的安全，避免單點失效。二是確保

數據庫的性能。同樣備份文件也在不同的磁盤上。

6?10.5存儲空間的設計

正確評估和測算數據庫的物理空間需求。因為數據庫采用預先分配存儲空間的方法。存儲空間的分

配操作是一個非常消耗資源的操作。所以設計人員需要評估數據空間的可能增長率，將數據庫的空間增

長方式設置為恰當好處，這樣就可以在空間和效率之間取得均衡。

1.設計要考慮的內容有：

2.數據庫文件和日志文件初始值的設計。

3.數據庫文件和日志文件以多大的比例增長。（不要用默認的1M或10%）要設置成按固定大小

增長，這樣就能避免一次增長太多或者太少所帶來的不必要的影響。建議對比較小白徵據庫，設

置一次增長50MB到100MB。對大的數據庫，設置一次增長200MB到800MB。

4.對于生產數據庫，推薦的設置是開啟數據庫自動增長和不限制大小，以防數據庫空間用盡導致

應用程序失敗。

5.在系統一段時間穩定后，可以采取日志備份的機制使得數據庫日志文件大小固定下來，不再持

續增長。事務日志備份可以截斷日志，在檢直點發生時會清空日志，這樣會在已有的空間內重新

記錄日志，而不用分配新的空間。

6.分配空間和壓縮空間都很帶來很大的資源開銷，所以盡量避免數據庫進行這兩個操作。比如對

日志文件截斷后不要使用收縮空間的操作，如果一定要收縮那么收縮到一個合適的值，這樣避

免日志文件重新分配空間。（不要收縮到最小空間，比如1M）

7.不要開啟數據庫的自動關閉和自動收縮選項。

7T?SQL編碼規范

7.1在設計確定的情況下，編碼的質量幾乎決定了整個系統的質量。編碼階段

首先是需要所有程序員有性能意識，也就是在實現功能同時有考慮性能

的思想。

7.2編寫規范的SQL語句不但利于閱讀，而且被數據庫重復使用的幾率也較

大，執行效率相對較高，編寫的好的SQL與編寫的差的SQL在執行性能

上可能會差幾倍甚至幾千幾萬倍，因此養成好的SQL編寫規范對于提高

項目質量及提高開發人員自身素質有著潛在的極大的影響。

7.3書寫基本規范

1.大小寫規則。

2.為了最大限度實現SQL的共享，要求書寫SQL語句時大小寫要一致，（比如保留關犍字、謂詞

和系統函數律大寫）這樣做的好處有兩點：一是為了閱讀方便。二是不統的語句由于寫法

不完全相同，數據庫會理解為4條不同的語句從而導致重復編譯，降低了性能。系統對象（系統

存儲過程、視圖、表，系統字段），按照系統定義的大小寫執行（數據庫里怎么定義就怎么引用）；

數據庫對象名稱，按照實際定義的大小寫執行（數據庫里怎么定義就怎么引用）

3.養成注釋的好習慣。

4.存儲過程、函數、視圖、觸發器等對象不僅要求創建時加上必要的注釋，而且在以后修改的過

程中也應該有注釋。螃最好以英文為主，盡可能做到簡潔而描述清晰。另外表也可以加上注

釋說明。

5.有結束符。

6.每一個完整的T-SQL語句都要以分號結束。據說在以后的數據庫版本里面會強制要求。現在的

版本，在公用表達式的編寫中，就有這個限制。

7.所有的賦值語句要求變量與運算符之間要有空格。如：v_count=v.count+1;,并保持適當

的對齊。

8.引用對象時帶上架構名。這也是比較推薦的寫法。默認的架構名為db。。在創建物化視圖是就

深有體會.

9.以內縮來標示IF、WHILE.BEGINEND.TRYCACHE等程序代碼區域。

10.在SQL代碼快中盡量使用BEGIN...END語句塊，提高代碼可閱讀性。

7.4在多表連接時，盡量用表別名十字段的格式來返回列。

7.5換行規則。BEGIN/END獨占一行；FROM子句獨占一行；WHERE子

句獨占一行；GROUPBY子句獨占一行；ORDERBY子句獨占一行；

單獨的LEFTJOIN和INNERJOIN獨占一行如果一行寫不完換行時,

需要確保每行邏輯完整性。

7.6使用可搜索參數（where使用原則）

建立索引后，并不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的

差別。只要我們在查詢語句中沒有強制指定索引，索引的選擇和使用方法是SQLSERVER的優化器自動

作的選擇，而它選擇的根據是查詢語句的條件以及相關表的統計信息，這就要求我們在寫SQL語句的時

候盡量使得優化器可以使用索引。

1.為了使得優化器能高效使用索引，寫語句的時候應該注意下面四點：

2.不要對索引字段進行運算，而要想辦法做變換。

3.不要對索引字段進行格式轉換。

4.不要對索引字段使用函數。

5.不要對索引字段進行多字段連接。

索引掃描或索引查找

所以在where條件語句中有時候一些不規范

的寫法會造成索引失效。右邊的寫法要優于左

邊，因為右邊可能會使得索引，而左列的寫法

是用不上索弓1的。（同樣適用于。N條件中）

索引掃描

WHEREYEAR（OrderDate）=2001WHEREOrderDate>=‘2001-07-01,

MONTH（OrderDate）=7（對條件字段使用函數ANDOrderDate<'2001-08-01,

或用戶自定義函數）

WHEREWHEREOrderDate

DATEDIFF(day,OrderDate,GETDATE())>DATEADD(DAY,-5,GETDATE())

5

WHERELEFT(AccountName,3)='fan1WHEREAccountNameLIKE1fan%1

WHEREid+9>WHEREid>@id-9

WHERECodeLIKE'%Core%1WHERECodeLIKE'Core%1

NOTIN(SELECTIDFROM)LEFTJOINbWHEREb.IDISNULL

或

NOTEXISTS(SELECT*FROMb)

WHEREIDIN(2,3,4,5,6)WHEREID>=2ANDID<=6

WHEREFirstName+'*+LastName='fanWHEREFirstName=1fan*AND

you';LastNair.e=*you*

WHERE[Status]<>10WHERE[StAbns]<1fiAND

[Status]>10

NOTIN、LEFTJOIN.NOTEXISTS和IN、INNERJOIN、EXISTS的效率問題要具體情況具體分

析。一般情況下推薦使用相關子查詢（EXISTS）和連接的方式。

7.7SQLServer從左到右處理表，這個在技術內幕上有。而where語句中

最能快速篩選數據的列應該放在最前面，也就是最接近where子句的地

方。但在SQLSERVER2005后來的版本中，優化器會幫你自動優化的。

7.8少用觸發器和禁用游標

觸發器觸發器在時間久遠后不易維護,觸發器不會立即讓開發人員看到。而且觸發

器內容太過復雜，還會存在潛在的性能瓶頸。所以我們統一規定不允許使用觸發

器，應使用其它辦法來代替觸發器。對之前已經存在的觸發器也應該抽時間去替

換它。項目負責人還需要檢查是否產生了新的觸發器并及時糾正。

游標SQLServer適合在數據集（多行）上進行操作游標是把結果集放在服務器

內存，并通過循環一條一條處理記錄，對數據庫資源（特別是內存和鎖資源）的

消耗是非常大的。

7.9聯合查詢盡可能使用UNIONALL

7.10UNION操作依次執行所有的SELECT語句，將所有的結果集合并為一個

結果集。將對結果集進行排序，并過濾掉重復的記錄。可見聯合查詢的效

率很低的，除非在必要的情況下才使用。如果允許結果集存在重復，或預

知結果集根本不可能重復時一定要用UNIONALL來代替。

7?11盡可能避免的地方

如果SELECT語句中查詢的字段很多，則使用該關鍵字段反而會大

下面這些操作在使

大降低查詢效率。因為查詢字段很多時，如果使用篩選重復關鍵字，數

據庫引擎需要花費大量的時間對所有字段進行比較，過濾掉重復的記

用前，可以重新思

錄，因此影響了查詢效率。

考下業務和檢查一

下邏輯，看是否可

以避免。

DISTINCT關鍵字

OrderByOrderBy等語句盡量是杳詢結果需要才使用，因為太忙都需要做額

外的計算。不管你使用TOP返回少量記錄，但OrderBy都會事先排完

序的。

嵌套視圖嵌套視圖是當一個視圖調用另一個視圖時，另一個視圖調用更多的

視圖.這可能導致非常容易混淆的代碼，因為視圖掩蓋了被執行的操

作。雖然查詢可能非常簡單，執行計劃和隨后SQL引擎的操作可能非

常復雜并且代價很高。同樣，嵌套的用戶自定義函數也是如此。

嵌套自定義函數注意表值函數分內聯表值函數和多語句表值函數。多語句表值函數

是用表變量返回的，對此用執行計劃查看時，優化器不能夠很好地估

計執行用戶定義函數的成本(不適合返回大量數據行)。所以必須引起

注意，有時候引用的自定義函數可以隱藏性能問題，所以應該分析內

部的語句。

7.12避免返回和使用多余的數據

1.返回數據到客戶端至少需要數據庫提取數據、網絡傳輸數據、客戶端接收數據以及客戶端處理

數據等環節，如果返回不需要的數據，就會增加服務器、網絡和客戶端的壓力，其害處是顯而

易見的，避免這類事件需要注意：

比如可能只要用到TProduct表就可以了,但卻用VProduct

橫向來看，不要寫

視圖，而VProduct視圖可能存在更多的表連接，甚至帶來了不

必要的性能問題。

SELECT*的語句，而是選

擇你需要的字段。

縱向來看，合理寫

WHERE子句，不要寫沒有

WHERE的SQL語句。

通用視圖帶來的干擾

返回多余的列返回多余的列可能會造成書簽查找操作，本來有時候非聚

集索引能解決的，還額外的使用了書簽查找。更不要允許出現

SELECT*這樣的語句。

但在相關子查詢中使用EXISTS(SELECT*)可以放心使用

因為EXISTS只關心行是否存在，不關心特定屬性，優化器

將會忽略子查詢中的SELECT列表。

但在相關子查詢中使用EXISTS(SELECT*)可以放心使用

因為EXISTS只關心行是否存在，不關心特定屬性，優化器

將會忽略子查詢中的SELECT列表。

SETNOCOUNTON存儲過程內部用SETNOCOUNTON;否則每次執行完就

會向客戶端發送消息，禁用它們能夠減少網絡流量。

7.13操作符優化

1.IN和EXISTS性能有外表和內表區分的，但是在大數據量的表中推薦用EXISTS代替IN。

2.NOTIN不走索引的是絕對不能用的，可以用NOTEXISTS代替。

3.ISNULL或ISNOTNULL操作

4.索引是不索引空值的，所以這樣的操作不能使用索弓I,可以用其他的辦法處理，例如：數字類型,

判斷大于0,字符串類矍設置一個默認值，判斷是否等于默認值即可，例如>〃

7.14<>操作符（不等于）不等于操作符是永遠不會用到索引的，因此對它

的處理只會產生全表掃描。用其它相同功能的操作運算代替，如av>0

改為a>OORa<0同樣av>''改為a>"ORa<"

7.15高版本的數據庫引擎可能會對上面的問題進行自動優化，但養成好的習

慣也是必要的。

7.16數據庫事務處理原則

1.事務過程中可能包含查詢語句和修改語句，對于查詢語句所申請的共享鎖會在查詢結束時釋放,

而對于修改語句申請的獨占鎖會持續到事務結束。所以你需要認真規劃事務：

2.保持事務范圍盡可能地小。不必要的語句盡量要拿到事務之外。

3.盡早提交事務。所以事務涉及到的SQL語句不能過于復雜。

4.避免事務不能結束。

使用SETXACT_ABORTON確保事務出現錯誤時中止或回滾。

5.消除讀寫阻塞。比如使用臟讀（表名后加WITH（NOLOCK））e（減少鎖開銷）如果不是重要的、

特別敏感的數據，允許駐讀可以避免一定程度的阻塞，加快查詢速度。

6.事務操作過程盡量要按同一順序訪問表對象。

7.17提高事務中每個語句的效率，利用索引和其他方法提高每個語句的效率

可以有效地減少整個事務的執行時間。

7.18盡量不要指定鎖類型和索引，SQLSERVER允許我們自己指定語句使用

的鎖類型和索引，但是一般情況下,SQLSERVER優化器選擇的鎖類型和

索引是在當前數據量和查詢條件下是最優的，我們指定的可能只是在目

前情況下更有，但是數據量和數據分布在將來是會變化的。

7.19最少次數的訪問表

盡量少做重復的工作，最少次數的訪問表。

如果可以只訪問一次表就沒有必要再次訪問表。有的是存儲過程內部語句太多，寫在后面沒有

留意的問題。所以需要審查自己寫的存儲過程。

UPDATEdbo.TEMPLOYEESETFNAME=,HAIWER*WHEREEMP_ID='VPA30890F';

UPDATEdbo.TEMPLOYEESETLNAME=*YANG*WHEREEMP_ID='VPA30890F';

1.使用公用表達式CTE,可以多次引用，從而減少基礎表的訪問次數。

2.使用0?ROWCOUNT判斷影響的表行數從而避免表多次返問。

7.20避免隱含的數據類型轉換

—Code本來是NVARCHAR(36)類型

DECLARE@codeVARCHAR(36);

SET?code='0D32C649-A584-4629-8EE3-DDF26A61F9C1,;

SELECT*FROMdbo.TTablelWHEREID=?code;

在執行計劃中看到，導致需要額外的操作對值進行轉換之后才能用于比較。

在Unicode字符前面使用N前綴避免引起數據的不一致。