数据库开发规范(主要针对ms sql,其它也类推)

--转载时请保留下面,以供大家加我MSN,增强交流,共同学习.

--姜庭华  msn: [e-mail]

--博客:http://blog.csdn.net/jaimejth

一．基本格式规范

1．全部使用小写,包括关键词,不要出现任何大写字母

2．程序块采用编程语言缩进风格书写，保证代码清晰易读，风格一致。缩进格数统一为4格。

3．必须使用空格键，不允许使用TAB键。

4．注释尽量保证左对齐,每行代码不要太多，要注意换行。

5．同一条SQL语句太长或占用多于一行时，把SQL的关键字做为下一行的行首，并且每行的第一个关键字应当右对齐。

6．不允许把多个SQL语句写在同一行中，保证一行只写一条语句。Select/From/Where/Order by/Group by等子句必须另其一行写。Select子句内容假如只有一项，与Select同行写。

7．相对独立的程序块之间、变量说明之后必须加空行分开。

8．双目运算符前后、操作符之后、间隔符、左括号之后以及右括号之前应以空格分隔。

9．在代码中create创建表或declare声明表变量，里面的栏位每栏占一行，并且右对齐，代码清晰易读和便于注释。

10．对两个以上变量赋值时，分多行写，每一行对一个变量赋值，代码清晰易读和便于注释。

11．定义两个以上变量时，分多行写，定义每一行对一个变量，代码清晰易读和便于注释。

12．if语句中用begin...end结构，阅读起来方便些。

13．避免在一行内写太长的SQL语句，在SQL关键字的地方将SQL语句分成多行会更加清晰。
  如：select user_id,user_name,user_pwd from sys_user_Login where user_id=20
  修改成：
select user_id,

user_name,

user_pwd
from sys_user_login
where user_id=20

14. Where子句的条件假如有多项，每一个条件占一行，以AND开头，且无缩进.

15. (Update)Set子句内容每一项单独占一行，无缩进.

16. Insert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进.

 

 

二．命名规范

1．所有数据库对象名和声明变量名的长度不得超过30个字符,太长不易读(为了将数据库能兼容)

2．所有用户自定义的数据库对象名称统一使用形如“小写首字母+下划线+混合拼写”的格式，其中小写首字母表示对象类型。各个单词之间用下划线分割。

3．将各功能按模块划分管理，将每个模块以三个或四个字前缀划分表，将每个模块分为基础表(b)和事务表(t)，易于区分和阅读。 例如．hrs_b_user

 

具体规则如下：

用户表         模块前缀_(基础表(b)和事务表(t))_ 

存储过程       usp_

函数           uf_

触发器         utr(i插入u更新d删除)_

视图           uv_

字段名         数值型加qty_ 其它无前缀

说明．

A．各功能模块必须有一个前缀，用户表名必须加功能模块的前缀。

B．Sql Server系统对象很多以sys开头，因此编码时禁止以(sys+对象名)形式为用户创建的对象命名。

C．Sql Server系统存储过程以Sp_开头或xp_开头，建议用户自定义的存储过程不要与此相同, 加u代表user用户自定义的。

D．数值型加qty_表示这个字段所对应的数据类型。如订单数量是一个典型的数字类型字段,对象中引用这个字段的时候，就不用费心的去想、去找资料，确定这个字段的数据类型了。

 

4．在命名函数或存储过程时，用以下具有相反意义的词。

  示例．  例如：usp_rpt_get_snr_count

begin    -  end       开始   - 结束

start     -  finish     启动   - 完成

first     -  last       第一个 - 最后一个

prior    -  next       前一个 - 后一个

add     -  remove     增加  -  删除

create   -  destroy    创建  -  销毁

get     -  set        获取  -  设置

increment  -  decrement  增量  -  减量

lock      -  unLock     加锁  -  解锁

open     -  close      打开  -  关闭

5．标识符的命名要清晰、明了，有明确含义，使用完整的英文单词或普遍认同的缩写单词。不允许使用除小写字母、数字、_、@以外的其他字符。不允许有个人风格。 不允许使用汉语拼音。

