数据库开发规范SQLSERVER篇最新版.docx

数据库开发规范SQLSERVER篇最新版.docx

《数据库开发规范SQLSERVER篇最新版.docx》由会员分享，可在线阅读，更多相关《数据库开发规范SQLSERVER篇最新版.docx（33页珍藏版）》请在冰豆网上搜索。

数据库开发规范SQLSERVER篇最新版

数据库开发规范

（SQLSERVER篇）

拟制：

日期：

审核：

日期：

批准：

日期：

文档编号:

密级:

机密

修订历史记录

日期

版本

说明

作者

2012-10-17

1.0

创建本文档

第一章命名规范

1.命名标志法

使用下面的三种大写标识符约定。

Pascal大小写

将标识符的首字母和后面连接的每个单词的首字母都大写。

可以对三字符或更多字符的标识符使用Pascal大小写。

例如：

BackColor

Camel大小写

标识符的首字母小写，而每个后面连接的单词的首字母都大写。

例如：

backColor

大写

标识符中的所有字母都大写。

仅对于由两个或者更少字母组成的标识符使用该约定。

例如：

System.IO

System.Web.UI

可能还必须大写标识符以维持与现有非托管符号方案的兼容性，在该方案中所有大写字母经常用于枚举和常数值。

一般情况下，在使用它们的程序集之外这些字符应当是不可见的。

2.数据库命名

数据库名要求全部使用Pascal命名法

例如：

MFC

MFC53

DataController

3.数据库月份库、数据表日分库命名规则

DatabaseName按数据库命名要求命名

TableName按数据表命名规则命名

Month,Day要求中间无任何连接符

例如

MFCLOG200301

MFC_log_ClientCheckin20030109

4.分段数据库分库命名规则

DatabaseName按数据库命名要求命名

Segment是分段的编号，要求长度一致并且3位或者以上

例如NIDCHyper021

5.分段分日期数据库分库命名规则

DatabaseName按数据库命名要求命名

Segment是分段的编号，要求长度一致并且3位或者以上

Day要求中间无任何连接符

例如

GatheredLog00120110227

MassLog00320110227

6.表的命名

__

SystemName为表所属的系统名，此处要求采用Pascal命名法

TableType为数据表的类别，此处要求全部使用小写，在我们的库中有如下几种数据表类型：

tb----------数据表,

stat--------统计表,

dict--------字典表,

sys--------系统信息表,

re----------关系表,

log---------日志表

Name为数据库表的名称，此处要求使用Pascal命名法

例如：

MFC_tb_Unit场所信息表

MFC_stat_UnitDailyStatus场所状态日统计表

MFC_re_UserArea用户地区关系表

MFC_log_Customer顾客日志表

MFC_dict_Sex性别字典表

7.字段命名

字段命名统一使用Pascal标志法，单词中间不用下划线。

应尽量使用简短而又能说明字段实际意义的词组组合，为保证不与系统字段重复，应尽量至少使用两个单词。

同样含义的字段应尽量使用已有字段的物理名。

例如：

CertificateCode证件号

CertificateType证件类别

AlertClassName报警类别名

8.存储过程命名

[]__

SystemName是系统名，此处要求使用Pascal命名法，对于跨系统使用的存储过程要求此段，其他非跨系统的存储过程不要求。

FunctionModule为功能模块名，此处要求使用Pascal标志法

TableName为数据库已有表名，命名规则同上面的表命名要求

FunctionName为存储过程的功能说明，此处要求使用Pascal标志法。

常用的功能有：

GetList取多条记录

GetModel去单条记录

GetListByCondition根据Condition条件取单条记录

Add插入或修改单条记录

Delete删除记录

Insert插入单条记录

BatchInsert批量插入多条记录

BatchUpdate批量更新多条记录

Update更新单条记录

例如：

Communication_MFC_re_UnitStatus_GetList

DataAnalysis_NIDC_tb_PersonGroup_Delete

DataAnalysis_MFC_tb_CrimeOnEsc_Add

