1、数据库开发规范SQLSERVER篇最新版数据库开发规范 （SQL SERVER篇）拟制： 日期： 审核： 日期：批准： 日期：文档编号: 密级: 机密修订历史记录日期版本说明作者2012-10-171.0创建本文档第一章 命名规范1. 命名标志法使用下面的三种大写标识符约定。Pascal 大小写将标识符的首字母和后面连接的每个单词的首字母都大写。可以对三字符或更多字符的标识符使用 Pascal 大小写。例如：BackColorCamel 大小写标识符的首字母小写，而每个后面连接的单词的首字母都大写。例如：backColor大写标识符中的所有字母都大写。仅对于由两个或者更少字母组成的标识符使用该
2、约定。例如：System.IOSystem.Web.UI可能还必须大写标识符以维持与现有非托管符号方案的兼容性，在该方案中所有大写字母经常用于枚举和常数值。一般情况下，在使用它们的程序集之外这些字符应当是不可见的。2. 数据库命名数据库名要求全部使用Pascal命名法例如： MFC MFC53 DataController3. 数据库月份库、数据表日分库命名规则DatabaseName按数据库命名要求命名TableName按数据表命名规则命名Month, Day要求中间无任何连接符例如 MFCLOG200301 MFC_log_ClientCheckin200301094. 分段数据库分库命名
3、规则DatabaseName按数据库命名要求命名Segment是分段的编号，要求长度一致并且3位或者以上例如 NIDCHyper0215. 分段分日期数据库分库命名规则DatabaseName按数据库命名要求命名Segment是分段的编号，要求长度一致并且3位或者以上Day要求中间无任何连接符例如GatheredLog00120110227MassLog003201102276. 表的命名_SystemName为表所属的系统名，此处要求采用Pascal命名法TableType为数据表的类别，此处要求全部使用小写，在我们的库中有如下几种数据表类型：tb-数据表,stat-统计表,dict-字典表
4、,sys-系统信息表,re-关系表,log-日志表Name为数据库表的名称，此处要求使用Pascal命名法 例如： MFC_tb_Unit 场所信息表 MFC_stat_UnitDailyStatus 场所状态日统计表 MFC_re_UserArea 用户地区关系表 MFC_log_Customer 顾客日志表MFC_dict_Sex 性别字典表7. 字段命名字段命名统一使用Pascal标志法，单词中间不用下划线。应尽量使用简短而又能说明字段实际意义的词组组合，为保证不与系统字段重复，应尽量至少使用两个单词。同样含义的字段应尽量使用已有字段的物理名。例如： CertificateCode 证件
5、号 CertificateType 证件类别 AlertClassName 报警类别名8. 存储过程命名_ SystemName是系统名，此处要求使用Pascal命名法，对于跨系统使用的存储过程要求此段，其他非跨系统的存储过程不要求。FunctionModule为功能模块名，此处要求使用Pascal标志法 TableName为数据库已有表名，命名规则同上面的表命名要求 FunctionName为存储过程的功能说明，此处要求使用Pascal标志法。 常用的功能有： GetList 取多条记录 GetModel 去单条记录 GetListByCondition 根据Condition条件取单条记录
6、 Add 插入或修改单条记录 Delete 删除记录 Insert 插入单条记录 BatchInsert 批量插入多条记录 BatchUpdate 批量更新多条记录 Update 更新单条记录例如： Communication_MFC_re_UnitStatus_GetList DataAnalysis_NIDC_tb_PersonGroup_Delete DataAnalysis_MFC_tb_CrimeOnEsc_Add9. 触发器命名, TR_如果只是针对单个操作类型的触发器，则要求说明操作类型：例如： TR_MFC_tb_Argot TR_MFC_tb_Argot_Insert10.
7、索引命名IX_例如：IX_MFC_log_Customer_EndTime11. 主键PK_。TableName同表命名规则 例如 PK_MFC_Log_Customer12. 外键FK_例如：FK_MFC_log_Customer_MFC_tb_Unit13. 缺省值DF_例如：DF_MFC_log_Customer_UserName 14. 视图的命名用Pascal标志法，和表一致；_view_视图的命名除中间用view链接以外与表一致例如： MFC_view_Strategy15. 函数的命名 采用存储过程同样的命名规则16. 其他数据库对象命名规则其他数据库对象，比如约定、队列、服务、
8、路由等采用表名相同的命名法。17. 其他数据库可编程性对象命名其他数据库可编程性对象采用存储过程相同的命名法。18. 数据库保留字不要使用数据库保留字，给数据对象命名；19. 禁止使用空格在数据库对象命名时，禁止使用空格。第二章 常用数据类型下面是我们再数据库设计中常用的几种数据类型：数据类型类型描 述int整型int 数据类型可以存储从- 231(-2147483648)到231(2147483 647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节bigint整型从-263(-9223372036854775808)到263-1(92
9、23372036854775807)的整型数据。这种数据类型在数据库里占用8 字节空间numeric精确数值型numeric数据类型与decimal 型相同（要求在存储过程或其他语句中必须表名数据长度及精度）datetime日期时间型datetime数据类型用来表示日期和时间。这种数据类型存储从1753年1月1日到9999年12月3 1日间所有的日期和时间数据， 精确到三百分之一秒或3.33毫秒cursor特殊数据型cursor 数据类型是一种特殊的数据类型，它包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用Uniqueidentifier特殊数据型Uniqueidenti
10、fier数据类型用来存储一个全局唯一标识符，即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列char字符型char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，你必须指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型的列宽最大为8000 个字符varchar字符型varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。与char 型不一样，此数据类型
11、为变长。当定义一列为该数据类型时，你要指定该列的最大长度。 它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度nvarchar统一编码字符型nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍Nvarchar(max)统一编码字符型最多为2301(1073741823)Unicode字符，占用2字符数2字节的空间Varchar(max)字符型最多为2311(2147483647)字符，一般用来定义XML的入参，每字符1字节2字节额外开销varbinary(max)二进制数据类型可变长度二进制数据。 n 的取值范围为
12、1 至 8,000。 max 指示最大存储大小是 231-1 个字节。 存储大小为所输入数据的实际长度 + 2 个字节。第三章 数据库设计规范1. 三范式数据库设计中应尽可能遵守三范式。所谓三范式即：没有重复的组或多值的列，这是数据库设计的最低要求。 非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。2. 适当的冗余但是完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计
13、划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。比如一些日志表的历史统计信息，我们可以通过作业定期在数据库负载较小的凌晨8点对数据日志数据进行统计，并建立冗余的统计表记录下来。3. 主键主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚
14、集索引对查询的影响是比较大的，这个在下面索引的叙述。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。4. 索引索引分为聚集索引和非聚集索引。每个数据表只能建立一个聚集索引，聚集索引决定了数据在表中的物理顺序，同时非聚集索引依赖聚集索引存在。每一个非聚集索引B树的页节点都存有对应的聚集索引键。因此聚集索引和非聚集索引的选择应该遵守如下规范：1)应尽量选择符合唯一约
15、束的字段建立聚集索引2)尽量选择占用空间较小的字段建立聚集索引，一般要求聚集索引小于900字节3)根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。同时对数据量比较大的表(1000行)应结合数据表的使用情况建立非聚集索引以提高数据库查询的反应效率。但是过多的非聚集索引也会影响数据表记录的插入及更新速度，一般要求非聚集索引的个数不超过两位数。因此应该针对各数据表的实际情况设计索引。4)若某列的值大部分是a，少数是别的值（如b,c,d），且经常以该列的其它值(如b,c,d)为查询条件，可以考虑对(如b,c,d)建立筛选索引。5)把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺
16、序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面，同一索引中的组成列最好不要超过3列。6)根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。7)若表主要用来查询，则可按需要建立索引，若对表操作主要是UPDATE，则尽可能少建索引。5. 主键与聚集索引的关系在数据库设计中，我们经常容易混淆主键和聚集索引的关系。因为如果我们建立主键的时候没有特别说明，SQL SERVER会默认在主键上建立聚集索引。同时由于聚集索引同时也是唯一索引，而且主键一般为较小的键。所以我们经常将主键作为聚集索引。但是这并不表示主键和聚集索引等同
17、。第四章 存储过程编写规范统一和规范的代码书写风格对保证软件的开发质量、提高团队的开发效率以及将来的维护及其扩展都至关重要。1. 注释为了增强可读性及美观性，在存储过程头部和存储过程中间应尽量按照如下演示的存储过程做好注释。2. 书写规范数据库服务器端的触发器和存储过程是一类特殊的文本，为方便开发和维护，提高代码的易读性和可维护性。规范建议按照分级缩进格式编写该文本。1)编写存储过程时应遵守以下缩进规则，如下示例IF 11 BEGIN -每个IF条件后的程序块缩进 SELECT U.GuildID -各字段尽量对其 ,U.UnitCode -每个查询字段要写明表别名或表名 ,U.UnitID
18、,U.AreaCode FROM MFC_HOTEL.dbo.MFC_tb_Unit U WITH(NOLOCK) INNER JOIN MFC_HOTEL.dbo.MFC_tb_Area A WITH(NOLOCK) ON U.AreaCode = A.AreaCode -JOIN条件缩进增强层次感 WHERE A.IsActive=0 -FROM,JOIN,WHERE对齐 ENDELSE RETURN 2)不要使用SELECT * 需要哪些字段，查询哪些字段, 尽可能少的返回结果集行的数量。3)在多表关联时，列名前需要加上别名（或表名），表名前加Owner（dbo）。如果涉及到跨数据库，就
19、需要加上数据库名称。例如：AdventureWorks.dbo.Contact；存储过程也一样；4)SQL保留字要大写对SQL的保留字，都需要大写。例如：SELECT，UPDATE，INSERT，WHERE，INNER JOIN，AND，OR等。5)过多使用GOTO语句会使得代码可读性降低6)查询列表和条件中的字段全部需要指定所属的表，可以使用表名别名简化。表名别名要简短，但意义要尽量明确，避免使用A、B、C等过于简单的别名。通常，使用大写的表名作为别名，使用 AS 关键字指定表或字段的别名。3. 性能相关 1)Where子句尽量避免使用函数；2)避免在ORDER BY子句中使用表达式；3)限
20、制在GROUP BY子句中使用表达式；4)慎用游标；5)避免隐式类型转换，例如字符型一定要用，数字型一定不要使用；6)查询语句一定要有范围的限定，避免全表扫描操作；7)慎用DISTINCT关键字；8)慎用OR关键字，可以用UNION ALL替代；9)除非必要，尽量用UNION ALL而非UNION10)使用EXISTS(SELECT 1)替count(*)来判断是否存在记录；11)SET NOCOUNT ON 语句把 SET NOCOUNT ON 语句放到存储过程和触发器中，作为第一句执行语句。例如：CREATE PROCEDURE dbo.UP_GetOrgChildrenASBEGIN S
21、ET NOCOUNT ON .关闭数据库提示输出。 4. 尽量使用索引1)IN/OR子句使用IN、OR、NOT IN 应尽量避免使用，这可能会导致SQL SERVER不使用索引而选择全表扫描，可以索引查找的，可以正常使用。2)!=或操作符子句使用!=或操作符应尽量避免使用，可以用索引查找的，可以正常使用。3)不要对索引字段进行运算例如：SELECT ID FROM T WHERE NUM/2=100应改为:SELECT ID FROM T WHERE NUM=100*2SELECT ID FROM T WHERE NUM/2=NUM1如果NUM有索引应改为:SELECT ID FROM T W
22、HERE NUM=NUM1*2如果NUM1有索引则不应该改。4)不要对索引字段进行格式转换日期字段的例子：WHERE CONVERT(VARCHAR(10),日期字段,120)=2008-08-15应该改为WHERE 日期字段=2008-08-15 AND 日期字段=2005-11-30 AND 日期0应改为:WHERE 日期=0应改为:WHERE 日期2005-12-01WHERE DATEDIFF(DAY, 日期,2005-11-30)=2005-12-01WHERE DATEDIFF(DAY, 日期,2005-11-30)=2005-11-306)不要对索引字段进行多字段连接例如：WHE
23、RE FAME+.+LNAME=H.Y 应改为: WHERE FNAME=H AND LNAME=Y7)Like的使用对索引列避免使用like %xx， 应该使用like xx%。设计数据结构时就应该考虑这个问题，不要出现必须要采用like %xx才能满足业务需要的情形。5. 事务和锁事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：1)使用NOLOCK提示查询优化器在繁忙的系统中，对改善并发问题，是个不错的选择；2)在存储过程，触发器，以及S
24、QL 簇中，尽可能按照相同的循序来访问相关的表。这样可以减少死锁的机会； 3)事务尽可能短4)在事务中涉及到数据修改量，尽可能小，提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。5)事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。6)尽可能低的设置锁，以及隔离的级别。7)尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会
25、变化的。6. 其他注意事项1)在相关表存在的数据库下创建存储过程和函数2)有设置默认值限制的字段不允许设置为可以为空3)合理对大表进行分区 4)视图嵌套使用不能超过3层5)对数据量比较大的日志表，应按日期，ID段分库分表7. 注意临时表和表变量的用法在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：1)如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。2)如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。3)如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。4)其他情况下，应该控制临时表和表变量的
26、使用。5)关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。6)临时表使用CREATE TABLE + INSERT INTO的方式8. 注意子查询的用法子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段
27、，这种子查询就叫做相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。关于相关子查询，应该注意：1)NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。例如：SELECT BEA.AddressID ,BEA.AddressTypeID FROM AdventureWorks2012.Person.BusinessEntityAddress BEA WITH(NOLOCK) WHERE BusinessEntityID NOT IN (SELECT BusinessEntityID FROM AdventureWorks2012.Person.BusinessEntity WITH(NOLOCK)可以改写成SELECT BEA.AddressID ,BEA.AddressTypeID FROM AdventureWorks2012.Person.BusinessEntityAddress BEA WITH(NOLOCK) LEFT JOIN AdventureWorks2012.Person.BusinessEntity BE WITH(NOLOCK) ON BEA.BusinessEntityID = BE
