2023年7月13日发(作者：)

数据库开发规范

（SQL SERVER篇）

拟制： 日期：

审核： 日期：

批准： 日期：

文档编号:

密级: 机密 SQL SERVER数据库开发规范

修订历史记录

日期

2012-10-17

版本

1.0

创建本文档

说明 作者

第 2 页 共 27 页 SQL SERVER数据库开发规范

目录

修订历史记录 ................................................................................................................................... 2

第一章 命名规范............................................................................................................................. 5

1. 命名标志法 ......................................................................................................................... 5

2. 数据库命名 ......................................................................................................................... 5

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

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

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

6. 表的命名 ............................................................................................................................. 6

7. 字段命名 ............................................................................................................................. 6

8. 存储过程命名 ..................................................................................................................... 6

9. 触发器命名, ........................................................................................................................ 7

10. 索引命名 ........................................................................................................................... 7

11. 主键 ................................................................................................................................... 7

12. 外键 ................................................................................................................................... 7

13. 缺省值 ............................................................................................................................... 7

15. 函数的命名 ....................................................................................................................... 7

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

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

18. 数据库保留字 ................................................................................................................... 7

19. 禁止使用空格 ................................................................................................................... 7

第二章 常用数据类型..................................................................................................................... 8

第三章 数据库设计规范 ............................................................................................................... 10

1. 三范式 ............................................................................................................................... 10

2. 适当的冗余 ....................................................................................................................... 10

3. 主键 ................................................................................................................................... 10

4. 索引 ................................................................................................................................... 10

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

第四章 存储过程编写规范 ........................................................................................................... 12

1. 注释 ................................................................................................................................... 12

2. 书写规范 ........................................................................................................................... 13

3. 性能相关 ........................................................................................................................... 13

4. 尽量使用索引 ................................................................................................................... 14

5. 事务和锁 ........................................................................................................................... 15

6. 其他注意事项 ................................................................................................................... 15

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

8. 注意子查询的用法 ........................................................................................................... 15

9. 常用写法 ........................................................................................................................... 17

9.1. XML解析 ............................................................................................................... 17

9.2. 检查表是否有数据 ................................................................................................ 17

9.3. 检查变量是否为空或为’’ ...................................................................................... 17

9.4. 动态SQL ............................................................................................................... 17

9.5. 建表 ........................................................................................................................ 18

9.6. 建索引 .................................................................................................................... 18

第 3 页 共 27 页 SQL SERVER数据库开发规范

9.7. 建用户 .................................................................................................................... 18

9.8. 建全文索引 ............................................................................................................ 18

9.9. 建链接服务器 ........................................................................................................ 19

9.10. SERVICE BROKER ............................................................................................. 19

9.11. 分区 ...................................................................................................................... 20

第五章 POWER DESIGNER使用规范 ..................................................................................... 22

1. PDM ................................................................................................................................... 22

第 4 页 共 27 页 SQL SERVER数据库开发规范

第一章

命名规范

1. 命名标志法

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

Pascal 大小写

将标识符的首字母和后面连接的每个单词的首字母都大写。可以对三字符或更多字符的标识符使用 Pascal 大小写。例如：

BackColor

Camel 大小写

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

backColor

大写

标识符中的所有字母都大写。仅对于由两个或者更少字母组成的标识符使用该约定。例如：

可能还必须大写标识符以维持与现有非托管符号方案的兼容性，在该方案中所有大写字母经常用于枚举和常数值。一般情况下，在使用它们的程序集之外这些字符应当是不可见的。

2. 数据库命名

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

例如：

MFC

MFC53

DataController

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

DatabaseName按数据库命名要求命名

TableName按数据表命名规则命名

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

例如

MFCLOG200301

MFC_log_ClientCheckin20030109

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

DatabaseName按数据库命名要求命名

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

例如 NIDCHyper021

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

DatabaseName按数据库命名要求命名

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