9.触发器命名,

TR_[_]

如果只是针对单个操作类型的触发器，则要求说明操作类型：

例如：

TR_MFC_tb_Argot

TR_MFC_tb_Argot_Insert

10.索引命名

IX__

例如：

IX_MFC_log_Customer_EndTime

11.主键

PK_。

TableName同表命名规则

例如

PK_MFC_Log_Customer

12.外键

FK__

例如：

FK_MFC_log_Customer_MFC_tb_Unit

13.缺省值

DF__

例如：

DF_MFC_log_Customer_UserName

14.视图的命名用Pascal标志法，和表一致；

_view_

视图的命名除中间用’view’链接以外与表一致

例如：

MFC_view_Strategy

15.函数的命名

采用存储过程同样的命名规则

16.其他数据库对象命名规则

其他数据库对象，比如约定、队列、服务、路由等采用表名相同的命名法。

17.其他数据库可编程性对象命名

其他数据库可编程性对象采用存储过程相同的命名法。

18.数据库保留字

不要使用数据库保留字，给数据对象命名；

19.禁止使用空格

在数据库对象命名时，禁止使用空格。

第二章常用数据类型

下面是我们再数据库设计中常用的几种数据类型：

数据类型

类型

描            述

int

整型

int数据类型可以存储从-231（-2147483648）到231（2147483647）之间的整数。

存储到数据库的几乎所有数值型的数据都可以用这种数据类型。

这种数据类型在数据库里占用4个字节

bigint

整型

从-2^63（-9223372036854775808）到2^63-1（9223372036854775807）的整型数据。

这种数据类型在数据库里占用8字节空间

numeric

精确数值型

numeric数据类型与decimal型相同（要求在存储过程或其他语句中必须表名数据长度及精度）

datetime

日期时间型

datetime数据类型用来表示日期和时间。

这种数据类型存储从1753年1月1日到9999年12月31日间所有的日期和时间数据，精确到三百分之一秒或3.33毫秒

cursor

特殊数据型

cursor数据类型是一种特殊的数据类型，它包含一个对游标的引用。

这种数据类型用在存储过程中，而且创建表时不能用

Uniqueidentifier

特殊数据型

Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。

GUID确实是全局唯一的。

这个数几乎没有机会在另一个系统中被重建。

可以使用NEWID函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列

char

字符型

char数据类型用来存储指定长度的定长非统一编码型的数据。

当定义一列为此类型时，你必须指定列长。

当你总能知道要存储的数据的长度时，此数据类型很有用。

例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。

此数据类型的列宽最大为8000个字符

varchar

字符型

varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。

与char型不一样，此数据类型为变长。

当定义一列为该数据类型时，你要指定该列的最大长度。

它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度

nvarchar

统一编码字符型

nvarchar数据类型用作变长的统一编码字符型数据。

此数据类型能存储4000种字符，使用的字节空间增加了一倍

Nvarchar（max）

统一编码字符型

最多为230–1（1 073 741 823）Unicode字符，占用2×字符数＋2字节的空间

Varchar（max）

字符型

最多为231–1（2 147 483 647）字符，一般用来定义XML的入参，每字符1字节＋2字节额外开销

varbinary（max）

二进制数据类型

可变长度二进制数据。

n的取值范围为1至8,000。

max指示最大存储大小是2^31-1个字节。

存储大小为所输入数据的实际长度+2个字节。

第三章数据库设计规范

1.三范式

数据库设计中应尽可能遵守三范式。

所谓三范式即：

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

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

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

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

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

2.适当的冗余

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

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

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

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

比如一些日志表的历史统计信息，我们可以通过作业定期在数据库负载较小的凌晨8点对数据日志数据进行统计，并建立冗余的统计表记录下来。

3.主键

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

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

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

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

4.索引

索引分为聚集索引和非聚集索引。

每个数据表只能建立一个聚集索引，聚集索引决定了数据在表中的物理顺序，同时非聚集索引依赖聚集索引存在。

