（4.9）SQL Server 資料庫規範

標籤：比較   資料庫表   state   efault   enter   dde   系統日誌   列印   子查詢   

SQL Server 資料庫規範

 

一、       命名規範

常用對象命名規範，使用帕斯卡命名法（Pascal，單字首大寫），統一使用英文。

 

 

1.        表。英文單數名詞，盡量寫完整單詞名稱一般不超過3個英文單詞都可表達出表的意思。使用帕斯卡命名法。

如：User，UserRole，Role，Group，Family，SalesOrderDetail

錯誤例子：Users，UserTable

 

注意：特殊意義的表

自訂的中繼資料表，使用首碼Sys開頭：SysDictionary，SysParameter，SysModel，SysRegion

業務同類表：WeChatCompany，WeChatUser，WeChatMember，SMSSend，SMSReceive

縮寫表，普遍認知的縮寫： US_User，EN_User，CN_User，WTO_Member，WTO_ Country

單詞過多的表：User_ ZGYH（中國銀行中文縮寫），User_ GSYH（工商銀行中文縮寫）

        

2.        欄位。與表命名雷同。

對於主鍵欄位，統一為：ID

外鍵引用的欄位，統一為：外鍵表名+ID

如 UserID，UserRoleID，SalesOrderDetailID，SysDictionaryID

 

3.        視圖（不要使用）。

與表命名雷同。大寫“V_”作為首碼，格式：V_視圖名稱

         如：V_User，V_SysDictionary

                 

4.        預存程序（業務處理不要使用，報表可用）。擷取或執行資料的過程。

要求動名片語合。大寫“USP_”作為首碼，格式：USP_預存程序名稱

如：P_GetUser，P_UpdateUserByUsername，P_CleanDeletedUser，P_GetMyRecord

 

5.        純量涵式（查詢到表的不要使用）。返回單個值。

要求動名片語合，大寫“FN_”作為首碼，格式：FN_函數名稱

如： FN_GetUserName，FN_GetUserNameByUserID

        

6.        資料表值函式（查詢到表的不要使用）。返回表。

要求動名片語合，寫“TF_”作為首碼，格式：TF_函數名稱

如：TF_ SplitChar，TF_ SplitCharByComma，TF_GetUserByCity

                  

7.        觸發器（不要使用）。

大寫“TR_”作為首碼，觸發操作為尾碼，格式：TR_表/視圖名稱_Insert/Delete/Update

如：TR_User_Insert，TR_User_Update，TR_User_Delete

 

8.        索引。盡量寫完涉及的欄位

大寫“IX_”作為首碼，格式：IX_表名稱_欄位名稱_欄位名稱_……

如：IX_User_ID，IX_User_UserName_Mobile

 

唯一索引： UIX_表名稱_欄位名稱

包含列索引：IX_表名稱_欄位名稱_Include

篩選索引：IX_表名稱_欄位名稱_Where

 

9.        約束。

主鍵約束：PK_表名稱_欄位名稱

外鍵約束：FK_表名稱_欄位名_主表名稱_主表欄位（禁用外鍵約束！）

唯一約束：UIX_表名稱_欄位名稱（唯一約束預設是建立唯一索引來約束）

預設值約束：DF_表名稱_欄位名稱

         Check約束：CK_表名稱_欄位名稱

 

10.     臨時對象。大小寫不要求，其他與表名稱規範相同。聲明使用會話層級。

暫存資料表：#user （不要使用 ##user）

(表)變數：@name （不要使用@@name）

 

 

二、       設計規範

對象級規範：

?  資料庫表設計必須滿足第三範式（特殊情況再討論）

?  業務表都設定自增主鍵ID！（主鍵不一定是叢集索引）

?  禁止使用外鍵約束！（操作資料慢；維護資料困難）

? 禁止使用觸發器！（程式中控制操作）

? 禁止使用視圖！（維護不便，多表關聯可能有欄位沒用。報表可用）

? 禁止使用函數訪問表、視圖等資料（函數只作為輔助計算工具，不參與訪問資料！）

? 禁止使用預存程序處理商務邏輯（先在程式計算好再去讀寫資料庫！）

? 禁止使用遊標！（少用遍曆，用集合概念來操作）

? 禁止使用暫存資料表！（業務操作頻繁會不可控）

? 禁止使用同義字！（維護易被忽略）

? （報表可用預存程序、視圖、暫存資料表，視圖中的結果集不要有排序）

? （若必要使用，視圖、預存程序、函數、觸發器、遊標等不要多層調用2 次以上！）

? 不要使用資料庫關鍵字作為表名、欄位名等（尤其系統對象名稱，如：getdate、sum 等）

? 建立約束、索引等，手動設定名稱，不用系統自動產生！