Day要求中间无任何连接符

第 5 页 共 27 页 SQL SERVER数据库开发规范

例如

GatheredLog

MassLog

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 批量插入多条记录

第 6 页 共 27 页 SQL SERVER数据库开发规范

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. 禁止使用空格

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

第 7 页 共 27 页

SQL SERVER数据库开发规范

第二章

常用数据类型

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

数据类型 类型 描 述

int 数据类型可以存储从-

2(-2147483648)到2(2147483 647)之间的int 整型 整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节

从-2^63(-9223372)到bigint 整型

2^63-1(9223372)的整型数据。这种数据类型在数据库里占用8 字节空间

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

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

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

cursor 数据类型是一种特殊的数据类型，它cursor 特殊数据型 包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用

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

唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列

char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，char 字符型

你必须指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型3131 第 8 页 共 27 页 SQL SERVER数据库开发规范

的列宽最大为8000 个字符

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

一样，此数据类型为变长。当定义一列为该数据类型时，你要指定该列的最大长度。 它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度

nvarchar 数据类型用作变长的统一编码字nvarchar 统一编码字符型 符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍

Nvarchar(max) 统一编码字符型

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

最多为231–1(2 147 483 647)字符，一般用Varchar(max) 字符型

来定义XML的入参，每字符1字节＋2字节额外开销

可变长度二进制数据。 n 的取值范围为 1

varbinary(max) 二进制数据类型

至 8,000。 max 指示最大存储大小是

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

第 9 页 共 27 页 SQL SERVER数据库开发规范

第三章

数据库设计规范

1. 三范式

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

•

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

•

非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。

•

一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。

2. 适当的冗余

但是完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

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

3. 主键

主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

4. 索引

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

每个数据表只能建立一个聚集索引，聚集索引决定了数据在表中的物理顺序，同时非聚集索引依赖聚集索引存在。每一个非聚集索引B树的页节点都存有对应的聚集索引键。因此聚集索引和非聚集索引的选择应该遵守如下规范：

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

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

3) 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。同时对数据量比较大的表(>1000行)应结合数据表的使用情况建立非聚集索引以提高数据库查询的反应效率。但是过多的非聚集索引也会影响数据表记录的插入及更新速度，一般要求非聚集索引的个数不超过两位数。因此应该针对各数据表的实际情况设计索引。

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

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

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

第 10 页 共 27 页 SQL SERVER数据库开发规范

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

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

在数据库设计中，我们经常容易混淆主键和聚集索引的关系。因为如果我们建立主键的时候没有特别说明，SQL SERVER会默认在主键上建立聚集索引。同时由于聚集索引同时也是唯一索引，而且主键一般为较小的键。所以我们经常将主键作为聚集索引。但是这并不表示主键和聚集索引等同。

第 11 页 共 27 页 SQL SERVER数据库开发规范

第四章

存储过程编写规范

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

1. 注释

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

USE [MFC_HOTEL]

GO

/*------------------------------------

-- 用途：根据用户ID查询辖区场所统计

-- 项目名称：

-- 说明：这里对存储过程进行详细说明

-- 时间：2012-09-24

-- 编写者:

--------------------------------------

-- 修改记录:

-- 编号 修改时间 修改人 修改原因 修改标注

-- 001 2012-10-11 这里说明修改原因 001

------------------------------------

测试语句

EXEC Web_UnitMange_MFC_tb_Unit_GetTreeList @LocationStatus=2

*/

CREATE PROCEDURE [dbo].[Web_UnitMange_MFC_tb_Unit_GetTreeList]

(

@GuildIDXML VARCHAR(MAX)=NULL,

@LocationStatus INT=0 --0-全部;1-已标注;2-未标注

)

AS

BEGIN --存储过程应尽量保持这种缩进风格，增强美观性和可读性

SET NOCOUNT ON --每个存储过程中关闭统计

