SQLServer开发通用规范.docx

SQLServer开发通用规范.docx

《SQLServer开发通用规范.docx》由会员分享，可在线阅读，更多相关《SQLServer开发通用规范.docx（17页珍藏版）》请在冰豆网上搜索。

SQLServer开发通用规范开发通用规范文件编号:

文件版次:

SQL开发规范开发规范文档编号版本号分册名称sqIServer开发规范第1册/共1册总页数正文附录编制孙启琳审批生效日期东软集团股份有限公司修改记录修改记录版本号变更控制报告编号更改条款及内容更改人审批人更改日期2010001文档创建孙启琳2010-2-21.1目的目的规范sql的编写格式，提高SQL语句的可读性，共享性和执行效率。

1.2使用范围使用范围所有需要与数据库交互的应用系统。

1.3概述概述大部分业务系统需要与数据库进行交互，与数据库交互的主要方式就是SQL语句，编写规范的SQL语句不但利于阅读，而且被数据库重复使用的几率也较大，执行效率相对较高，编写的好的SQL与编写的差的SQL在执行性能上可能会差几倍甚至几千几万倍，因此养成好的SQL编写规范对于提高项目质量及提高开发人员自身素质有着潜在的极大的影响。

1.4书写书写SQL书写遵守如下规范：

在同一个项目中，为了最大限度实现SQL的共享，要求书写sql语句时大小写要一致，为了阅读方便和统一起见，所有SQL语句全部小写（如SQL!

词，字段名，表名等）,常量除外，常量可以按需要书写。

举例：

下面两个相同的语句除常量外都要统一起来。

1）selectnamefromemp;2）selectNAMEfromempwhereemp_no=QD001SQL语句尽可能放在一行，若SQL太长放在一行中影响阅读时可分多行，但要保持缩进一致，缩进可用TAB或者空格，但TAB数和空格数最好一致。

SQL语句中，各谓词之间以空格分割的，尽量保持空格数量一致，即若用一个空格分割，则全部都用一个空格分割，便于数据库能够共享。

能使用绑定变量的，尽量使用绑定变量，尤其是在前台程序中对下面列出的情况，慎重使用绑定变量：

1）列值倾斜严重，如：

某一状态列大部分值是1,只有极少数值为2，这种情况不宜用绑定变量，而应该用常量，便于数据库使用柱状图统计信息2）日期时间列。

总之：

书写SQL的目标是若sql的用途是一样的，则sql应该完全一致，包括空格，大小写。

下面的语句由于写法不完全相同，数据库会理解为4条不同的语句从而导致重复编译，降低了性能。

1）selectnamefromempwhereemp_no=1112）SelectnameFromempWhereemp_no=1113）selectNamefromEMPwhereemp_no=1114）selectnamefromempwhereEMP_NO=111下面的语句，由于语句规范，可以只编译一次。

1）selectnamefromempwhereemp_no=1112）selectnamefromempwhereemp_no=2223）selectnamefromempwhereemp_no=3334）selectnamefromempwhereemp_no=:

b11.5注释注释1.5.1开头注释开头注释所有的过程、函数、触发器、包都应该在开头有注释，注释中要列出对象名称；完成功能简述；调用模块，调用时机；创建日期；作者信息；历次修改日期；历次修改人；历次修改原因和其它作者认为重要的内容，在每次修改的注释之间建一空行。

举例如下：

/*名称：

PRO_WO_MULTI_REPAIR_JOB功能：

多次维修判定程序调用：

自动统计月结前，后台调用作者：

xxx时间：

2009-02-20修改人：

XXX修改时间:

2009-03-08修改内容：

重写部分SQL优化性能修改人：

XXX修改日期：

2008-03-23修改内容：

增加判断条件*1.5.2块注释块注释对于复杂的语句块，必须提供块注释，清晰描述该语句块的功能逻辑、数据结构以及算法；块注释应该和它们所描述的代码具有一样的缩进格式；块注释之首应该有一个空行，用于把块注释和代码分割开来；块注释结构如下所示：

/*计算配送能力系数：

派工系数=a*b+c*d+e*fa:

考评成绩，取xx的考评成绩c:

承担度度，根据当前遗留量和总服务能力计算所得，。

=当前遗留配送量/总配送能力*/1.5.3行注释行注释对于复杂的SQL语句，必须提供行注释，清晰描述该SQL语句功能以及目的;行注释结构如下所示：

-计算动态能力得分，当负荷率超高时，可能出现负值，注意处理。

添加注释时要注意：

注释中包含GO命令时会生成一个错误消息。