每一个非聚集索引B树的页节点都存有对应的聚集索引键。

因此聚集索引和非聚集索引的选择应该遵守如下规范：

1）应尽量选择符合唯一约束的字段建立聚集索引

2）尽量选择占用空间较小的字段建立聚集索引，一般要求聚集索引小于900字节

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

同时对数据量比较大的表（>1000行）应结合数据表的使用情况建立非聚集索引以提高数据库查询的反应效率。

但是过多的非聚集索引也会影响数据表记录的插入及更新速度，一般要求非聚集索引的个数不超过两位数。

因此应该针对各数据表的实际情况设计索引。

4）若某列的值大部分是a，少数是别的值（如b,c,d…），且经常以该列的其它值（如b,c,d…）为查询条件，可以考虑对（如b,c,d…）建立筛选索引。

5）把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面，同一索引中的组成列最好不要超过3列。

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

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

5.主键与聚集索引的关系

在数据库设计中，我们经常容易混淆主键和聚集索引的关系。

因为如果我们建立主键的时候没有特别说明，SQLSERVER会默认在主键上建立聚集索引。

同时由于聚集索引同时也是唯一索引，而且主键一般为较小的键。

所以我们经常将主键作为聚集索引。

但是这并不表示主键和聚集索引等同。

第四章存储过程编写规范

统一和规范的代码书写风格对保证软件的开发质量、提高团队的开发效率以及将来的维护及其扩展都至关重要。

1.注释

为了增强可读性及美观性，在存储过程头部和存储过程中间应尽量按照如下演示的存储过程做好注释。

2.书写规范

数据库服务器端的触发器和存储过程是一类特殊的文本，为方便开发和维护，提高代码的易读性和可维护性。

规范建议按照分级缩进格式编写该文本。

1）编写存储过程时应遵守以下缩进规则，如下示例

IF1<>1

BEGIN--每个IF条件后的程序块缩进

SELECTU.[GuildID]--各字段尽量对其

U.[UnitCode]--每个查询字段要写明表别名或表名

U.[UnitID]

U.[AreaCode]

FROM[MFC_HOTEL].[dbo].[MFC_tb_Unit]UWITH（NOLOCK）

INNERJOINMFC_HOTEL.dbo.MFC_tb_AreaAWITH（NOLOCK）

ONU.AreaCode=A.AreaCode--JOIN条件缩进增强层次感

WHEREA.IsActive=0

--FROM,JOIN,WHERE对齐

END

ELSE

RETURN

2）不要使用SELECT*需要哪些字段，查询哪些字段,尽可能少的返回结果集行的数量。

3）在多表关联时，列名前需要加上别名（或表名），表名前加Owner（dbo）。

如果涉及到跨数据库，就需要加上数据库名称。

例如：

AdventureWorks.dbo.Contact；存储过程也一样；

4）SQL保留字要大写

对SQL的保留字，都需要大写。

例如：

SELECT，UPDATE，INSERT，WHERE，INNERJOIN，AND，OR等。

5）过多使用GOTO语句会使得代码可读性降低

6）查询列表和条件中的字段全部需要指定所属的表，可以使用表名别名简化。

表名别名要简短，但意义要尽量明确，避免使用A、B、C等过于简单的别名。

通常，使用大写的表名作为别名，使用AS关键字指定表或字段的别名。

3.性能相关

1）Where子句尽量避免使用函数；

2）避免在ORDERBY子句中使用表达式；

3）限制在GROUPBY子句中使用表达式；

4）慎用游标；

5）避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’；

6）查询语句一定要有范围的限定，避免全表扫描操作；

7）慎用DISTINCT关键字；

8）慎用OR关键字，可以用UNIONALL替代；

9）除非必要，尽量用UNIONALL而非UNION

10）使用EXISTS（SELECT1）替count（*）来判断是否存在记录；

11）SETNOCOUNTON语句

把SETNOCOUNTON语句放到存储过程和触发器中，作为第一句执行语句。

例如：