--这里介绍每个代码块的功能，增强代码可读性

IF ISNULL(@GuildIDXML,'')<>''

BEGIN

EXEC sp_xml_preparedocument @Handle OUTPUT, @GuildIDXML

/*SELECT GuildID INTO #TempGuildID 修改前的代码段注释保留*/

INSERT INTO #TempGuildID --001 这里标注相应修改的位置

SELECT GuildID

FROM OPENXML(@Handle, N'/ROOT/ROW') WITH (GuildID int)

EXEC sp_xml_removedocument @Handle

END

END

第 12 页 共 27 页 SQL SERVER数据库开发规范

2. 书写规范

数据库服务器端的触发器和存储过程是一类特殊的文本，为方便开发和维护，提高代码的易读性和可维护性。规范建议按照分级缩进格式编写该文本。

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

IF 1<>1

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

END

ELSE

RETURN

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

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

,U.[UnitID]

,U.[AreaCode]

FROM [MFC_HOTEL].[dbo].[MFC_tb_Unit] U WITH(NOLOCK)

INNER JOIN MFC__tb_Area A WITH(NOLOCK)

ON de = de --JOIN条件缩进增强层次感

WHERE ve=0

--FROM,JOIN,WHERE对齐

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

3) 在多表关联时，列名前需要加上别名（或表名），表名前加Owner（dbo）。如果涉及到跨数据库，就需要加上数据库名称。

例如：t；存储过程也一样；

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) 限制在GROUP BY子句中使用表达式；

4) 慎用游标；

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

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

7) 慎用DISTINCT关键字；

8) 慎用OR关键字，可以用UNION ALL替代；

9) 除非必要，尽量用UNION ALL而非UNION

10) 使用EXISTS(SELECT 1)替count(*)来判断是否存在记录；

11) SET NOCOUNT ON 语句

把 SET NOCOUNT ON 语句放到存储过程和触发器中，作为第一句执行语句。例如：

第 13 页 共 27 页 SQL SERVER数据库开发规范

CREATE PROCEDURE [dbo].[UP_GetOrgChildren]

AS

BEGIN

SET 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*2

SELECT ID FROM T WHERE NUM/2=NUM1

如果NUM有索引应改为:

SELECT ID FROM T WHERE NUM=NUM1*2

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

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

日期字段的例子：

WHERE CONVERT(VARCHAR(10),日期字段,120)='2008-08-15'

应该改为

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

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

日期查询的例子：

WHERE LEFT(NAME, 3)='ABC' 或者 WHERE SUBSTRING(NAME,1, 3)='ABC'

应改为:

WHERE NAME LIKE 'ABC%'

日期查询的例子：

WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0

应改为:WHERE 日期>='2005-11-30' AND 日期<'2005-12-1'

WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0

应改为:WHERE 日期<'2005-11-30'

WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0

应改为:WHERE 日期<'2005-12-01'

WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0

应改为:WHERE 日期>='2005-12-01'

WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0

应改为:WHERE 日期>='2005-11-30'

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

例如：

第 14 页 共 27 页 SQL SERVER数据库开发规范

WHERE FAME+'.'+LNAME='H.Y'

应改为:

WHERE FNAME='H' AND LNAME='Y'

7) Like的使用

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

5. 事务和锁

事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：

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

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

2) 在存储过程，触发器，以及SQL 簇中，尽可能按照相同的循序来访问相关的表。这样可以减少死锁的机会；

3) 事务尽可能短

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

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

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

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

6. 其他注意事项

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

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

3) 合理对大表进行分区

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

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

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

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

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

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

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

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

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

6) 临时表使用CREATE TABLE + INSERT INTO的方式

8. 注意子查询的用法

子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE

第 15 页 共 27 页 SQL SERVER数据库开发规范

语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫做相关子查询。相关子查询可以用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

可以改写成

SELECT BEA.[AddressID]

,BEA.[AddressTypeID]

FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK)

LEFT JOIN [AdventureWorks2012].[Person].[BusinessEntity] BE WITH(NOLOCK)

ON ssEntityID = ssEntityID

WHERE ssEntityID IS NULL

FROM [AdventureWorks2012].[Person].[BusinessEntity] WITH(NOLOCK))

2) 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。

SELECT BEA.[AddressID]

,BEA.[AddressTypeID]

FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK)

WHERE BusinessEntityID IN (SELECT BusinessEntityID

可以改写成：

SELECT BEA.[AddressID]

,BEA.[AddressTypeID]

FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK)

INNER JOIN [AdventureWorks2012].[Person].[BusinessEntity] BE WITH(NOLOCK)

ON ssEntityID = ssEntityID

FROM [AdventureWorks2012].[Person].[BusinessEntity] WITH(NOLOCK))

3) 不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS

SELECT BEA.[AddressID]

,BEA.[AddressTypeID]

FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK)

WHERE (SELECT COUNT(*) FROM [AdventureWorks2012].[Person].[BusinessEntity]

WITH(NOLOCK))=0

可以改写成：

SELECT BEA.[AddressID]

,BEA.[AddressTypeID]

FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK)

LEFT JOIN [AdventureWorks2012].[Person].[BusinessEntity] BE WITH(NOLOCK)

第 16 页 共 27 页 SQL SERVER数据库开发规范

ON ssEntityID = ssEntityID

WHERE ssEntityID IS NULL

9. 常用写法

9.1. XML解析

CREATE TABLE #Temp

(

FieldName nvarchar(50),

FieldValue nvarchar(256),

Memo nvarchar(100)

)

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @Xml

INSERT INTO #Temp(FieldName,FieldValue,Memo)

SELECT FieldName,

FieldValue,

Memo

FROM OPENXML(@docHandle, N'/ROOT/ROW')

WITH

(

)

FieldName nvarchar(50),

FieldValue nvarchar(256),

Memo nvarchar(100)

EXEC sp_xml_removedocument @docHandle

9.2. 检查表是否有数据

IF EXISTS(SELECT 1 FROM #Temp)

9.3. 检查变量是否为空或为’’

IF ISNULL(@Input,'')<>''

9.4. 动态SQL

不带输出参数值的写法

DECLARE @SQL NVARCHAR(MAX),@Input INT

SET @Input=1

SET @SQL=N'

SELECT UnitCode

FROM _tb_Unit WITH(NOLOCK)

WHERE UnitID='+CONVERT(NVARCHAR(8),@Input)

EXEC(@SQL)

带输出参数值的写法

DECLARE @SQL NVARCHAR(MAX),@Input INT,@Output NVARCHAR(20)

SET @Input=1

SET @SQL=N'

SELECT @Output=UnitCode

FROM _tb_Unit WITH(NOLOCK)

WHERE UnitID=@Input

第 17 页 共 27 页 SQL SERVER数据库开发规范

'

EXEC sp_executesql @SQL,N'@Input INT,@Output NVARCHAR(20)

OUTPUT',@Input,@Output OUTPUT

PRINT @Output

9.5. 建表

CREATE TABLE [dbo].[NB_re_RoleDepartment](

[RoleID] [int] NOT NULL,

[DepartmentID] [int] NOT NULL,

CONSTRAINT [PK_NB_RE_ROLEDEPARTMENT] PRIMARY KEY CLUSTERED

( [RoleID] ASC,

[DepartmentID] ASC

)WITH (IGNORE_DUP_KEY = OFF,DATA_COMPRESSION = PAGE) ON [PRIMARY]

) ON [PRIMARY]

这其中DATA_COMPRESSION = PAGE页压缩选项在SQL SERVER2008或之后的版本才能使用

9.6. 建索引

CREATE NONCLUSTERED INDEX [IX_MFC_tb_Process_UserID] ON [dbo].[MFC_tb_Process]

([UserID] DESC)WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]

