SQL SERVER数据库脚本开发规范.docx
《SQL SERVER数据库脚本开发规范.docx》由会员分享，可在线阅读，更多相关《SQL SERVER数据库脚本开发规范.docx（14页珍藏版）》请在冰豆网上搜索。
SQLSERVER数据库脚本开发规范
SQLSERVER数据库脚本开发规范
修订历史记录
版本
改版日期
改版说明*
作者
备注
V0.9
2010-8-17
C
创建稿
SimonCheng
V1.0
2010-8-31
A
追加
何根华
V1.0
2010-8-31
A
追加
王永庚
注：
改版说明中填写C-创建,A-追加,M-修改,D-删除,R-评审
一、
命名规范
1）命名标志法
用Pascal标志法，给数据库对象命名，Tables，Views，StoreProcedures。
例如：
Contracts；
ContactPhones
2）一组相关表的命名
如果有一组相关的表，需要加前缀。
以下划线“_”分割。
例如：
Products_USA
Products_India
Products_Mexico
3）存储过程命名
[GroupName_]。
这里的Action如：
Get，Delete，Update，Write，Archive，Erase等动词。
例如：
ARP_GetStockProcess;
GetOrgUsers
注：
GroupName见附件
4）禁止使用触发器命名,
5）索引命名
IX__。
例如：
IX_UserDetails_UserID。
6）主键
PK_。
7）外键
FK__。
例如：
FK_UserDetails_Emails。
8）引用字段命名
一个表如有引用字段，指有外键的列，该字段名格式为：
ID;
9）缺省值
DF__。
例如：
DF_UserDetails_UserName。
10）禁止使用空格
在数据库对象命名时，禁止使用空格。
11）数据库保留字
不要使用数据库保留字，给数据对象命名；
12）视图的命名用Pascal标志法，和表一致；
13）字段的命名用Pascal标志法，单词中间不用下划线
二、书写格式
1）SELECT语句
不要在SELECT语句后面用星号*，表名前需要加Owner（dbo）。
在SELECT语句后面写明需要的列名。
例如：
SELECTMainDeptId
PowerDesc
FROM[dbo].[AppDatabases]WITH（NOLOCK）
2）数据库对象引用
在多表关联时，列名前需要加上别名（或表名），表名前加Owner（dbo）。
如果涉及到跨数据库，就需要加上Database名称。
例如：
例如：
AdventureWorks.dbo.Contact；存储过程也一样；
3）不要拼SQL语句
从安全角度出发，尽可能用参数化来实现；
4）明确写出关联方式（不要用旧的书写方式）
--错误的格式:
SELECT*
FROMTable1,Table2
WHERETable1.d=Table2.c
--正确的格式:
SELECTTb1.Columns1
Tb2.Columns2
FROMTable1Tb1WITH（NOLOCK）
INNERJOINTable2Tb2WITH（NOLOCK）
ONTb1.d=Tb2.c
5）SQL语句缩进方式
SQL语句要按照缩进方式书写。
提高可读性。
例如：
Example：
WrongFormat
SELECTE.UserName,UD.PasswordEncodeFROMDBO.UserDetailsUDWITH（NOLOCK）INNERJOINEmployeeEWITH（NOLOCK）ONE.EmployID=UD.UserID
Example：
CorrectFormat
SELECTE.UserName
UD.PasswordEncode
FROMDBO.UserDetailsUDWITH（NOLOCK）
INNERJOINEmployeeEWITH（NOLOCK）
ONUD.UserID=E.EmployID
6）SQL保留字要大写
对SQL的保留字，都需要大写。
例如：
SELECT，UPDATE，INSERT，WHERE，INNERJOIN，AND，OR等。
7）不使用系统前缀
不要用系统对象使用了的前缀来命名。
例如“sp_”是数据库系统自身存储过程的前缀（请参存储过程的命名格式）；
8）注释的重要性，注释是必须的
不要忘记在存储过程，触发器，SQLBatches中添加注释。
注释不会影响性能。
9）注释部分修改的规范
至少要包含，以下三个关键信息：
/*
修改日期：
修改人:
修改内容：
*/
10）注释的格式
单行注释，用两个减号（--）。
多好注释，用（/**/）括起来；
11）过多使用GOTO语句会使得代码可读性降低
三、性能相关
1）尽量用主键
在Update和Delete的Where子句中，尽量用主键
2）尽量避免使用游标Cursors
建议使用SELECT语句，或用WhileLoop语句实现相同功能；
方法说明：
●每个表基本上都有一个自增的唯一标志字段（字符串也有一样），先取出符合条件的最大最小值，然后在这个范围内部循环，就可以达到和Cursor一样的功能。
●把查询结果放到带有自增列的表变量中去；
下面是一个简单循环控制的例子：
DECLARE@TransactionIDINT
DECLARE@TransactionTypeNCHAR
（1）
DECLARE@QuantityINT
SET@TransactionID=
（
SELECTMIN（TransactionID）
FROMTransactionHistory
）
WHILE@TransactionIDISNOTNULL
BEGIN
SET@TransactionID=
（
SELECTMIN（TransactionID）
FROMProduction.TransactionHistoryWHERETransactionID>@TransactionID
）
--Dosomethinghere
END
3）SETNOCOUNTON语句
把SETNOCOUNTON语句放到存储过程和触发器中，作为第一句执行语句。
例如：
CREATEPROCEDURE[dbo].[UP_GetOrgChildren]
AS
BEGIN
SETNOCOUNTON
......
关闭数据库提示输出。
4）模糊查询Like语句
在模糊查询Like语句中，禁止使用前置通配。
即不要在单词的第一个字母使用百分号%。
这样Index不再起作用；
5）禁止在Where子句关联字段上使用函数
这会使得Index不起作用；
6）FilteredIndex可以提高查询性能
当特定集合经常被访问，使用FilteredIndex可以提高查询性能，可以减少索引维护开销；
7）尽量不用临时表
建议使用派生表或公用表表达式（CTE）
--查找排在第二位的价格
SELECTMin（UnitPrice）
FROMProductsWith（Nolock）
WHEREProductIDIN
（
SELECTTOP2ProductID
FROMProductsWith（Nolock）
ORDERBYUnitPriceDESC
）
实现同样功能，可以改造成以下用派生表来表达。
效率是上面的两倍：
SELECTMin（UnitPrice）
FROM
（
SELECTTOP2UnitPrice
FROMProductsWith（Nolock）
ORDERBYUnitPriceDESC
）AsPro
8）WHERE子句中不用“不等于号”
9）及时检查global变量@@ERROR
在执行数据处理（INSERT/UPDATE/DELETE）后，及时检查global变量@@ERROR。
以便在出错时，能Rollback。
或者用TryCatch；
10）Declare语句
在存储过程的起始部分定义Declare语句。
这会使查询优化器，Reuse查询计划；
11）SET语句
在存储过程前面（Declare后面），执行SET语句，对变量进行初始化；
12）尽可能不使用CrossJoin
13）尽可能不使用集合返回
如果在没有必要返回集合的情况下，不要使用集合返回。
例如：
IFEXISTS（SELECTTop11
FROMdbo.UserDetailsWITH（NOLOCK）
WHEREUserID=50），这样比下面写法好：
IFEXISTS（SELECT*
FROMdbo.UserDetailsWITH（NOLOCK）
WHEREUserID=50）
14）数据用多少抓多少
通常情况下，不要一下子返回很大的结果集。
建议用TopN分页抓；
15）视图使用
把经常使用的，复杂的关联查询，以及计算，合并到一个视图中去。
16）尽可能不使用NText，Text，Image类型的字段
可以用NVarChar（Max），VarChar（Max），Varbinary（Max）。
不要在数据中保存Binary或Image文件（二进制BLOB对象）。
而是通过外部文件，数据库中保存这些外部文件的Path来实现；
17）静态SQL语句
尽量避免使用动态SQL语句。
比如说查询条件中的字段都是变化的。
动态SQL语句的执行效率比静态的要低。
动态SQL语句在每次执行的时候，会重新生成执行计划；
18）查询结果中尽可能减少Null值的出现
因为会招致Query和Update的复杂性。
可以用ISNULL或COALESCE来处理Null值；
19）OutputParam参数与DataSet
如果存储过程返回的结果集，始终只会是一条记录。
建议使用OutputParam参数的方式。
因为ADO在处理OutputParam的时候效率要高于DataSet；
20）有效的Indexes是提高数据性能的最好办法
21）SQL语句的性能需要检查
通过使用QueryAnalyzer，SHOWPLAN_TEXT，或者SHOWPLAN_ALL，来了解您的SQL语句的执行计划。
您的查询应该是执行IndexSeek，而不是IndexScan或TableScan；
四、索引相关
1）原则上每个表必须建立一个主键和一个聚集索引
注意主键和聚集索引之间的区别。
聚集索引建在业务相关的字段上。
建议建立一个业务不相关的自增字段代理主键；
2）聚集索引
建立在经常被作为查询条件，以及有表关联的字段上。
3）索引不是越多越好
索引是需要系统维护的，数据更新频繁的表尤其如此。
不需要的索引，一定要及时删除；
4）索引经常建立的字段
关联字段，查询条件，OrderBy，GroupBy
5）不建议使用Distinct
没有唯一性要求的地方，就不用Distinct。
Unionall不进行Duplicated检查的，Union是需要的；
6）覆盖索引
在2005/2008中提倡使用Included索引，把需要显示的字段加到索引中去，减少Page操作。
添加的字段不宜多;
五、死锁相关
1）使用NOLOCK提示查询优化器
在繁忙的系统中，对改善并发问题，是个不错的选择；
2）访问相关的Tables循序宜相同
在存储过程，触发器，以及SQLBatches中，尽可能按照相同的循序来访问相关的Tables。
这样可以减少Deadlock的机会；
3）TransactionScope尽可能短
4）在TransactionScope中涉及到数据修改量，尽可能小
5）尽可能低的设置锁，以及隔离的级别
六、其他约束
1）每个表都应该有主键字段
通常该主键为IDENTITY类型。
2）有DefaultValue限制的字段不允许设置为可以为空（Nullable）
3）在相关表存在的DataBase下创建存储过程和函数
4）SQL归档文件格式
以存储过程为例：
USE[Northwind]
GO
/*
执行位置：
mliappdb.rdtry
对象名称:
[dbo].[CustOrderTotal]
对象类型：
STOREPROCEDURE
入口参数：
@CustomerID用户ID
出口参数：
@TotalSales总销售额
对象说明：
根据CustomerID，计算该用户总的销售额
创建日期:
08/18/201009:
39:
13
创建人:
SimonCheng
修改日期：
修改人:
修改内容：
*/
ALTERPROCEDURE[dbo].[CustOrderTotal]
@CustomerIDNCHAR（5）,
@TotalSalesMONEYOUTPUT
AS
BEGIN
SETNOCOUNTON--关闭输出
/*
统计该客户的销售总额
其中需要扣除折扣
*/
SELECT@TotalSales=SUM（OD.UNITPRICE*（1-OD.DISCOUNT）*OD.QUANTITY）
FROMORDERSOWITH（NOLOCK）
ORDERDETAILSODWITH（NOLOCK）
WHEREO.CUSTOMERID=@CustomerID
ANDO.ORDERID=OD.ORDERID
END
5）Constraint创建级别
DefaultConstraint必须建在Column级别上。
其他类型的Constraint建立在Table级别上。
例如：
A.ColumnLevel：
CREATETABLEProductSales
（
SalesIDINTCONSTRAINTPK_ProductSales_SIDPRIMARYKEY,
ProductIDINTCONSTRAINTFK_ProductSales_PIDFOREIGNKEYREFERENCES
Products（ProductID）,
SalesPersonVARCHAR（25）
）;
B.TableLevel
CREATETABLEProductSales
（
SalesIDINT,
ProductIDINT,
SalesPersonVARCHAR（25）
CONSTRAINTpk_productSales_sidPRIMARYKEY（SalesID）,
CONSTRAINTfk_productSales_pidFOREIGNKEY（ProductID）REFERENCESProducts（ProductID）
）;
6）带INSERT的SELECT语句
当从一个表向另外一个表Insert数据时（列出字段），尽量使用带INSERT的SELECT语句。
而不是使用游标来做。
例如：
INSERTINTODBO.TableA（
Column1
Column2）
SELECTColumn1
Column2
FROMDBO.TableBWITH（NOLOCK）
WHERE......
七、附加说明
1）GroupName，前缀缩写表
GroupName
acp_
arp_
bep_
csp_
dtp_
fup_
等等...
升级会员 