CREATEPROCEDURE[dbo].[UP_GetOrgChildren]

AS

BEGIN

SETNOCOUNTON

......

关闭数据库提示输出。

4.尽量使用索引

1）IN/OR子句使用

IN、OR、NOTIN应尽量避免使用，这可能会导致SQLSERVER不使用索引而选择全表扫描，可以索引查找的，可以正常使用。

2）!

=或<>操作符子句使用

!

=或<>操作符应尽量避免使用，可以用索引查找的，可以正常使用。

3）不要对索引字段进行运算

例如：

SELECTIDFROMTWHERENUM/2=100

应改为:

SELECTIDFROMTWHERENUM=100*2

SELECTIDFROMTWHERENUM/2=NUM1

如果NUM有索引应改为:

SELECTIDFROMTWHERENUM=NUM1*2

如果NUM1有索引则不应该改。

4）不要对索引字段进行格式转换

日期字段的例子：

WHERECONVERT（VARCHAR（10）,日期字段,120）='2008-08-15'

应该改为

WHERE日期字段>='2008-08-15'AND日期字段<'2008-08-16'

5）不要对索引字段使用函数

日期查询的例子：

WHERELEFT（NAME,3）='ABC'或者WHERESUBSTRING（NAME,1,3）='ABC'

应改为:

WHERENAMELIKE'ABC%'

日期查询的例子：

WHEREDATEDIFF（DAY,日期,'2005-11-30'）=0

应改为:

WHERE日期>='2005-11-30'AND日期<'2005-12-1'

WHEREDATEDIFF（DAY,日期,'2005-11-30'）>0

应改为:

WHERE日期<'2005-11-30'

WHEREDATEDIFF（DAY,日期,'2005-11-30'）>=0

应改为:

WHERE日期<'2005-12-01'

WHEREDATEDIFF（DAY,日期,'2005-11-30'）<0

应改为:

WHERE日期>='2005-12-01'

WHEREDATEDIFF（DAY,日期,'2005-11-30'）<=0

应改为:

WHERE日期>='2005-11-30'

6）不要对索引字段进行多字段连接

例如：

WHEREFAME+'.'+LNAME='H.Y'

应改为:

WHEREFNAME='H'ANDLNAME='Y'

7）Like的使用

对索引列避免使用like‘%xx’，应该使用like‘xx%’。

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

5.事务和锁

事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。

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

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

1）使用NOLOCK提示查询优化器

在繁忙的系统中，对改善并发问题，是个不错的选择；

2）在存储过程，触发器，以及SQL簇中，尽可能按照相同的循序来访问相关的表。

这样可以减少死锁的机会；

3）事务尽可能短

4）在事务中涉及到数据修改量，尽可能小，提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。

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

6）尽可能低的设置锁，以及隔离的级别。

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

6.其他注意事项

1）在相关表存在的数据库下创建存储过程和函数

2）有设置默认值限制的字段不允许设置为可以为空

3）合理对大表进行分区

4）视图嵌套使用不能超过3层

5）对数据量比较大的日志表，应按日期，ID段分库分表

7.注意临时表和表变量的用法

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

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

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

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

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

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

6）临时表使用CREATETABLE+INSERTINTO的方式

8.注意子查询的用法

子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。

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

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

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

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

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

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

1）NOTIN、NOTEXISTS的相关子查询可以改用LEFTJOIN代替写法。

例如：

SELECTBEA.[AddressID]

BEA.[AddressTypeID]

FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH（NOLOCK）

WHEREBusinessEntityIDNOTIN（SELECTBusinessEntityIDFROM[AdventureWorks2012].[Person].[BusinessEntity]WITH（NOLOCK））

可以改写成

SELECTBEA.[AddressID]

BEA.[AddressTypeID]

FROM[AdventureWorks2012].[Person].[BusinessEntityAddress]BEAWITH（NOLOCK）

LEFTJOIN[AdventureWorks2012].[Person].[BusinessEntity]BEWITH（NOLOCK）

ONBEA.BusinessEntityID=BE