这其中DATA_COMPRESSION = PAGE页压缩选项在SQL SERVER2008或之后的版本才能使用

9.7. 建用户

--创建数据库用户mfcc

IF NOT EXISTS (SELECT * FROM _principals WHERE name = N'mfcc')

CREATE LOGIN [mfcc] WITH PASSWORD=N'852369', DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[简体中文], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

EXEC _addsrvrolemember @loginame = N'mfcc', @rolename = N'sysadmin'

GO

9.8. 建全文索引

BEGIN TRY -- 屏蔽全文错误

--建全文索引目录

IF NOT EXISTS(SELECT name FROM xt_catalogs WHERE name =

'ChatQQ20121018') -- 全文目录不存在

AND EXISTS (SELECT 1 FROM WHERE name = 'NIR_log_ChatQQ20121018') -- 表存在

BEGIN

CREATE FULLTEXT CATALOG ChatQQ20121018 WITH ACCENT_SENSITIVITY = ON

AUTHORIZATION [dbo]

END;

--建全文索引

IF NOT EXISTS(SELECT 1 FROM xt_indexes WHERE object_id =

object_id('NIR_log_ChatQQ20121018')) -- 全文索引不存在

AND EXISTS (SELECT 1 FROM WHERE name = 'NIR_log_ChatQQ20121018') -- 表存在

AND EXISTS (SELECT 1 FROM xt_catalogs WHERE name =

第 18 页 共 27 页 SQL SERVER数据库开发规范

'ChatQQ20121018')

BEGIN

END

END TRY

BEGIN CATCH

END CATCH

-- 全文目录存在

CREATE FULLTEXT INDEX ON NIR_log_ChatQQ20121018([Content]) KEY INDEX

PK_NIR_log_ChatQQ20121018 ON ChatQQ20121018

SQL SERVER 2008及以后全文索引目录是一个虚拟的概念，不需要制定PATH

9.9. 建链接服务器

-- 增加链接服务器

exec sp_addlinkedserver 'MFC208', ' ', 'SQLOLEDB ', '192.168.9.208nsmc6_5'

--MFC208是链接服务器的数据库逻辑名(别名)

-- 增加链接服务器关联登录用户

exec sp_addlinkedsrvlogin 'MFC208 ', 'false ',null, 'mfcc', '852369'

--MFC208是链接服务器关联到远程的用户mfcc,密码是852369

9.10. SERVICE BROKER

USE MFC

GO

--建立消息类型

CREATE MESSAGE TYPE [XMLMessageType] VALIDATION = WELL_FORMED_XML

GO

--建立约定

CREATE CONTRACT [XMLMessageContract] ([XMLMessageType] SENT BY INITIATOR)

GO

--建立队列

CREATE QUEUE [dbo].[Queue_Argot] WITH STATUS = ON , RETENTION = OFF ON [PRIMARY]

GO

--建立服务

CREATE SERVICE [Service_Argot] ON QUEUE [dbo].[Queue_Argot] ([XMLMessageContract])

GO

--建立表删除新增触发器,并发送消息到队列

CREATE TRIGGER [dbo].[TR_MFC_tb_Argot]

ON [dbo].[MFC_tb_Argot]

FOR INSERT,DELETE

AS

BEGIN

IF @@ROWCOUNT=0

RETURN

SET NOCOUNT ON

-- 将要发送的数据生成xml 数据

DECLARE

@message xml

IF EXISTS ( SELECT 1

第 19 页 共 27 页 SQL SERVER数据库开发规范

FROM INSERTED )

BEGIN

SET @message = ( SELECT Operation = 'INSERTED'

FOR

,ArgotName AS KeyWordID

FROM INSERTED

XML RAW('ROW') ,

ROOT('ROOT')

)

END

IF EXISTS ( SELECT 1

FROM DELETED )

BEGIN