6．命名中若使用特殊约定或缩写，则要有注释说明。

7．对于变量命名，禁止取单个字符（如i、j、k...），建议除了要有具体含义外，还能表明其变量类型、数据类型等。形如i、j、k的变量只允许作为局部循环变量。

8．所有的标示符、对象名命名不得使用系统保留字、关键字。

9．表名以代表表内的内容的一个和多个名词组成，以下划线分隔,不使用tb或tab或tbl作为表前缀（本来就是一个表，为什么还要说明）

10．一些判断性的字段，以一些助动词开头，更加直接生动：如:用户是否通过检查ischecked等。
11．各主关键字在其它表中的名字和数据类型必须一致。例如：dept_id部门ID在一个系统中统一用一个名字。
12. 使用表的别名(alias)，当在sql语句中连接多个表时，请使用表的别名并把别名前缀于每个column上，这样可以减少解析的时间并减少那些由column歧义引起的语法错误 

 

三．注释规范

1．在数据库脚本文件头部应进行注释，注释必须列出．创建日期、创建人、功能、修改日期、修改人等，头文件的注释中还应有函数或过程的目的/功能、输入参数、输出参数、返回值、各返回值表示的意义, 调用关系（函数、表）等、取值范围。。

  例如：
        存储过程名：usp_prop_get_dept_month_count()

功      能： 取得部门月汇总...........

输入参数：@dept_id varchar(20) 部门ID

          @mth datatime  日期月份

调用方式：usp_prop_get_dept_month_count ‘123456’,’2010-03-01’
     创建人_时间_说明： 小平 2009.10.23因为某种需要而做的创建.
     修改人_时间_说明1：张三 2010.01.01因为某种需要而做的修改
     修改人_时间_说明N：李四 2010.02.03因为某种需要而做的修改

2．对存储过程的任何修改,都需要在文件头部注释最后添加修改人,修改日期及修改原因等信息.

3．边写代码边注释，修改代码同时修改相应的注释，以保证注释与代码的一致性。不再有用的注释要删除。

4．注释的内容要清楚、明了，含义准确，防止注释二义性，在注释中不允许使用缩写。

5．注释应与其描述的代码相近，对代码的注释应放在其上方或右方（对单条语句的注释）相邻位置，不可放在下面，如放于上方则需与其上面的代码用空行隔开。不允许在一行代码或表达式的中间插入注释。

6．注释与所描述内容进行同样的缩进。

7．对变量的定义和分支语句（条件分支、循环语句等）必须编写注释。

8．在程序块的结束行右方加注释标记，以表明某程序块的结束。

9．对于单行注释使用“--”，对于多行注释可考虑使用“/**/”。

10．语句、参数、变量后的注释采用左对齐方式.

 

 

四．基本语法设计规则

1．尽量使用 SQL-92 标准句法，为了以后数据库能兼容及迁移其它数据库。

2．在从后台数据库取数据时(select)使用 order by，统一不在后台排序，将数据提取到前台后，前台再排序。特殊情况需要排序除外。

3．不要在 T-SQL 代码中使用双引号。字符常量应该使用单引号。

4．尽量使用表变量来代替临时表。SQL Server 2000以上版本中,表变量大大优于临时表。

5．避免隐式的数据类型转换。需要类型转换的地方一定要使用函数进行显示转换。

6．表栏位允许为空和不允许为空的情况下，不允许为空的尽量加上默认值。

7．避免隐式解析对象名称，确保所有对象都归 dbo 所有,在创建对象时在前面加上dbo。

8．判断变量是否为空，应使用 is null 或 is not null 进行比较。对于如果可能为空的变量使用，需要先判断是否为空，或者使用isnull 函数进行空值的转换。

9．确保变量和参数的类型和大小与表数据列相匹配。

10．不允许写select * from ……，必须指明需要读取的具体字段。

11．相同的代码尽量提出来写成一个存储过程和函数。