1.5.4过程和函数过程和函数1.5.5命名规范命名规范存储过程/函数的命名（代码）使用以下方式：

存储过程名=sp_+模块代码+存储过程代码函数名=F_+模块代码+函数代码存储过程以表现其功能的简洁语言进行命名，定义好后，放置于每一个Diagram的特定区域（一般置于右边部分的上边）。

1.5.6过程过程/函数头定义规范函数头定义规范包括过程/函数名称定义、参数定义、注释说明、变量定义四部分。

参数定义所有的参数必须显示指出变量类型，对于返回参数，必须指出其方向；参数变量定义格式为：

“a“+参数类型简写+“_”+参数名称，其中a是单词argument（参数）的第一个字母，参数类型如下：

文本型：

s日期时间型：

d数字型：

n整形：

i布尔型：

b举例：

as_name标识文本型ad_birthdate表示日期时间型an_age表示数字型注释说明见前面描述。

1.5.7变量定义规范变量定义规范

（1）.变量的命名除游标变量以外，所有的变量命名采用此方式:

变量名=“v_”+变量名称游标变量采用：

变量名=“cur”+变量名（第一个字母大写）。

其中变量名称由代表变量确切含意的单词代码组合而成，每一个单词的首字母根据阅读的方便性决定是否需要大写。

举例：

变量名：

v_EmpName游标名：

curNetlist

（2）.变量类型定义变量的类型时，尽量采用显示定义的方式。

对于仅用于Oracle系列的数据库应用，也可以引用表、游标的引用定义方式。

尽量将变量的定义分开来，并进行格式化，以便程序代码的阅读。

例如：

v_UserNameCHAR（10）v_SEXCHAR

（1）1.5.8变量注释变量注释原则上要求对于一般变量定义时必须加上注释，如变量有特定的值范围，则必须显示描述各确切值及其含意说明；1.5.9过程/函数体定义规范在过程/函数的定义中，最重要的是过程代码的严谨性和可读性，主要包括以下的注意事项：

（1）.如无特殊需要，SQL中涉及的所有内容都要小写。

（2）.原则上要求所有的SQL语句必须在其前面加上注释，对于IF/CASE等流程控制语句，必须在语句前/后说明控制处理和可能的流程方向；（3）.所有的赋值语句要求变量与运算符之间要有空格。

如：

v_Count:

=v_Count+1，并保持适当的对齐；（4）.尽量避免复杂SQL尤其是关联多个大表的SQL对于需要关联多个大表的SQL最好分解成小的SQL分步处理，避免出现性能问题，对于复杂的语句块之间，要求中间加入空行；（5）.所有可能的返回结果，必须在过程体中显示定义和说明，并在注释中说明。

在其它的过程、代码中调用本过程/函数时，必须在其代码中处理所有的各种可能的返回结果；（6）.锁定数据时，尽可能只锁定要操作的数据行，避免锁定整个表，限制使用localtable等DDL语句，若要对数据表、数据行加锁时，需要考虑由此导致的并发操作失败的处理；（7）.所有的过程/函数代码在编写之前，必须要有设计原型及其说明；存储过程/函数错误定义

（1）.所有的DML语句必须考虑死锁、并发、主关键字不唯一等的出错异常处理，应该对所有可能出现的异常进行捕获、编码处理，并注明异常的内容（注：

异常捕获及处理请参见）；

（2）.异常处理有两种需要考虑的问题，一是事务的完整性；二是错误的出错日志及返回处理。

所有异常的根据其类型和等级进行以下的处理：

类型内容处理系统级数据库级发出的底层的异常，如主关键字不唯一冲突等；返回定义错误代码为其它错误，完全回退事务，登录异常日志；应用级应用系统定义并发出的异常，如指定记录不存在；定义应用级错误代码，根据实际情况处理事务；使用原则

（1）.存储过程功能尽量保持独立、复用，但尽量避免嵌套调用，充分考虑其性能体现；

（2）.使用前台代码调用存储过程时，必须考虑返回传入参数的有效性、代码的处理及展示、事务完整性处理；（3）.直接使用参数作为变量处理，不要在存储过程中定义参数同样含义的变量使用，避免由于值的不同导致性能问题。

1.6触发器触发器1.6.1命名规范：

命名规范：

触发器不同类型的命名结构如下操作时机命名结构InsertAfterai表名UpdateAfterau_表名DeleteAfterad_表名162触发器编写规范触发器编写规范请参见存储过程/函数编写规范。

1.7异常异常所有过程和函数处理必须考虑可能出现的错误，一是数据库或操作系统底层错误；二是由于业务逻辑造成的错误；三是无反应或超时；四是输入参数不在指定范围内。