SET @message = ( SELECT Operation = 'DELETED'

,ArgotName AS KeyWordID

FROM DELETED

FOR

XML RAW('ROW') ,

ROOT('ROOT')

)

END

DECLARE @handle uniqueidentifier

BEGIN DIALOG CONVERSATION @handle

FROM SERVICE [Service_Argot]

TO SERVICE N'Service_Argot'

ON CONTRACT XMLMessageContract

WITH

ENCRYPTION = OFF;

SEND

ON CONVERSATION @handle

MESSAGE TYPE XMLMessageType(@message);

-- 消息发出即可, 不需要回复, 因此发出后即可结束会话

--END CONVERSATION @handle

END

9.11. 分区

--创建分区函数

IF NOT EXISTS (SELECT 1 FROM ion_functions WHERE name =

N'MFCPartitionFunction')

CREATE PARTITION FUNCTION [MFCPartitionFunction](bigint) AS RANGE FOR VALUES

(-7378697629483820647, -5534486, -36893488,

-18446744, -3, 18446744, 36893488,

5534480, 7378697629483820641, 8378697629483820641)

第 20 页 共 27 页 SQL SERVER数据库开发规范

GO

--创建分区方案

IF NOT EXISTS (SELECT 1 FROM ion_schemes WHERE name =

N'MFCPartitionScheme')

CREATE PARTITION SCHEME [MFCPartitionScheme] AS PARTITION [MFCPartitionFunction]

TO ([2005P1], [2005P2], [2005P3], [2005P4], [2005P5], [2005P6], [2005P7],

[2005P8], [2005P9], [2005P10], [PRIMARY])

GO

IF NOT EXISTS (SELECT 1 FROM ion_schemes WHERE name =

N'MFCPartitionSchemeInd')

CREATE PARTITION SCHEME [MFCPartitionSchemeInd] AS PARTITION

[MFCPartitionFunction] TO ([PRIMARY], [2005P10], [2005P9], [2005P8], [2005P7],

[2005P6], [2005P5], [2005P4], [2005P3], [2005P2], [2005P1])

GO

第 21 页 共 27 页 SQL SERVER数据库开发规范

第五章

POWER DESIGNER使用规范

本章主要介绍下使用POWER DESIGNER的一些要求和注意事项,不详细阐述POWER

DESIGNER的详细使用教程.

1. PDM

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

表属性需包含如下三要素（标红部分）：

表字段必须包括字段物理名(字段中文名)、字段物理名、字段说明、字段类型四个部分，如下图。

第 22 页 共 27 页 SQL SERVER数据库开发规范

如果字段存在标识列或者计算列则需要在字段详细属性页中设定：

如果字段是标识列，同时种子及增量不是默认的1,1 则在如下的选项页修改：

第 23 页 共 27 页 SQL SERVER数据库开发规范

索引页不需要表名索引的中文名，如果索引时聚集索引或者唯一索引则在后面C,U复选框中标明：

下面的索引字段选项卡选择索引字段及排序方式：

第 24 页 共 27 页 SQL SERVER数据库开发规范

如果索引需修改默认的文件组或者分区架构，在如下的选项卡中修改：我暂时没找到在POWER DESIGNER中添加分区方案和文件组的方法，这里的文件组名不能选择，要直接填写数据库中已有的分区架构和文件组。

表、字段、整个数据库系统说明用如下的白底黑字对话框标明：

第 25 页 共 27 页 SQL SERVER数据库开发规范

同一类型的表需用同一种颜色标识，如下图：

不同类型的数据表可以考虑用不同颜色的Symbol表示，只要整体美观就行。

我们在设计POWER DESIGNER文档时对表之间的关系用外面连接，但是应该将生成的建表语句中的外键语句块删除。外键名用系统自动生成的外键名即可，如下图：

第 26 页 共 27 页 SQL SERVER数据库开发规范

第 27 页 共 27 页