12．每个表要有主键。如果无现成的字段可用作主键，建议添加一个自增长的整型字段。强烈建议主键为一个字段。

13. 每个表都具备以下字段（创建人，创建时间，最后修改人，最后修改时间）( create_user , create_date , update_user , update_date) 如果需加强数据管理的地方还需增加栏位删除状态(delete_ flag ) ,不是物理删除，只有逻辑删除

13．表设计时．如果某个字段值有多个含义，需要在相应的字段说明里说明各个值的含义。包括设计文档和脚本。

14．不提倡用外键，外键只是增加表与表之间的约束而已.完全可以用触发器或者程序来实现这种约束.因为在程序出错信息方面，外键不够直观，使用后者在提示信息方面更加人性化管理。主外键关系会降低更新及插入数据的性能。导入导出迁移会比较麻烦。

15. 当一个sql语句中涉及到多个表时，始终使用表名别名来限定字段名。这使其他人阅读起来更清楚，避免了含义模糊的引用。

16．表的关键字中，有些用的是decimal(10,0),有些用的是numeric(10,0),规范统一用numeric(18,0),需要小数位的精度统一为2位,特殊情况可以改变。 
       (decimal或numeric 精度1-9占5个字节，10-19占9个字节)

17．CHAR类型长度大于20的不是定长的，全用varchar表示。

 

五．性能规则

1．为了提高性能，应优先使用连接，然后使用子查询或嵌套查询。

2．避免对where子句中的字段使用函数。避免对where子句中的字段进行计算。应先计算好值使用。

3．尽量使用where子句替换having子句，having只对统计结果进行过滤。

4．在长度允许范围内，使用integer代替numeric

5．like子句尽量前端匹配。例如: like ‘中国%’.如果这样like ‘%中国%’,将会引起全表扫描.

6．对于比较复杂的语句，或者涉及数据量较大的表的语句，可以通过做查询计划检测、调整语句性能。

7．尽量不使用 "<>",这会引起全表扫描.

8．分解复杂查询，用常量代替变量

9．使用identity自增列，如果多张表需要共同identity型字段,可以创建一个含有identity类型字段的小表，由sql server自动管理sequence的产生,通过ident_current('table_name')返回为任何会话和任何作用域中的指定表最后生成的标识值(自增字段要慎用，不利于数据迁移,可以不用系统的自增列,改用用户表管理自增列)

10．在select查询时，需要牢记这个原则：最先出现的条件，一定是过滤和排除掉更多结果的条件；第二出现的次之；以此类推。因而，表中不同字段的值的分布，对查询速度有着很大影响。不过,在新的版本中,一般情况数据库能进行自动优化.
11．尽量使用表变量来代替临时表。sql server 2000以上版本中,表变量大大优于临时表。

12查询时不要返回不需要的行或列,或使用top ,尽量减少磁盘IO.

13 在使用union和union all 的时候,不是为了求得行数据唯一性。使用union all性能好. 注意使用distinct ，在没有必要时不要用，它同union一样会使查询变慢

14 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致性能低下的全表扫描.

 

 

 

六．索引规范

1．不要创建过多索引,过多的索引会造成insert,update,delete时间更久。在经常查询的条件中,根据最频繁使用的字段建立索引.

2．频繁更新的字段不创建clustered聚集索引。

3．索引字段要精,不要过多。对所选的每一列，可指出索引是按升序还是降序组织列值。

4．不要在过长的字符型列上建立索引，如长度超过50的varchar字段。这可以避免索引占用过多的空间。

5．为了更好的性能,可以在各自独立的存储设备上创建数据库文件或文件组，并将索引和表分别建在不同的文件组上。

6．不需要将聚集主键字段建在任何非聚集索引中。

7．频繁更新的索引列应该经常整理碎片.

8. 如果使用了in 或者or等时发现查询没有走索引，使用显示申明指定索引：select * from  表名 （index = 索引名） where user_id=20

9. where条件中,应避免在索引列上使用计算,否则会引起全表扫描.

 

 

七．存储过程、函数规范