设计所有的过程时，必须考虑出错后的恢复。

1.8优化优化1.8.1SQL语句原则语句原则Where子句尽量避免使用函数；避免在ORDERB子句中使用表达式；限制在GROUPB子句中使用表达式；慎用游标；大小写规范统一，变量绑定统一，避免重复编译；尽可能少的返回结果集行的数量避免使用select*语句；减少结果集中的列的数量；视图嵌套使用不能超过3层；不要使用没有意义的列作为聚集索引列，例如，力卩1自增列；避免隐式类型转换，例如字符型一定要用，数字型一定不要使用；查询语句一定要有范围的限定，避免全表扫描操作；合理对大表进行分区；慎用DISTINCT关键字；慎用UNION关键字，可以用OR替代；使用top1替count（*）来判断是否存在记录；1.8.2索引创建原则索引创建原则同一索引中的组成列最好不要超过3列。

把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

若某列中有大量的值是空值，可以建立索引。

要对值分布较宽的列建立索引。

若表主要用来查询，则可按需要建立索引，若对表操作主要是UPDAT，则尽可能少建索引。

不要对值较窄的列建立索引，如性别不要索引较小的表（如表不足1000行）若某列的值大部分是a,少数是别的值（如b,c,d），且经常以该列的其它值（如b,c,d）为查询条件，则可以将值a设为空值，并在此列上创建索引。

1.8.3充分利用索引充分利用索引1.8.3.1函数、表达式使用在where语句中，尽量避免在运算符左边对列进行函数或者表达式操作，容易引起全表扫描，要尽可能将操作移至运算符右边。

1.8.3.2IN/OR子句使用子句使用IN、ORNOTINSqlServer2005数据库可以分析出应该根据索引查找。

属于2005版本的新特性。

1.8.3.3!

=或或操作符子句使用操作符子句使用!

=或操作符可以用INDEXSEEK查找的，可以正常使用1.8.3.4不要对索引字段进行运算例如：

SELECTIDFROMTWHERENUM/2=100应改为:

SELECTIDFROMTWHERENUM=100*2SELECTIDFROMTWHERENUM/2=NUM1如果NUMt索引应改为：

SELECTIDFROMTWHERENUM=NUM1*2如果NUM有索引则不应该改。

1.8.3.5不要对索引字段进行格式转换日期字段的例子：

WHEREC0NVERT（VARCHAR（1日期字段，120）=2008-08-15应该改为WHERE期字段=2008-08-15AND日期字段2008-08-16ISNULL转换的例子：

WHEREISNULL字段,）应改为:

WHERE字段WHEREISNULL字段,）=不应修改WHEREISNULL字段,F）=T应改为:

WHERE字段=TWHEREISNULL字段,F）T不应修改1.8.3.6不要对索引字段使用函数WHERELEFT（NAME,3）=ABC或者WHERESUBSTRING（NAME,1,3）=ABC应改为:

WHERENAMELIKEABC%日期查询的例子：

WHERDATEDIFF（DAY,日期,2005-11-30）=0应改为：

WHERE日期=2005-11-30AND日期v2005-12-1WHEREDATEDIFF（DAY日期,2005-11-30）0应改为：

WHERE日期=0应改为：

WHERE日期2005-12-011.8.3.7不要对索引字段进行多字段连接例如：

WHEREFAME+.+LNAME=应改为：

WHEREFNAMEH=ANDLNAME=Y1.8.3.8Like的使用的使用对索引列避免使用like%xx,应该使用likexx%。

设计数据结构时就应该考虑这个问题，不要出现必须要采用like%xx才能满足业务需要的情形。

1.9设计设计1.9.1数据库逻辑设计的规范化第1规范：

没有重复的组或多值的列，这是数据库设计的最低要求。

第2规范：

每个非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。

消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。

第3规范：

一个非关键字段不能依赖于另一个非关键字段。

消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

1.9.2合理的冗余合理的冗余完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。

冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。

冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。

从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

1.9.3主键的设计主键的设计主键是必要的，SQLSERVE的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。

聚集索引对查询的影响是比较大的，这个在下面索引的叙述。

在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。

主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

1.9.4外键的设计外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK勺束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。

谨慎使用级联删除和级联更新，级联删除和级联更新作为SQLSERVER2000当年的新功能，在2005作了保留，应该有其可用之处。

因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。

从性能看级联删除和级联更新是比其他方法更高效的方法。

1.9.5字段的设计字段的设计字段是数据库最基本的单位，其设计对性能的影响是很大的。

