数据库设计和编码规范

.数据库设计和编码规范————————————————————————————————作者：————————————————————————————————日期：数据库设计和编码规范Version1.0目录TOC\o"1-3"\h\z\uHYPERLINK\l"_Toc460340862"1简介PAGEREF_Toc460340862\h4HYPERLINK\l"_Toc460340863"1.1读者对象PAGEREF_Toc460340863\h4HYPERLINK\l"_Toc460340864"1.2目的PAGEREF_Toc460340864\h4HYPERLINK\l"_Toc460340865"2数据库命名规范PAGEREF_Toc460340865\h4HYPERLINK\l"_Toc460340866"2.1规范总体要求PAGEREF_Toc460340866\h4HYPERLINK\l"_Toc460340867"2.2数据库对象命名规范PAGEREF_Toc460340867\h5HYPERLINK\l"_Toc460340868"2.3变量命名规范PAGEREF_Toc460340868\h6HYPERLINK\l"_Toc460340869"3数据库 设计规范 民用建筑抗震设计规范配电网设计规范10kv变电所设计规范220kv变电站通用竖流式沉淀池设计 PAGEREF_Toc460340869\h6HYPERLINK\l"_Toc460340870"3.1选择有效的设计工具PAGEREF_Toc460340870\h7HYPERLINK\l"_Toc460340871"3.2表的设计PAGEREF_Toc460340871\h7HYPERLINK\l"_Toc460340872"3.2.1遵守范式要求PAGEREF_Toc460340872\h7HYPERLINK\l"_Toc460340873"3.2.2字段设计PAGEREF_Toc460340873\h8HYPERLINK\l"_Toc460340874"3.2.3适当的合理的冗余PAGEREF_Toc460340874\h9HYPERLINK\l"_Toc460340875"3.2.4注意大类型的字段设计PAGEREF_Toc460340875\h10HYPERLINK\l"_Toc460340876"3.3表关系和约束设计PAGEREF_Toc460340876\h10HYPERLINK\l"_Toc460340877"3.3.1主键设计PAGEREF_Toc460340877\h11HYPERLINK\l"_Toc460340878"3.3.2外键设计PAGEREF_Toc460340878\h12HYPERLINK\l"_Toc460340879"3.3.3检查约束PAGEREF_Toc460340879\h12HYPERLINK\l"_Toc460340880"3.4索引的设计PAGEREF_Toc460340880\h13HYPERLINK\l"_Toc460340881"3.4.1聚集索引和非聚集索引PAGEREF_Toc460340881\h13HYPERLINK\l"_Toc460340882"3.4.2索引的初始创建原则PAGEREF_Toc460340882\h15HYPERLINK\l"_Toc460340883"3.4.3索引的注意事项PAGEREF_Toc460340883\h15HYPERLINK\l"_Toc460340884"3.4.4索引的后期维护工作PAGEREF_Toc460340884\h16HYPERLINK\l"_Toc460340885"3.5物理存储设计PAGEREF_Toc460340885\h17HYPERLINK\l"_Toc460340886"3.5.1日志文件另外存放PAGEREF_Toc460340886\h17HYPERLINK\l"_Toc460340887"3.5.2存储空间的设计PAGEREF_Toc460340887\h17HYPERLINK\l"_Toc460340888"4T-SQL编码规范PAGEREF_Toc460340888\h18HYPERLINK\l"_Toc460340889"4.1书写基本规范PAGEREF_Toc460340889\h19HYPERLINK\l"_Toc460340890"4.2使用可搜索参数（where使用原则）PAGEREF_Toc460340890\h20HYPERLINK\l"_Toc460340891"4.3少用触发器和禁用游标PAGEREF_Toc460340891\h21HYPERLINK\l"_Toc460340892"4.4联合查询尽可能使用UNIONALLPAGEREF_Toc460340892\h22HYPERLINK\l"_Toc460340893"4.5尽可能避免的地方PAGEREF_Toc460340893\h22HYPERLINK\l"_Toc460340894"4.6避免返回和使用多余的数据PAGEREF_Toc460340894\h22HYPERLINK\l"_Toc460340895"4.7操作符优化PAGEREF_Toc460340895\h23HYPERLINK\l"_Toc460340896"4.8数据库事务处理原则PAGEREF_Toc460340896\h24HYPERLINK\l"_Toc460340897"4.9最少次数的访问表PAGEREF_Toc460340897\h25HYPERLINK\l"_Toc460340898"4.10避免隐含的数据类型转换PAGEREF_Toc460340898\h25HYPERLINK\l"_Toc460340899"4.11表变量、临时表和公用表达式的用法PAGEREF_Toc460340899\h27HYPERLINK\l"_Toc460340900"4.12正确地判断记录是否存在PAGEREF_Toc460340900\h29HYPERLINK\l"_Toc460340901"4.13注意自定义标量函数的影响PAGEREF_Toc460340901\h29HYPERLINK\l"_Toc460340902"4.14避免编写复杂的TSQL语句PAGEREF_Toc460340902\h30HYPERLINK\l"_Toc460340903"4.15应用程序层防止执行大块的TSQL语句PAGEREF_Toc460340903\h30HYPERLINK\l"_Toc460340904"4.16对数据库大表的处理方案PAGEREF_Toc460340904\h31HYPERLINK\l"_Toc460340905"4.17sp_executesql代替EXECPAGEREF_Toc460340905\h32HYPERLINK\l"_Toc460340906"4.18存储过程的一些建议PAGEREF_Toc460340906\h33HYPERLINK\l"_Toc460340907"5如何进行质量控制PAGEREF_Toc460340907\h33HYPERLINK\l"_Toc460340908"5.1规范的制定、认可和实施PAGEREF_Toc460340908\h33HYPERLINK\l"_Toc460340909"5.2讨论和检查工作PAGEREF_Toc460340909\h33HYPERLINK\l"_Toc460340910"5.3对制定的规范不断完善PAGEREF_Toc460340910\h34HYPERLINK\l"_Toc460340911"5.4讨论和制定公共模板PAGEREF_Toc460340911\h34HYPERLINK\l"_Toc460340912"5.4.1SELECT语句PAGEREF_Toc460340912\h35HYPERLINK\l"_Toc460340913"5.4.2JOIN语句PAGEREF_Toc460340913\h35HYPERLINK\l"_Toc460340914"5.4.3子查询PAGEREF_Toc460340914\h36HYPERLINK\l"_Toc460340915"5.4.4INSERT语句PAGEREF_Toc460340915\h36HYPERLINK\l"_Toc460340916"5.4.5UPDATE语句PAGEREF_Toc460340916\h36HYPERLINK\l"_Toc460340917"5.4.6DELETE语句PAGEREF_Toc460340917\h36HYPERLINK\l"_Toc460340918"5.4.7CASE语句PAGEREF_Toc460340918\h37HYPERLINK\l"_Toc460340919"5.4.8IF语句PAGEREF_Toc460340919\h37HYPERLINK\l"_Toc460340920"5.4.9WHILE语句PAGEREF_Toc460340920\h37HYPERLINK\l"_Toc460340921"5.4.10EXISTS语句PAGEREF_Toc460340921\h37HYPERLINK\l"_Toc460340922"5.4.11变量声明PAGEREF_Toc460340922\h38HYPERLINK\l"_Toc460340923"5.4.12变量赋值PAGEREF_Toc460340923\h38HYPERLINK\l"_Toc460340924"5.4.13创建表及约束索引PAGEREF_Toc460340924\h38HYPERLINK\l"_Toc460340925"5.4.14存储过程PAGEREF_Toc460340925\h39HYPERLINK\l"_Toc460340926"5.4.15带输出参数的存储过程PAGEREF_Toc460340926\h40HYPERLINK\l"_Toc460340927"5.4.16视图PAGEREF_Toc460340927\h41HYPERLINK\l"_Toc460340928"5.4.17物化视图PAGEREF_Toc460340928\h41HYPERLINK\l"_Toc460340929"5.4.18自定义标量函数PAGEREF_Toc460340929\h42HYPERLINK\l"_Toc460340930"5.4.19自定义表值函数（多语句）PAGEREF_Toc460340930\h42HYPERLINK\l"_Toc460340931"5.4.20自定义表值函数（内联）PAGEREF_Toc460340931\h43HYPERLINK\l"_Toc460340932"5.4.21索引整理PAGEREF_Toc460340932\h44HYPERLINK\l"_Toc460340933"5.4.22数据库事务格式PAGEREF_Toc460340933\h44简介读者对象此文档 说明书 房屋状态说明书下载罗氏说明书下载焊机说明书下载罗氏说明书下载GGD说明书下载 供开发部全体成员阅读。目的一个合理的数据库结构设计是保证系统性能的基础。一个好的规范让新手容易进入状态且少犯错，保持团队支持顺畅，系统长久使用后不至于紊乱，让管理者易于在众多对象中，获取所需或理清问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 。同时，定义 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 程序也需要团队合作，讨论出大家愿意遵循的规范。随着时间演进，还需要逐步校订与修改规范，让团队运行更为顺畅。数据库命名规范团队开发与管理信息系统讲究默契，而制定服务器、数据库对象、变量等命名规则是建立默契的基本。命名规则是让所有的数据库用户，如数据库管理员、程序设计人员和程序开发人员，可以直观地辨识对象用途。而命名规则大都约定俗成，可以依照公司文化、团队习惯修改并落实。规范总体要求避免使用系统产品本身的惯例，让用户混淆自定义对象和系统对象或关键词。例如，存储过程不要以sp_或xp_开头，因为SQLSERVER的系统存储过程以sp_开头，扩展存储过程以xp_开头。不要使用空白符号、运算符号、中文字、关键词来命名对象。名称不宜过于简略，要让对象的用途直观易懂，但也不宜过长，造成使用不方便。不用为数据表内字段名称加上数据类型的缩写。名称中最好不要包括中划线。禁止使用[拼音]+[英语]的方式来命名数据库对象或变量。数据库对象命名规范我们约定，数据库对象包括表、视图（查询）、存储过程（参数查询）、函数、约束。对象名字由前缀和实际名字组成，长度不超过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_CorpID变量命名规范数据列参数命名格式为@+[列名称]。示例：@EmployeeID@employee_id非数据列参数在参数无法跟列名称进行关联时，使用能够反映该参数功能的英文单词或单词组合,采用Pascal样式命名。示例：@WorkType@work_type数据库设计规范好的数据库架构设计对系统运行的性能起着很大的作用，所以要在开始时就要引起重视。为了保证数据库设计的高效必须安排时间对设计结果进行评审，这一环节必不可少。选择有效的设计工具数据库设计工具：PowerDesigner、ERStudio、Rose、MicrosoftVisio。项目开始前要确定使用哪种设计工具。(另有开发插件:RedGate系列(SQLPrompt))选择的工具要便于讨论便入生成脚本导入数据库。设计通过后要形成文档，并且这个结构设计文档要存档，签入VSS基线库中。在进行数据库设计时，应随时进行数据字典的维护。(字段要求写说明)表的设计表设计在数据库设计中占据有十分重要的地位。表是实际存储数据的对象。除了要注重表结构设计，字段的设计之外还要注意表之间关系的设计。遵守范式要求通常，合理的规范化会最小化数据异常和减少数据的冗余。为了更新数据的正确与快速，在设计的初始阶段多采用三范式设计数据库表。第一范式强调的是列的原子性，即列不能够再分成其他几列。第二范式包含两层意思，一是表必须有一个主键；二是非主键列必须完全依赖于主键，且不能只依赖于主键的一部分。(尽量少使用复合主键)第三范式需要确保数据表中的所有非主键列直接与主键列相关，而不能直接依赖于非主键列。字段设计尽量避免可为空的列。虽然在个别情况下，允许空值可能是有用的，但是应尽量少用。这是因为需要对它们进行特殊处理，从而会增加数据操作的复杂性和增加CPU额外的逻辑判断。很多情况下可以考虑用默认值0或空字符串('')来代替NULL值。所以字段应该有NOTNULL的限制。Unicode的选择。nvarchar和nchar相应比varchar和char要占用更多的存储空间。设计的原则是：如果确保存储的内容只是纯英文和数字，用char/varchar。如果含有中文字符或其它多国语言，用nchar/nvarchar。字段长度要精确，遵守“必须、够用”的原则。精确的长度设计既能完整的描述数据，又可以节省存储空间。积小成大，当数据表中的数据有很多记录的时候，这种存储空间的优势就能体现得十分明显。存储空间越紧凑，分配的页面就越少，在同样大小的内存空间中就可以存储更多的页面，这样操作数据的效率就会提高。例如能用char(10)的就不要用char(20),提高存储的利用率和系统性能，但同时也要兼顾扩展性和可移植性。字段类型存储空间补充说明bigint8字节-2^63(-9,223,372,036,854,775,808)到2^63-1(9,223,372,036,854,775,807)int4字节-2^31(-2,147,483,648)到2^31-1(2,147,483,647)smallint2字节-2^15(-32,768)到2^15-1(32,767)tinyint1字节0到255decimal(9,2)5字节decimal(9,2)前面的9为精度，后面为小数位。当精度位于1~9之间时，占5字节。当精度位于10~19之间时，占9字节。注意，numeric在功能上等价于decimal。decimal(19,2)9字节money8字节-922,337,203,685,477.5808到922,337,203,685,477.5807smallmoney4字节-214,748.3648到214,748.3647datetime8字节精确到3.33毫秒。例如：2014-03-0717:25:39.450存储范围：1753年1月1日到9999年12月31日smalldatetime4字节精确到分钟，例如：2014-03-0717:24:00存储范围是：1900年1月1日到2079年6月6日uniqueidentifier16字节uniqueidentifier数据类型可存储16字节的二进制值，其作用与全局唯一标识符(GUID)一样。(CHAR(36))bit1字节取值范围：0或1。char(n)N字节varchar(n)实际存储的每个字符占1字节nchar(n)2xN字节nvarchar(n)实际存储的每个字符占2字节在存储空间一样的情况下，字符串数据类型需要字符串匹配操作，这通常比整数匹配操作的开销要大。所以尽量选择整数作为字段类型。适当的合理的冗余降低范式标准的一个重要原因是为了在检索数据时少连接表从而提供一个性能优势。或是预先汇总计算结果并存放起来，或是将相同字段内容一式多份地放在多个表中，这样数据的冗余会增加开发人员的工作量和业务判断。(最好是对有冗余的字段要另外用文档统一说明)完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。数据库设计阶段，对必要的冗余处理可以事先安排设计，如果在代码实现阶段发现一些必要的冗余字段可以及早提出来考虑。注意大类型的字段设计如果设计过程中发现表中存在大类型(可存储2G)的字段时，要慎重考虑，因为这样的字段会造成单一数据页存放不了几条记录。而过多的页面也会在查询扫描时带来性能影响。一般的做法是将XML、IMAGE、VARCHAR(MAX)、NVARCHAR(MAX)或TEXT类型的字段切割到另外的数据表，而后与主数据表一对一连接。因为这些大型数据访问缓慢，修改时可能造成记录锁定较久。且在大多数的使用状态下，查询一般字段内容时可能根本用不到这些字段。这些列的存在会增加表的页面数，不分割出去容易会影响其它字段的修改和查询。VARCHAR(MAX)、NVARCHAR(MAX)字段如果实际长度在8000以下，这个值将被作为常规的变长数据类型来对待，如果超过8000个字节，SQLServer将该值作为TEXT来存储处理。如果该表数据量比较大时，一定要考虑大字段分离设计原则。少用TEXT和IMAGE，二进制字段的读写是比较慢的。表关系和约束设计正确处理表间关系。一对多、一对一、多对多等关系。主外键关系是保证数据完整性的一个重要机制。维护数据的正确性。尽量采用提供的约束，如主外键、检查、默认值、不可NULL等。尽可能不要通过程序或存储过程、触发器等机制来运行，毕竟SQLSERVER约束是在内部以优化过的二进制程序代码来实现的，而其它方式效率当然不如直接设置的约束高。还有，能够确定具有唯一值的字段上尽量加上唯一性约束。一些约束在客户端判断的确是可以减少服务器的资源，但是不能完全保证数据的错误产生。而且用数据库使用域和参照完整性有时候还能帮助优化器减少查询执行时间。域和参照完整性帮助优化器分析有效的数据值而不需要物理访问数据，这减少了查询时间。主键设计所有的表必须设置主键。主键跟聚焦索引没有什么关系,但主键必须要有索引。主键的选择原则：字段值唯一。不可NULL。字段大小尽量最小。字段值不常变更。不建议用复合主键。主健值过大会影响外健数据表的大小。如果主键是聚集索引，由于所有非聚集索引都会存储聚集索引的键值，所以主键值过大，还将导致其他索引结构的效率不佳（页面数）。主键关乎着数据的正确性与完整性。而聚焦索引是从数据的运行效率出发。虽然主键跟聚集索引是两回事，但基于主键的上述特性，所以主键往往适合作为表的聚集索引，这也是微软的默认做法。但一些没有意义的ID做聚集索引的意义不大，这时候需要在创建表的时候给主键指定为唯一的非聚集索引。--主键约束(非聚集索引)：ALTERTABLE[dbo].[TCustomer]ADDCONSTRAINTPK_TCustomerPRIMARYKEYNONCLUSTERED(ID);选择GUID做为主键时在系统对接、移值和代码编写下都提供了很大的方便，但它是建立在牺牲性能的基础上。在实际运用中，如果对于用36字符的GUID当作主键时，应当注意的问题如下：GUID是无序的，所以不适合用来做聚集索引。否则会引起频繁的页面移动而产生大量的碎片。GUID类型的存储可以由char(36)改为uniqueidentifier类型（16个字节），以节省存储空间。对于有关联的表之间，考虑程序方便可用使用GUID做为主键，但对于独立的表，还是以INT类型的字段做为主键来设计。所以设计阶段要分清哪些必须用GUID来做主键。3.3.2外键设计外键的存在会在处理数据时带来麻烦，但实际上这点恰恰是它的好处。外键的存在就最高效的一致性维护方法。所以在表设计时要考虑主外键的设计。如果决定使用外键约束，那么所有人必须遵守严格执行。外键是最高效的一致性维护方法，数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。3.3.3检查约束约束除了主外键约束、唯一性约束和默认值约束外，还有一类叫检查约束。检查约束是一个识别SQLServer表中每行可接受的列值的规则，检查约束帮助实施域的完整性，域完整性定义了数据库表中列的有效值，检查约束可以验证单列的域完整性，也可以验证多列的域完整性，在单个列上可以有多个检查约束，如果插入或更新的数据违反了检查约束，数据库引擎将暂时停止INSERT和UPDATE操作。CREATETABLEdbo.TEmployee(IDINT,CodeVARCHAR(20),SexCHAR(1)CONSTRAINTText_Sex_CKCHECK(Sex='F'ORSex='M'),--Sex列创建相应的约束，其值只能是'F'或'M'值。ExperienceINTCONSTRAINTText_Experience_CKCHECK(Experience>=0)--Experience列创建相应的约束，其值必须>=0);索引的设计索引是一把双刃剑，它通常可以加快数据检索数据的同时，往往又会带来额外的资源开销（在insert、update和delete使用时）。有时候这个开销代价甚至超过了查询优化带来的好处。所以，索引的创建是门艺术，要在工作中不断的积累经验和不断的总结。一般来说，建立索引要看数据使用的方式，也就是说那些访问数据的SQL语句经常使用，针对这些经常使用的SQL语句创建有效的索引还是值得的，但过多的索引又是对于OLTP（在线事务）数据库是不利的。聚集索引和非聚集索引每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。聚集索引和数据是混为一体的，而非聚集索引是与数据独立分开的。其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。　　我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。　　如果您认识某个字，您可以快速地从自典中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字（非聚集索引查找），然后根据这个字后的页码直接翻到某页来找到您要找的字（书签查找）。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。--聚集索引查找，没有书签查找开销SELECT*FROM[dbo].[TOrder]WHEREOrderID=1ORDERBYOrderID;--非聚集索引查找SELECTUserID,OrderIDFROM[dbo].[TOrder]WHEREUserID=1ORDERBYUserID;--非聚集索引查找+书签查找SELECTUserID,OrderID,OrderPriceFROM[dbo].[TOrder]WHEREUserID=1ORDERBYUserID;索引的初始创建原则如果处在数据库项目的开始，而且不确定如何对索引建模，可以使用不加思考或默认索引模式作为开始。一旦能够根据实际事务信息重新评估数据库后，再调整索引。所以在系统的初始上线阶段一般只考虑创建最少的、最必要的索引。1.所有表要有聚集索引，如果没有合适的字段，那么暂时在主键上创建聚集索引。2.所有外键上创建索引。3.可预知的用来频繁查找的字段上创建索引。4.小表可以不需要特意去创建索引。有主键就好。索引的注意事项一个经常插入更新的表不要加太多索引，因为索引影响插入和更新的速度。所有非聚集索引包含聚集索引键值，创建非聚集索引时不要再包含进来。如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。唯一索引除了可以保证数据的正确性外还可能帮助优化器生成更高效的执行计划。因为在唯一索引中每行都是唯一的，一旦找到一行，SQLServer不必进一步查找其他匹配的行。索引不只是带来查询优化，对于更新操作，索引有时候优化查询带来的好处会超过索引维护的开销。所以索引有某些情况下会缩短整个数据更新的时间。因为有时候，表扫描带来的开销会远大于更新操作本身的开销。（先查找后更新）尽可能地选择那些小数据类型的列来创建索引，大的索引键值增加了索引页面的数量，从而增加了索引所需要的内存和磁盘活动数量。经常有范围查询（between,>,<，>=,<=）或用来作条件返回很多列和orderby、groupby发生的列，可考虑建立聚集索引；(分区字段是时间类型的话，适合聚集索引)非聚集索引在需要从一个大表上读取少量的行时最有用。当匹配返回的记录数过多时，需要用到的书签查找(键查找)的开销将会变得很大。所以像性别这样的字段不要创建非聚集索引。低选择性的列只能配合其它字段创建复合非聚集索引。多个字段创建组合索引时要尽量使关键查询形成索引覆盖，其第一个列一定是使用最频繁的列；但包含的列不能太多，不能有大类型的字段。缺乏合适的索引也是造成阻塞、死锁的原因。频繁更新的列不适合创建聚集索引。主键就是聚集索引，极端错误的，是对聚集索引的一种浪费。虽然SQLSERVER默认是在主键上建立聚集索引的。显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。（尤其是分区表，适合时间做聚集索引）索引的后期维护工作索引创建后不就是完事了的，一定要定期观察索引在实际工作环境中的使用情况。及时阻止索引对系统带来的负面影响。总的来说应该考虑如下几点：去掉使用率低的索引。合理的改善索引，使索引更有效的被利用到。创建缺失的必要的索引。考虑索引碎片的问题。索引碎片率过大时，查询得不到优化。由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那么扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。如果碎片小于10%~20%，碎片不太可能会成为问题，如果索引碎片在20%~40%，碎片可能成为问题，但是可以通过索引重组来消除索引解决，大规模的碎片(当碎片大于40%)，可能要求索引重建。--查看某个表的碎片情况(整理数据的碎片，是整理聚集索引的碎片）--结果看LogicalFramentation字段DBCCSHOWCONTIG('[dbo].[TLog]')WITHFAST,TABLERESULTS,ALL_INDEXES,NO_INFOMSGS;总之，索引的后期跟踪是不断持续的过程。为了搭建高性能的系统环境，就必须定期有效的跟踪索引。物理存储设计除了重视逻辑对象的设计，还需要考虑数据库的物理设计。在并发要求很高、并发用户数很多的情况下，这一设计对数据库的性能起到十分关键的作用。数据库物理文件一般不要存放在C盘，因为系统重装对C盘破坏最大。日志文件另外存放查询数据库的页，可以看到，由于页的ID不连续，所以数据文件内部的读写是随机的。而日志文件的读写是顺序的，所以两者放在同一个硬盘上，会造成硬盘驱动器一会随机，一会顺序，效率会比较低。将数据文件和日志分离存储在不同的物理硬盘上。这样的好处是确保数据的安全，避免单点失效。二是确保数据库的性能。同样备份文件也在不同的磁盘上。存储空间的设计正确评估和测算数据库的物理空间需求。因为数据库采用预先分配存储空间的方法。存储空间的分配操作是一个非常消耗资源的操作。所以设计人员需要评估数据空间的可能增长率，将数据库的空间增长方式设置为恰当好处，这样就可以在空间和效率之间取得均衡。设计要考虑的内容有：数据库文件和日志文件初始值的设计。数据库文件和日志文件以多大的比例增长。（不要用默认的1M或10%）要设置成按固定大小增长，这样就能避免一次增长太多或者太少所带来的不必要的影响。建议对比较小的数据库，设置一次增长50MB到100MB。对大的数据库，设置一次增长200MB到800MB。对于生产数据库，推荐的设置是开启数据库自动增长和不限制大小，以防数据库空间用尽导致应用程序失败。在系统一段时间稳定后，可以采取日志备份的机制使得数据库日志文件大小固定下来，不再持续增长。事务日志备份可以截断日志，在检查点发生时会清空日志，这样会在已有的空间内重新记录日志，而不用分配新的空间。分配空间和压缩空间都很带来很大的资源开销，所以尽量避免数据库进行这两个操作。比如对日志文件截断后不要使用收缩空间的操作，如果一定要收缩那么收缩到一个合适的值，这样避免日志文件重新分配空间。(不要收缩到最小空间，比如1M)不要开启数据库的自动关闭和自动收缩选项。T-SQL编码规范在设计确定的情况下，编码的质量几乎决定了整个系统的质量。编码阶段首先是需要所有程序员有性能意识，也就是在实现功能同时有考虑性能的思想。编写规范的SQL语句不但利于阅读，而且被数据库重复使用的几率也较大，执行效率相对较高，编写的好的SQL与编写的差的SQL在执行性能上可能会差几倍甚至几千几万倍，因此养成好的SQL编写规范对于提高项目质量及提高开发人员自身素质有着潜在的极大的影响。书写基本规范大小写规则。为了最大限度实现SQL的共享，要求书写SQL语句时大小写要一致，（比如保留关键字、谓词和系统函数一律大写）这样做的好处有两点：一是为了阅读方便。二是不统一的语句由于写法不完全相同，数据库会理解为4条不同的语句从而导致重复编译，降低了性能。系统对象（系统存储过程、视图、表，系统字段），按照系统定义的大小写执行（数据库里怎么定义就怎么引用）；数据库对象名称，按照实际定义的大小写执行（数据库里怎么定义就怎么引用）养成注释的好习惯。存储过程、函数、视图、触发器等对象不仅要求创建时加上必要的注释，而且在以后修改的过程中也应该有注释。注释最好以英文为主，尽可能做到简洁而描述清晰。另外表也可以加上注释说明。有结束符。每一个完整的T-SQL语句都要以分号结束。据说在以后的数据库版本里面会强制要求。现在的版本，在公用表达式的编写中，就有这个限制。所有的赋值语句要求变量与运算符之间要有空格。如：v_count=v_count+1;，并保持适当的对齐。引用对象时带上架构名。这也是比较推荐的写法。默认的架构名为dbo。在创建物化视图是就深有体会。以内缩来标示IF、WHILE、BEGINEND、TRYCACHE等程序代码区域。在SQL代码快中尽量使用BEGIN…END语句块，提高代码可阅读性。在多表连接时，尽量用表别名+字段的格式来返回列。换行规则。BEGIN/END独占一行；FROM子句独占一行；WHERE子句独占一行；GROUPBY子句独占一行；ORDERBY子句独占一行；单独的LEFTJOIN和INNERJOIN独占一行；如果一行写不完换行时，需要确保每行逻辑完整性。使用可搜索参数（where使用原则）建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，索引的选择和使用方法是SQLSERVER的优化器自动作的选择，而它选择的根据是查询语句的条件以及相关表的统计信息，这就要求我们在写SQL语句的时候尽量使得优化器可以使用索引。为了使得优化器能高效使用索引，写语句的时候应该注意下面四点：不要对索引字段进行运算，而要想办法做变换。不要对索引字段进行格式转换。不要对索引字段使用函数。不要对索引字段进行多字段连接。所以在where条件语句中有时候一些不规范的写法会造成索引失效。右边的写法要优于左边，因为右边可能会使得索引，而左列的写法是用不上索引的。（同样适用于ON条件中）索引扫描索引扫描或索引查找WHEREYEAR(OrderDate)=2001ANDMONTH(OrderDate)=7（对条件字段使用函数或用户自定义函数）WHEREOrderDate>='2001-07-01'ANDOrderDate<'2001-08-01'WHEREDATEDIFF(day,OrderDate,GETDATE())>5WHEREOrderDate>=DATEADD(DAY,-5,GETDATE())WHERELEFT(AccountName,3)='fan'WHEREAccountNameLIKE'fan%'WHEREid+9>@idWHEREid>@id–9WHERECodeLIKE'%Core%'WHERECodeLIKE'Core%'NOTIN(SELECTIDFROM)LEFTJOINbWHEREb.IDISNULL或NOTEXISTS(SELECT*FROMb)WHEREIDIN(2,3,4,5,6)WHEREID>=2ANDID<=6WHEREFirstName+''+LastName='fanyou';WHEREFirstName='fan'ANDLastName='you'WHERE[Status]<>10WHERE[Status]<10AND[Status]>10NOTIN、LEFTJOIN、NOTEXISTS和IN、INNERJOIN、EXISTS的效率问题要具体情况具体分析。一般情况下推荐使用相关子查询（EXISTS）和连接的方式。SQLServer从左到右处理表，这个在技术内幕上有。而where语句中最能快速筛选数据的列应该放在最前面，也就是最接近where子句的地方。但在SQLSERVER2005后来的版本中，优化器会帮你自动优化的。少用触发器和禁用游标触发器触发器在时间久远后不易维护,触发器不会立即让开发人员看到。而且触发器内容太过复杂，还会存在潜在的性能瓶颈。所以我们统一规定不允许使用触发器，应使用其它办法来代替触发器。对之前已经存在的触发器也应该抽时间去替换它。项目负责人还需要检查是否产生了新的触发器并及时纠正。游标SQLServer适合在数据集（多行）上进行操作，游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。联合查询尽可能使用UNIONALLUNION操作依次执行所有的SELECT语句，将所有的结果集合并为一个结果集。将对结果集进行排序，并过滤掉重复的记录。可见联合查询的效率很低的，除非在必要的情况下才使用。如果允许结果集存在重复，或预知结果集根本不可能重复时一定要用UNIONALL来代替。尽可能避免的地方下面这些操作在使用前，可以重新思考下业务和检查一下逻辑，看是否可以避免。DISTINCT关键字如果SELECT语句中查询的字段很多，则使用该关键字段反而会大大降低查询效率。因为查询字段很多时，如果使用筛选重复关键字，数据库引擎需要花费大量的时间对所有字段进行比较，过滤掉重复的记录，因此影响了查询效率。OrderByOrderBy等语句尽量是查询结果需要才使用，因为太忙都需要做额外的计算。不管你使用TOP返回少量记录，但OrderBy都会事先排完序的。嵌套视图嵌套视图是当一个视图调用另一个视图时，另一个视图调用更多的视图。这可能导致非常容易混淆的代码，因为视图掩盖了被执行的操作。虽然查询可能非常简单，执行计划和随后SQL引擎的操作可能非常复杂并且代价很高。同样，嵌套的用户自定义函数也是如此。嵌套自定义函数注意表值函数分内联表值函数和多语句表值函数。多语句表值函数是用表变量返回的，对此用执行计划查看时，优化器不能够很好地估计执行用户定义函数的成本（不适合返回大量数据行）。所以必须引起注意，有时候引用的自定义函数可以隐藏性能问题，所以应该分析内部的语句。避免返回和使用多余的数据返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节，如果返回不需要的数据，就会增加服务器、网络和客户端的压力，其害处是显而易见的，避免这类事件需要注意：横向来看，不要写SELECT*的语句，而是选择你需要的字段。纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。通用视图带来的干扰比如可能只要用到TProduct表就可以了，但却用VProduct视图，而VProduct视图可能存在更多的表连接，甚至带来了不必要的性能问题。返回多余的列返回多余的列可能会造成书签查找操作，本来有时候非聚集索引能解决的，还额外的使用了书签查找。更不要允许出现SELECT*这样的语句。但在相关子查询中使用EXISTS(SELECT*)可以放心使用*。因为EXISTS只关心行是否存在，不关心特定属性，优化器将会忽略子查询中的SELECT列表。SETNOCOUNTON存储过程内部用SETNOCOUNTON;否则每次执行完就会向客户端发送消息，禁用它们能够减少网络流量。操作符优化IN和EXISTS性能有外表和内表区分的，但是在大数据量的表中推荐用EXISTS代替IN。NOTIN不走索引的是绝对不能用的，可以用NOTEXISTS代替。ISNULL或ISNOTNULL操作索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可，例如>‘’<>操作符（不等于）不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。用其它相同功能的操作运算代替，如a<>0改为a>0ORa<0同样a<>’’改为a>‘’ORa<‘’高版本的数据库引擎可能会对上面的问题进行自动优化，但养成好的习惯也是必要的。数据库事务处理原则事务过程中可能包含查询语句和修改语句，对于查询语句所申请的共享锁会在查询结束时释放，而对于修改语句申请的独占锁会持续到事务结束。所以你需要认真规划事务：保持事务范围尽可能地小。不必要的语句尽量要拿到事务之外。尽早提交事务。所以事务涉及到的SQL语句不能过于复杂。避免事务不能结束。使用SETXACT_ABORTON确保事务出现错误时中止或回滚。消除读写阻塞。比如使用脏读（表名后加WITH(NOLOCK)）。(减少锁开销)如果不是重要的、特别敏感的数据，允许脏读可以避免一定程度的阻塞，加快查询速度。事务操作过程尽量要按同一顺序访问表对象。提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。尽量不要指定锁类型和索引，SQLSERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQLSERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。最少次数的访问表尽量少做重复的工作，最少次数的访问表。如果可以只访问一次表，就没有必要再次访问表。有的是存储过程内部语句太多，写在后面没有留意的问题。所以需要审查自己写的存储过程。UPDATEdbo.TEMPLOYEESETFNAME='HAIWER'WHEREEMP_ID='VPA30890F';UPDATEdbo.TEMPLOYEESETLNAME='YANG'WHEREEMP_ID='VPA30890F';使用公用表达式CTE，可以多次引用，从而减少基础表的访问次数。使用@@ROWCOUNT判断影响的表行数从而避免表多次返问。避免隐含的数据类型转换--Code本来是NVARCHAR(36)类型DECLARE@codeVARCHAR(36);SET@code='0D32C649-A584-4629-8EE3-DDF26A61F9C1';SELECT*FROMdbo.TTable1WHEREID=@code;在执行计划中看到，导致需要额外的操作对值进行转换之后才能用于比较。在Unicode字符前面使用N前缀，避免引起数据的不一致。DECLARE@codeNVARCHAR(36);SET@code=N'0D32C649-A584-4629-8EE3-DDF26A61F9C1';SELECT*FROMdbo.TTable1WHEREID=@code;所以存储过程或TSQL语句中变量类型的定义要以数据库中表字段设计类型为依据。表变量、临时表和公用表达式的用法1.如果表中的行数在100以下，推荐使用表变量。但是SQL引擎并不能为表变量建立统计信息，当表变量存储过多的数据并且又和其它表进行join时,优化引擎会错误估计表变量的行数，认为少数的几十行记录导致性能往往会很差（所以这也是多语句表值函数不适合返回大表的原因）。而临时表会创建统计数据，通过实际的行数生成执行计划。2.表变量仅仅在当前的批处理中有效，作用域很小(不能跨GO),并且在批处理结束后自动被清除。表变量较临时表产生更少的存储过程重编译。表变量的事务仅仅在更新数据时生效，持续时间比临时表短，事务回滚不会影响表变量。而临时表是正常的事务长度，比表变量持续时间长。局部临时表只在当前会话中有效，这也包括嵌套的存储过程。总之，表变量使用较少的日志和加锁资源，在存储过程内部，临时表属于中间缓存数据表需要编译执行计划，而表变量不需要重新编译。3.表变量也可以创建索引。--表变量创建时就要定义为索引或主键。因为一旦建立表变量后就无法对其进行DDL语句操作。DECLARE@MyTableVariableTABLE(RowIDINTPRIMARYKEYCLUSTERED);--表变量的列上创建唯一约束以及如何建立复合索引。DECLARE@tempTABLE(RowIDINTNOTNULL,ColAINTNOTNULL,ColBCHAR(1)UNIQUE,PRIMARYKEYCLUSTERED(RowID,ColA));INSERTINTO@MyTableVariableVALUES(1);INSERTINTO@MyTableVariableVALUES(2);SELECT*FROM@MyTableVariableWHERERowID=1;4.一般来说，表变量和CTE放内存，临时表放硬盘，但证明表变量也会放硬盘的。也会在硬盘创建对象。相对而言临时表主要是多了I/O时间，但少了对内存资源的占用。数据量较大的时候，由于对内存资源的消耗较少，使用临时表比表变量有更好的性能。（数据量大时，表变量产生的执行计划也不准确）。DECLARE@tbTABLE(idINT)SELECT*FROMtempdb.sys.tables--有一个临时表goSELECT*FROMtempdb.sys.tables--没了goCREATETABLE