1．所有的存储过程都应在开始时设置 set nocount on , 此标准同样适用于触发器。

2．共用代码段可以考虑写成一个子过程(或函数),对于控制流语句，不论哪个条件最后都会执行的语句应该放在控制流结束之后执行，这样是为了节约代码，便于维护

3．检查输入参数的有效性由调用者来完成，存储过程内部一般不检查参数有效性（约定）。

4．存储过程的代码必须放在begin...end块中。

5．代码中声明与表的字段相对应的变量时，应保证变量名和字段名相同，数据类型与数据长度相同, 这样规定的目的是增强可读性。

6．存储过程中变量的声明应集中在前段处AS和BEGIN关键字之间，不允许在代码中随处定义变量（重要）。

7．始终指定字符数据类型的长度，并确保允许用户可能需要的最大字符数，因为超出最大长度的字符会丢失. 不能用以下方式: declare @t varchar

8．如果存储过程中都会用到getdate()值,那么就在程序开始时定义一个赋值为getdate()的供整个存储过程使用的变量.

9．对所有变量和输出参数都要做初始化。

10．返回值代码含义必须要在接口设计文档和代码注释中说明，并保持一致。

11．存储过程中每个失败出口均应返回不同的自定义错误码。

12．前台程序在访问数据库时尽量用存储过程访问数据库。
微软的帮助信息：
   使用 SQL Server 中的存储过程而不使用存储在客户计算机本地的 Transact-SQL 程序的优势有：
允许模块化程序设计：
    只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。
允许更快执行：
    如果某操作需要大量 Transact-SQL 代码或需重复执行，存储过程将比 Transact-SQL 批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行 Transact-SQL 语句时，都要从客户端重复发送，并且在 SQL Server 每次执行这些语句时，都要对其进行编译和优化。
减少网络流量：
    一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。
可作为安全机制使用：
    即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。

 

 

八．事务处理

1. 事务操作过程要尽量小，能拆分的事务要拆分开来。

2. 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。

3. 事务操作过程要按同一顺序访问对象。

4．诸如删除/清空临时表之类对数据完整性要求不高的操作，可以不放在事务中。

5．在某些特殊情况下，为了提高代码效率，可以不使用事务控制，但必须通过增加其他的容错处理以保证业务逻辑的正确性。

6．为保证数据的完整性，在存在显式事务的函数中,必须加上SET XACT_ABORT ON

   加上SET XACT_ABORT ON ,在事务内,如果不是语法错误,在执行出错后,事务将不再往下执行,整个事务将自动回滚.这样就无需在每条语句后加上 if @@error <> 0 的判断。

7．尽量避免使用嵌套事务

8. 提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

9. 查询时可以用较低的隔离级别，如果对数据要求不是实时非常之高的要求,像一般的查询特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。

九．游标处理

1．不推荐使用复杂或动态的游标. 能不用游标的尽量不要用.可以用替代方法实现。

2．除非必要否则不要使用static/insensitive游标。打开static游标会造成所有的行都被拷贝到临时表。这正是为什么它对变化不敏感的原因——它实际上是指向临时数据库表中的一个备份。很自然，结果集越大，声明其上的static游标就会引起越多的临时数据库的资源争夺问题。

3. 除非必要否则不要使用keyset游标。和static游标一样，打开keyset游标会创建临时表。虽然这个表只包括基本表的一个关键字列(除非不存在唯一关键字)，但是当处理大结果集时还是会相当大的。

4. 当处理单向的只读结果集时，使用fast_forward代替forward_only。使用fast_forward定义一个forward_only，则read_only游标具有一定的内部性能优化。

5. 使用read_only关键字定义只读游标。这样可以防止意外的修改，并且让服务器了解游标移动时不会修改行。

6. 小心事务处理中通过游标进行的大量行修改。根据事务隔离级别，这些行在事务完成或回滚前会保持锁定，这可能造成服务器上的资源争夺。

7. 在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表增加自增列,用一个从小到最大行的循环处理反而效率更好.