?  索引盡量不超過5個，尤其那些頻繁更改和插入的表！

?  表和欄位在資料庫中須添加詳細注釋！（參考：sp_addextendedproperty）

?  視圖、預存程序、函數、觸發器等，須填寫建立資訊及每個操作的描述！

?  禁止在資料庫中隱藏檔。

?  插入字元資料時，須去掉左右空格！

?  插入表時所有欄位必須全部列出！（如： insert into tab(name,phone) select name, mobile from user）

?  預存程序、函數等，模組語句加上 BEGIN……END；去掉多餘的空行和空格。

?  程式動作表的許可權只允許select、insert、update、delete！

?  對於可能刪除的業務資料，資料庫不實際刪除資料，增加欄位 IsDeleted 來判斷是否刪除！

?  動作記錄、系統日誌等表，只允許插入，不能再修改！

?  對約束較強的枚舉選項，程式應設定選項而不要使用者手動隨意填寫！

? 業務表都添加一個時間欄位：addTime datetime not null constraint DF_表名稱_datetime default(getdate())

 

 

欄位規範：

?  盡量設定使用 “not null”約束，數值預設0，字元預設為空白’’。否則查詢時 null 是不參與比較的！

?  禁用 ntext、text 和 image，用nvarchar(max)、varchar(max)和 varbinary(max)替代，盡量不用max！

?  非英文重要資料使用unicode類型儲存，如nvarchar ，nchar，其他可以varchar；

?  日期時間欄位統一用 datetime，精確到毫秒；

?  金額、小數類型使用 decimal，不用int、float、double（int可儲存21億，float、double精度不準）

?  狀態欄位統一用State（可枚舉的），不用status

?  不允許明文儲存密碼！

 

 

三、       查詢規範

u  定義的參數、變數類型要參考與欄位類型和長度相同。

Declare @name varchar(50) --類型要與 User(Name)相同

Select * from User where Name = @name

 

u  少用遊標遍曆，用集合概念來處理資料。

u  操作使用 IN 內的常量不要過百，盡量少！應用其他方式改為表串連。

錯誤樣本：select * from [User] whereName in(‘AA‘,‘BB‘,‘CC‘,‘DD‘,‘EE‘,‘FF‘,………)

 

u  若須用“IN”，嵌套子查詢不要超過3個。

Select * from User

where Name in(

    select Name

    from Manager

    where Sex in(Select Sex from User where Name=‘kk‘)

)

 

u  子查詢“IN”都改為“innerjoin”方式，注意是否有一對多或多對多情況，有則先串連鍵分組再串連。

上面的 IN 查詢可改為如下：

Select distinct t1.*

from User t1

inner join Manager t2 on t1.Name=t2.Name

inner join User t3 on t2.Sex=t3.Sex

where t3.Name=‘kk‘

 

u  能用“EXISTS  ”就不用“IN”，exists 可以避免可能的錯誤。

--表 TestRole 不存在欄位 Mobile，但是查詢不會報錯！

select * from User where name in(select Mobile fromTestRole)

select * from User where name not in(select Mobile from TestRole) 

改為:

select * from User a where exists(select 1 fromTestRole b where a.name=b.RoleName)

 

--表 TestRole 欄位 RoleName 只要存在一個 Null ，查詢則沒有結果

select * from User where name not in(select RoleName from TestRole) 

改為:

select * from User where name not in(select RoleName from TestRole whereRoleName is notnull) 

select * from User a where not exists(select 1 from TestRole b wherea.name=b.name)

 

u  預存程序中首行添加：set nocount on ，主要是將執行結果顯示的列印資訊不返回用戶端，減少網路IO。

CREATE PROC dbo.TestPro

AS

BEGIN

SET NOCOUNT ON --此處添加

…………

END

GO

 

u  預存程序內不要列印（print）資訊，理由同上。

u  查詢不用星號，哪怕欄位再多也寫完整，並非所有欄位都用的上的。且星號不能建立合適的索引。

u  Where篩選中，不要在欄位上使用函數，否則整表都掃描來進行函數處理。

Select * from User where left(Name) = ‘黃‘

改為：

Select * from User where Name like ‘黃%‘

 

Select * from User where convert(varchar(10),AddTime,120)=‘2018-01-01‘

改為：

Select * from User where AddTime>=‘2018-01-01‘ andAddTime<‘2018-01-02‘

 

u  模糊比對like不用講 % 放在首位。（同上）

u  時間比較注意，不必手寫那麼詳細

Select * from User where addTime<=‘2017-12-31 23:59:59‘

改為：

Select * from User where addTime<‘2018-01-01‘

 

u  一致性不強或需要粗略統計的大表資料或報表，可以加上Nolock 允許髒讀。