需要注意如下：

A、数据类型尽量用数字型，数字型的比较比字符型的快很多。

B、数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

C尽量不要允许NULL除非必要，可以用NOTNULL+DEFAU代替。

D少用TEXT和IMAGE二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

E、自增字段要慎用，不利于数据迁移1.9.6索引的设计索引的设计A、根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。

B根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。

C把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。

D一个经常插入更新的表不要加太多索引，因为索引影响插入和更新的速度。

E个多用于查询的表可以适量增加索引，保证查询效率，例如报表系统。

1.10编码阶段编码阶段1.10.1尽可能少的范围数据尽可能少的范围数据A、横向来看，不要写SELECT*的语句，而是选择你需要的字段。

B纵向来看，合理写WHER子句，不要写没有WHER的SQL语句。

C注意SELECTINTO后的WHER子句，因为SELEC1NTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHER子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

D对于聚合查询，可以用HAVING句进一步限定返回的行。

1.10.2尽可能少的重复操作尽可能少的重复操作A、控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的。

B减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

C杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

D合并对同一表同一条件的多次UPDATE比如UPDATEEMPLOYEESETFNAMHE=WHEREEMP_ID=FUPDATEEMPLOYESEETLNAME=YWHEREEMP_ID=F这两个语句应该合并成以下一个语句UPDATEEMPLOYEESETFNAMEH=,LNAME=YWHEREEMP_ID=FE、UPDAT操作不要拆成DELET操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

F、不要写一些没有意义的查询，比如SELECT*FROMEMPLOYEEWHERE1=21.10.3注意事务和锁注意事务和锁事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。

在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。

具体以下方面需要特别注意：

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

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

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

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

E、尽量不要指定锁类型和索引，SQLSERVE允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQLSERVE优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。

F、查询时可以用较低的隔离级别，特别是报表查询的时候，可以选择最低的隔离级别（未提交读）。

1.10.4注意临时表和表变量的用法注意临时表和表变量的用法在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：

A、如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

B、如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

C、如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

D其他情况下，应该控制临时表和表变量的使用。

E、关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

F、关于临时表产生使用SELECTINT口CREATETABLE+INSERTINTO的选择，我们做过测试，一般情况下，SELECTNTO会比CREATTABLE+INSERTINT0的方法快很多，但是SELECTNT0会锁定TEMPD的系统表SYSOBJECTSSYSINDEXESSYSCOLUMJN在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATETABLE+INSERTINT大数据量的单个语句使用中，使用SELECTINTO。

G注意排序规则，用CREATETABL建立的临时表，如果不指定字段的排序规则，会选择TEMPD的默认排序规则，而不是当前数据库的排序规则。

如果当前数据库的排序规则和TEMPDB勺排序规则不同，连接的时候就会出现排序规则的冲突错误。

一般可以在CREATETABL建立临时表时指定字段的排序规则为DATABASE_DEFAU来避免上述问题。

1.10.5注意子查询的用法注意子查询的用法子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATEDELETE语句或其它子查询中。

任何允许使用表达式的地方都可以使用子查询。

子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。

但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。

如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。

相关子查询可以用IN、NOTIN、EXISTSNOTEXISTS引入。

关于相关子查询，应该注意：

ANOTIN、NOTEXISTS勺相关子查询可以改用LEFTJOIN代替写法。

比如：

SELECTPUB_NAMEFROMPUBLISHERS（SELECTPUB_IDFROMTITLESWHERETYPE=BUSINESS）可以改写成：

SELECTFROMPUBLISHERSALEFTJOINTITLESBON=BUSINESSAND.PUB_ID=B.PUB_IDREISNULLSELECTTITLEROMTITLESHERENOTEXISTS（SELECTTITLE_IDFROMSALESWHERETITLE_ID=可以改写成：

SELECTTITLEOMTITLESLEFTJOINSALESN=WHEREISNULLB如果保证子查询没有重复，IN、EXISTS的相关子查询可以用INNERJOIN代替。

比如：

SELECTPUB_NAMEOMPUBLISHERSWHEREPUB_IDIN（SELECTPUB_IDFROMTITLESWHERETYPE=BUSINESS）可以改写成：

SELECTDISTINCTFROMPUBLISHERSAINNERJOINTITLESBON=BUSINESSAND=B.PUB_IDC不要用COUNT（*的子查询判断是否存在记录，最好用LEFTJOIN或者EXISTS比如有人写这样的语句：

SELECTJOB_DESCFROMJOBSWHERE（SELECTCOUNT（*）FROMEMPLOYEEWHEREJOB_ID=0应