.数据库设计和编码规范————————————————————————————————作者：————————————————————————————————日期：数据库设计和编码规范Version 1.0目录1简介 (7)1.1读者对象 (7)1.2目的 (7)2数据库命名规范 (7)2.1规范总体要求 (7)2.2数据库对象命名规范 (8)2.3变量命名规范 (9)3数据库设计规范 (9)3.1选择有效的设计工具 (10)3.2表的设计 (10)3.2.1遵守范式要求103.2.2字段设计113.2.3适当的合理的冗余123.2.4注意大类型的字段设计133.3表关系和约束设计 (13)3.3.1主键设计143.3.2 外键设计 (15)3.3.3 检查约束 (15)3.4索引的设计 (16)3.4.1聚集索引和非聚集索引163.4.2索引的初始创建原则183.4.3索引的注意事项183.4.4索引的后期维护工作193.5物理存储设计 (20)3.5.1日志文件另外存放203.5.2存储空间的设计204T-SQL编码规范 (22)4.1书写基本规范 (22)4.2使用可搜索参数（WHERE使用原则） (23)4.3少用触发器和禁用游标 (24)4.4联合查询尽可能使用UNION ALL (25)4.5尽可能避免的地方 (25)4.6避免返回和使用多余的数据 (26)4.7操作符优化 (26)4.8数据库事务处理原则 (27)4.9最少次数的访问表 (28)4.10避免隐含的数据类型转换 (28)4.11表变量、临时表和公用表达式的用法 (30)4.12正确地判断记录是否存在 (32)4.13注意自定义标量函数的影响 (32)4.14避免编写复杂的TSQL语句 (33)4.15应用程序层防止执行大块的TSQL语句 (33)4.16对数据库大表的处理方案 (34)4.17SP_EXECUTESQL代替EXEC (35)4.18存储过程的一些建议 (36)5如何进行质量控制 (36)5.1规范的制定、认可和实施 (36)5.2讨论和检查工作 (36)5.3对制定的规范不断完善 (37)5.4讨论和制定公共模板 (37)5.4.1SELECT语句385.4.2JOIN语句385.4.3子查询395.4.4INSERT语句395.4.5UPDA TE语句395.4.6DELETE语句395.4.7CASE语句405.4.8IF语句405.4.9WHILE语句405.4.10EXISTS语句 405.4.11变量声明 415.4.12变量赋值 415.4.13创建表及约束索引 415.4.14存储过程 425.4.15带输出参数的存储过程 435.4.16视图 445.4.17物化视图 445.4.18自定义标量函数 455.4.19自定义表值函数（多语句） 455.4.20自定义表值函数（内联） 465.4.21索引整理 475.4.22数据库事务格式 471简介1.1 读者对象此文档说明书供开发部全体成员阅读。
1.2 目的一个合理的数据库结构设计是保证系统性能的基础。
一个好的规范让新手容易进入状态且少犯错，保持团队支持顺畅，系统长久使用后不至于紊乱，让管理者易于在众多对象中，获取所需或理清问题。
同时，定义标准程序也需要团队合作，讨论出大家愿意遵循的规范。
随着时间演进，还需要逐步校订与修改规范，让团队运行更为顺畅。
2数据库命名规范团队开发与管理信息系统讲究默契，而制定服务器、数据库对象、变量等命名规则是建立默契的基本。
命名规则是让所有的数据库用户，如数据库管理员、程序设计人员和程序开发人员，可以直观地辨识对象用途。
而命名规则大都约定俗成，可以依照公司文化、团队习惯修改并落实。
2.1 规范总体要求1.避免使用系统产品本身的惯例，让用户混淆自定义对象和系统对象或关键词。
例如，存储过程不要以sp_或xp_开头，因为SQL SERVER的系统存储过程以sp_开头，扩展存储过程以xp_开头。
2.不要使用空白符号、运算符号、中文字、关键词来命名对象。
3.名称不宜过于简略，要让对象的用途直观易懂，但也不宜过长，造成使用不方便。
4.不用为数据表内字段名称加上数据类型的缩写。
5.名称中最好不要包括中划线。
6.禁止使用[拼音]+[英语]的方式来命名数据库对象或变量。
2.2 数据库对象命名规范我们约定，数据库对象包括表、视图（查询）、存储过程（参数查询）、函数、约束。
对象名字由前缀和实际名字组成，长度不超过30。
避免中文和保留关键字，做到简洁又有意义。
前缀就是要求每种对象有固定的开头字符串，而开头字符串宜短且字数统一。
可以讨论一下对各种对象的命名规范，通过后严格按照要求实施。
例如：对象命名规范数据库数据库名：[项目英文名称]+DB数据文件：[数据库名称]+_Data.mdf日志文件：[数据库名称]+_Log.ldf表前缀T+[表名];单词首写字母为大写，其余全部小写。
示范：TCustomer表字段不需要前缀，直接用英文单词或缩写，单词首字母为大写，其余为小写。
例如：UserName，如果是两个单词的首写字母缩写，统一用大写，比如:UserID主键所在字段不要用ID。
一律用表名+ID (如果表名太长的话，采用缩写用各单词的首写字母组合)存储过程用P_前缀+[功能描述] (首单词大写，其余下写)例如：P_GetAllCorps视图用前缀V_+[视图名称]例如：V_Account自定义标量函数前缀F_+[功能描述] (首单词大写，其余下写)例如：F_GetEWSourceName自定义表值函数前缀TF_+[功能描述] (首单词大写，其余下写)主键PK_[表名]例如：PK_TExAccount外键用FK_[主表名] _字段表表示（考虑到名字会比较长，突出主表）例如：FK_TOrder_OrderID默认值约束用DF_[表名]_[字段名] 表示例如：DF_TOrder_Type检查约束用CK_[表名]_[字段名] 表示例如：CK_TCustomer_Mail唯一性约束用UQ_[表名]_[字段名] 表示例如：UQ_TCustomer_Code聚集索引用DX_[表名]_[字段名] 表示例如：DX_TCachet_ID其它索引用IX_[表名]_[字段名] 表示(字段名较多时，取前面两个即可)例如：IX_TCachet_CName_CorpID2.3 变量命名规范1.数据列参数命名格式为@+[列名称]。
示例：@EmployeeID @employee_id2.非数据列参数在参数无法跟列名称进行关联时，使用能够反映该参数功能的英文单词或单词组合,采用Pascal样式命名。
示例：@WorkType @work_type3数据库设计规范好的数据库架构设计对系统运行的性能起着很大的作用，所以要在开始时就要引起重视。
为了保证数据库设计的高效必须安排时间对设计结果进行评审，这一环节必不可少。
3.1 选择有效的设计工具数据库设计工具：Power Designer、ER Studio、Rose、Microsoft Visio。
项目开始前要确定使用哪种设计工具。
(另有开发插件:RedGate系列(SQL Prompt)) 选择的工具要便于讨论便入生成脚本导入数据库。
设计通过后要形成文档，并且这个结构设计文档要存档，签入VSS基线库中。
在进行数据库设计时，应随时进行数据字典的维护。
(字段要求写说明)3.2 表的设计表设计在数据库设计中占据有十分重要的地位。
表是实际存储数据的对象。
除了要注重表结构设计，字段的设计之外还要注意表之间关系的设计。
3.2.1遵守范式要求通常，合理的规范化会最小化数据异常和减少数据的冗余。
为了更新数据的正确与快速，在设计的初始阶段多采用三范式设计数据库表。
第一范式强调的是列的原子性，即列不能够再分成其他几列。
第二范式包含两层意思，一是表必须有一个主键；二是非主键列必须完全依赖于主键，且不能只依赖于主键的一部分。
(尽量少使用复合主键)第三范式需要确保数据表中的所有非主键列直接与主键列相关，而不能直接依赖于非主键列。
3.2.2字段设计1.尽量避免可为空的列。
虽然在个别情况下，允许空值可能是有用的，但是应尽量少用。
这是因为需要对它们进行特殊处理，从而会增加数据操作的复杂性和增加CPU额外的逻辑判断。
很多情况下可以考虑用默认值0或空字符串('')来代替NULL值。
所以字段应该有NOT NULL的限制。
2.Unicode的选择。
nvarchar和nchar相应比varchar和char要占用更多的存储空间。
设计的原则是：如果确保存储的内容只是纯英文和数字，用char/varchar。
如果含有中文字符或其它多国语言，用nchar/nvarchar。
3.字段长度要精确，遵守“必须、够用”的原则。
精确的长度设计既能完整的描述数据，又可以节省存储空间。
积小成大，当数据表中的数据有很多记录的时候，这种存储空间的优势就能体现得十分明显。
存储空间越紧凑，分配的页面就越少，在同样大小的内存空间中就可以存储更多的页面，这样操作数据的效率就会提高。
例如能用char(10)的就不要用char(20),提高存储的利用率和系统性能，但同时也要兼顾扩展性和可移植性。
字段类型存储空间补充说明bigint 8 字节-2^63 (-9,223,372,036,854,775,808) 到2^63-1(9,223,372,036,854,775,807)int 4 字节-2^31 (-2,147,483,648) 到2^31-1 (2,147,483,647) smallint 2 字节-2^15 (-32,768) 到2^15-1 (32,767)tinyint 1 字节0 到255decimal(9,2) 5 字节decimal(9,2) 前面的9为精度，后面为小数位。
当精度位于1~9之间时，占5字节。
当精度位于10~19之间时，占9 decimal(19,2) 9字节字节。
注意，numeric 在功能上等价于decimal。
money 8 字节-922,337,203,685,477.5808 到922,337,203,685,477.5807 smallmoney 4 字节-214,748.3648 到214,748.3647datetime 8字节精确到3.33 毫秒。
例如：2014-03-07 17:25:39.450存储范围：1753 年1 月1 日到9999 年12 月31 日smalldatetime 4字节精确到分钟，例如：2014-03-07 17:24:00存储范围是：1900 年 1 月1 日到2079 年6 月6 日uniqueidentifier 16字节uniqueidentifier 数据类型可存储16 字节的二进制值，其作用与全局唯一标识符(GUID) 一样。