Select * from User with(nolock)

Select * from User as U with(nolock)

 

 

u  Count 是不統計null 的。計數使用 count(*)，MSSQL中count(*) 預設走索引長度最小的來統計。

COUNT(*) = COUNT(1) = COUNT(‘A‘)

Select COUNT(*) from User --統計表總行數

Select COUNT(Mobile) from User --統計手機不為NULL的總行數

 

u  SQL Server 中，預設 null 加上任何字元都為null，所以注意！

SELECT ‘A‘+NULL --結果為 NULL

SELECT 100+NULL --結果為 NULL

—正常寫法，如果欄位有 NULL 值，使用 ISNULL 判斷更改。

Select Name + ISNULL(Mobile,‘‘) from User

注意：集合函數 sum、avg、max、min 是忽略 NULL 的。

 

u  除了資料需要大量匯出，任何查詢一定要使用分頁查詢。

u  一個事務中不用頻繁重複讀取表資料或操作資料

u  查詢盡量參數化，即先聲明參數，後賦值，再把參數帶入執行指令碼（如： where name = @name）

Select Name,Mobile from User where Name =‘AA‘

Select Name,Mobile from User where Name =‘BB‘

改為：

Declare @name varchar(50)

Set @name = ‘AA‘

Select Name,Mobile from User where Name [email protected]

go

Declare @name varchar(50)

Set @name = ‘BB‘

Select Name,Mobile from User where Name [email protected]

go

 

u  多表關聯情況且結果集在滿足情況下，盡量使用左串連(left join）而不使用內串連( inner join)

Select t1.Name

from User t1

inner join Manager t2 on t1.ManagerID=t2.ID

where t1.Mobile=‘13000000000‘

         改為：

Select t1.Name

from User t1

left join Manager t2 on t1.ManagerID=t2.ID

where t1.Mobile=‘13000000000‘

因為 Mobile 唯一，第二種情況只查詢 User 表。所以當條件不確定的時候、且不影響結果，使用第二種。

 

u  按添加時間排序時，效能不好可以用主鍵叢集索引ID排序，因為ID也是遞增的。

Select top(10) Name,Mobile from User order by AddTime desc

改為：

Select top(10) Name,Mobile from User order by ID desc

 

u  多表串連查詢，使用簡短別名，欄位都加上別名且別名要統一！

Select t1.Name from User t1 inner join Manager t2on t1.ManagerID=t2.ID

Select a.Name from User a inner join Manager b on a.ManagerID=b.ID

Select u.Name from User u inner join Manager m on u.ManagerID=m.ID

        

u  編寫指令碼不要寫在一行上面，注意縮排，每行長度盡量不超過120個字元。

u  指令碼中的系統關鍵字保持統一，可全部大寫或全部小寫或首字母大寫。

u  不在資料庫中使用連結的伺服器來跨伺服器查詢，程式中應從一節點讀取資料後再傳遞到另一節點操作。

 

u  多表串連中，“on”後面若有 “or”，則改為 union 取串連：

Select a.name,b.name From User a Inner Join Manager b On a.ManagerID=b.ID or a.Mobile=b.Mobile

改為：

Select a.name,b.name From User a Inner Join Manager b On a.ManagerID=b.ID

Union

Select a.name,b.name From User a Inner Join Manager b On a.Mobile=b.Mobile

 

u  不要在生產庫查詢大量資料，佔用有效資料的IO和記憶體。若須查詢則加 nolock。

Select * from User with(nolock)

Select * from User as U with(nolock)

 

u  對於複雜分頁查詢，可以先按照條件找出主表ID（在非叢集索引中找出ID），通過ID再關聯找出分頁全部資料（通過叢集索引找出所有資料）

 

 

四、       維護規範

生產庫使用完整模式，測試、開發庫可用簡單模式

動作記錄表，ID通常只做主鍵，沒什麼用。可將欄位“AddTime”作為叢集索引欄位和分區欄位。

每次修改函數、預存程序等，備份舊的副本，記錄修改資訊。

資料庫層級的變動，先備份資料庫

大量更新、刪除資料時，先備份交易記錄

刪除、更新資料時，把需要操作的資料存放區到另一個專門的資料庫中作臨機操作備份。

刪除、更新資料時，小大量操作。建議每次不超過5000行，減少了事務的粒度，也防止鎖定擴大為表鎖。

定期分批重建索引，不要一次性全部重建。

 

其他： Disk IO、CPU、Memery、NetWork IO、遷移、擴充等效能問題，找DBA！~

 

 

 

SQL Server 的最大容量規範：https://msdn.microsoft.com/zh-cn/library/ms143432(v=sql.100).aspx

（4.9）SQL Server 資料庫規範