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

有限公司

文档中心

文档编号

产品名称：

产品版本

密级

共15页

Transact-SQL语言编程规范

(仅供内部使用)

修订记录

日期

修订版本

描述

作者

目 录

1

2

3

4

5

排版 ................................................................................................................................. 5

数据库对象命名 ............................................................................................................. 6

数据库设计 ..................................................................................................................... 8

程序效率 ....................................................................................................................... 13

事务 ............................................................................................................................... 15 Transact-SQL语言编程规范

本规范是基于软件编程规范总则的基础上，针对Sybase和MS SQL Server的Transact-SQL语言的编写风格做出统一的规范约束，以提高源程序的执行效率和可维护性。

本规范的内容包括：排版、数据库对象命名、数据库设计、程序效率与事务等。

本规范的示例都以MS SQL Server为背景，采用以下的术语描述：

 规则：编程时必须遵守的原则

 建议：编程时必须加以考虑的原则

 说明：对此规则或建议进行必要的解释。

 示例：对此规则或建议从正、反两方面给出例子。

1

排版

规则1-1：程序块采用缩进风格，缩进的空格数为4个；但凡SQL程序可加容器关键字END的内容都要缩格，他的内容都要左对齐、类似程序中的函数与子程序。

使用SQL Server的Query Analyzer工具编辑程序时，请打开文件菜单的configure对话框，选中Change tabs to space charact 4 spaces复选项。

规则1-2：在SQL语句的编写中，凡是SQL语句的关键字一律大写，如：SELECT、ORDER BY、 GROUP BY、 FROM、 WHERE、 UPDATE、 INSERT INTO、

SET、 BEGIN、 END等。

2 数据库对象命名

规则2-1：表名以字符串tb开头，字段名以字符串fld开头，逗号在每行的最前面。

示例：如下的表命名方式比较直观。

CREATE TABLE tblEmployee

(

fldID INT NOT NULL

,fldName CHAR(20) NOT NULL

,fldBirthday SMALLDATETIME NOT NULL

,fldNation CHAR(10) NOT NULL

)

规则2-2：视图名以字符串vw开头，字段名以字符串fld开头。

示例：如下的视图命名方式比较直观。

CREATE VIEW vwEmployee(fldID,fldName,fldAge)

AS

SELECT fldID,fldName,DATEDIFF(year, fldBirthday, getdate())

FROM tblEmployee

规则2-3：局部临时表名以字符串#tbl开头，全局临时表名以字符串##tbl开头，字段名以字符串fld开头，逗号在每行的最前面。

示例：如下的局部临时表命名方式比较直观。

CREATE TABLE #tblEmployee

(

fldID INT NOT NULL

,fldName CHAR(20) NOT NULL

,fldBirthday SMALLDATETIME NOT NULL

,fldNation CHAR(10) NOT NULL

)

规则2-4：规则名以字符串rl开头，命名必须有明确含义，最好在rl后接所要绑定的域名。

示例：如下的规则命名方式比较直观。

CREATE RULE rlNation

AS

@Nation in ('China','India','England')

规则2-5：用户定义的数据类型以字符串tp开头，命名必须有明确含义，最好在tp后接相关的域名。

示例：如下的用户定义的数据类型命名方式比较直观。 EXEC sp_addtype tpTelephoneNumber, 'VARCHAR(14)', 'NOT NULL'

规则2-6：存储过程名以字符串sp开头，命名必须有明确含义，参数名以@p开头，每个参数一行，逗号在每行参数的最前面。

示例：如下的存储过程定义比较直观。

CREATE PROCEDURE spAddPerson

@pLastName varchar(30)

,@pFirstName varchar(18)

,@pAge tinyint

AS

BEGIN

END

GO

规则2-7：触发器名以字符串tri开头，续以对应表的全名或缩写，后接i、u或d的组合字符串以表示该触发器的操作类型。组合字符串包含i表示是插入操作触发器，包含u表示是修改操作触发器，包含d表示是删除操作触发器。。

示例：如下的触发器定义比较直观。

CREATE TRIGGER triEmployee_id--插入和删除触发器

on tblEmployee

FOR INSERT, DELETE

AS

BEGIN

END

GO

示例：如下的游标定义比较直观。

DECLARE curEmployee CURSOR FOR

SELECT fldAge,fldName,fldNation

FROM tblEmployee

WHERE fldNation = "China"

ORDER BY fldAge

规则2-8： 游标名以字符串cur开头。

3 数据库设计

规则3-1： 数据库描述的一个关系或属性，必须在多个表中以域的形式定义，而这些域的数据类型也是相同的，对于这样的域采用如下方式定义该域的数据类型：针对该关系或属性创建用户自定义的数据类型，并在表定义时使用相应的用户自定义的数据类型。

说明：采用用户自定义的数据类型定义该域的数据类型的目的是便于维护数据库。因为一旦要修改该关系或属性的数据类型时，只要修改对应的用户自定义的数据类型即可。

示例：以B型机Bam数据库描述的电话号码属性为例。

Bam数据库中描述电话号码的域有号段表(tbl_PhoneSeg)的号段起始号码(sSegStartNo)和号段终止号码(sSegEndNo)，ST用户数据表(tbl_STUserData)的用户号码(sDN)，V5ST用户数据表(tbl_V5STUserData)的用户号码(sDN)等等，定义的数据类型都是Varchar(8)。

如果用户要求支持9位电话号码，就要把数据库所有描述电话号码属性的域类型修改为Varchar(9)，一旦有遗漏修改之处，只好等待测试部测试并提交问题了。

这种做法显然不利于维护。

如果采用本规则，就只要修改相应的用户自定义的数据类型即可。

以号段表为例。

EXEC sp_addtype tpTelephoneNumber, 'VARCHAR(8)', 'NOT NULL'

GO

CREATE TABLE neSeg

(

fldiDnSet tinyint NOT NULL

,fldsSegStartNo tpTelephoneNumber

,fldsSegEndNo tpTelephoneNumber

,fldiUserIdxPtr int NOT NULL

)

规则3-2： 如果数据库描述的一个关系或属性，必须在多个表中以域的形式定义，而这些域服从相同的校验约束，对于这样的域采用如下方式定义该域的校验约束：创建规则并把该规则绑定到该域，如果该域采用用户自定义的数据类型，也可以把该规则绑定到相应的用户自定义的数据类型。

说明：采用规则定义该域的校验约束关系的目的是便于维护数据库。因为一旦要修改该关系或属性的校验约束关系时，只要修改该域绑定的规则即可。

示例：以身份证号码为例。

采用如下定义表的方式定义人员信息表：

CREATE TABLE tblPersonInfo

(

fldID TINYINT NOT NULL

,fldName CHAR(20) NOT NULL

,fldIDCardNo CHAR(15) NULL

,fldSex BIT NOT NULL

,CONSTRAINT chkSex CHECK (fldSex LIKE

'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]')

)

原来的身份证号码为15位。 由于目前身份证号码增加到18位，就要修改数据库所有描述身份证号码的校验约束关系，一旦有遗漏修改之处，只好等待测试部测试并提交问题了。这种做法显然不利于维护。

如果采用本规则，就只要修改相应的规则即可。如下。

CREATE RULE rlIDCardNo

AS

@IDCardNo LIKE

'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'

GO

EXEC sp_bindrule rlIDCardNo, 'tblPersonInfo. FldIDCardNo'

针对目前身份证号码增加到18位，只要修改规则rlIDCardNo为如下即可：

CREATE RULE rlIDCardNo

AS

@IDCardNo LIKE

'[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9][0-9,A-X]'

GO

规则3-3：不要在一个表中定义冗余的域。

示例：以B型机Bam数据库的机框描述表(tbl_SMFrameDes)为例。

CREATE TABLE tbl_SMFrameDes

(

fldID int NOT NULL

,fldModuleNum smallint NOT NULL

,fldFrameNo smallint NOT NULL

...

)

其中fldID与(fldModuleNum、fldFrameNo)必须满足如下隐含关系：

fldID=fldModuleNum*100 + fldFrameNo

即fldID与(fldModuleNum、fldFrameNo)互为冗余，如果插入如下数据：

INSERT tbl_SMFrameDes VALUES(1,1,1)

该纪录不满足如上隐含关系，并且能够成功插入该表中，容易引发不可预见的问题。对于这种情况，其实可以采用视图的方式解决。如下：

CREATE TABLE tblSMFrameDes

(

fldID int NOT NULL

...

)

GO

CREATE VIEW vwSMFrameDes(fldID,fldModuleNum,fldFrameNo,...)

AS

SELECT fldID,fldID / 100,fldID % 100

FROM tblSMFrameDes

规则3-4：聚簇索引准则

(1) 大多数表都应有聚簇索引或利用分区来减少对于堆的最后页的竞争，在多事务环境中，对最后一页的加锁严重制约工作效率。

(2) 如果你的环境需很多插入操作，聚簇索引键不应为单调增加值，通常，主键不满足这一准则。

(3) 当键与范围查询的查找参数匹配时，聚簇索引能提供非常好的性能。

(4) 其它好的候选聚簇索引键为Order by和group by子句以及join操作中使用的列。

规则3-5：太多的索引在有利于SELECT操作的同时也减慢了数据修改，应分析每个索引的需求，除去不必要的或很少用的索引。

规则3-6：索引准则

(5) 如果一索引键是唯一的，保证定义索引唯一，则优化器马上知道对键的查找或join仅返回一个纪录。

(6) 如果数据库设计使用引用完整性，引用列必须有唯一索引。

(7) 如果在要进行许多插入活动的表上建立一索引，利用填充因子减少页分割，改善并发性和减少死锁。

(8) 如果在只读表上创建索引，利用的填充因子使表或索引层尽可能地紧密。

(9) 使键的大小尽可能地小。

(10)使用小的数据类型，只要它能满足设计。数字间比较快于字符串间比较。可变长度符和二进制类型需比固定长度类型更多的纪录开销。尽可能使被用作索引键的短列为固定长度非空类型。保持不同表join列的数据类型兼容。

规则3-7：存储过程具有的优点 (11)存储过程可以改善性能。使用存储过程可以减少在服务器和客户端之间的数据交换，避免运行时间对SQL语句的分析。

(12)减少开发时间，减少错误。程序员不必再为这些例程写自己的版本，而是共享这些存储过程，进而减少应用程序的潜在的错误。

(13)易于改变控制，便于维护。如果需要改变操作的话，只需要在一个地方改就可以了。

(14)简单。新的程序员不需要知道这些函数的内部复杂性，他们只需要知道存储过程的名称、参数、返回值和目的。

规则3-8：存储过程和触发器的选择准则：当要执行的操作例程是由用户驱动的，则通过存储过程方式来实现该历程；当要执行的操作例程是自动驱动的，则通过触发器方式来实现该历程。

示例：以B型机MML命令的登记操作为例。

在执行MML命令时，如果增加、修改或删除了一个纪录，就要进行登记操作。在B型机中登记操作是在存储过程中进行的，导致存储过程偏长、可读性不好，还可能登记错误。如果采用本规则，考虑到在增加、修改或删除了一个纪录时就要进行登记操作，把登记操作放在触发器中

规则3-9：游标准则

(15)游标会对性能产生不利的影响，主要表现在：页级和表级锁定、网络资源和处理指令的开销。只有必要时才使用游标。

(16)如果需要在活动表上执行大量的更新和删除，可通过在带有游标的存储过程中执行操作，并频繁进行交付，以减少阻塞。

建议3-1：选择索引时应回答的问题

(17)当前什么索引和特定表关联。

(18)使用表的最重要的处理是什么。

(19)表的选择操作和数据修改操作的比例。

(20)是否已给表赋予了聚簇索引。

(21)聚簇索引是否能被非聚簇索引替换。

(22)任何一个索引是否能被聚簇索引替换。

(23)是否需一组合索引来强制复合主键的唯一性。

(24)什么索引可被定义为唯一的。

(25)排序的主要要求是什么。 (26) 索引是否支持你的JOIN操作和应用一致性检查。

(27) 建立索引是否影响修改类型（直接或延迟）。

(28) 游标定位需要什么索引。

(29) 如果需读脏页，是否有唯一索引支持扫描。

(30) 是否需把IDENTITY列加入表和索引以便产生唯一索引。

建议3-2：当决定使用多少索引时，考虑的因素

(31)空间限制。

(32)表的存取途径数据修改与选择操作的比例。

(33)从报表到OLTP的性能要求。

(34)索引改变的性能影响。多长时间进行一次Update statistics。

4 程序效率

规则4-1： 在值查询中作存在检查时，不要使用count集合函数，而要用exists（或in），使用count作存在检查要比用exists慢。

示例：

SELECT * FROM tbl1

WHERE 0 <(SELECT COUNT(*) FROM )

可以修改为

SELECT * FROM tbl

WHERE EXISTS (SELECT count(*) FROM )

规则4-2： SQL SERVER不能优化由or连接的连接子句，它用迪卡尔乘积去执行查询。SQL SERVER能够优化的连接是由Union链接的选择操作。除了对重复行和空表的处理外， or与union的结果很相象（union删除所有重复的数据行，union all则不删除重复数据行。适当的时候可以将or子句转化为union子句。

示例：

SQL Server处理下面的查询，很可能执行迪卡尔乘积。

SELECT* FROM tbl1,tbl2

WHERE tbl1.a=tbl2.b OR tbl1.x=tbl2.y

如果使用union，则可分别对union的两个查询优化。

SELECT* FROM tbl1,tbl2

WHERE tbl1.a=tbl2.b

UNION

SELECT* FROM tbl1,tbl2

WHERE tbl1.x=tbl2.y

规则4-3：当聚集函数的列上有索引时，SQL SERVER为max和min集合函数使用了特殊的优化方法。对于min，它读取索引根页上的第一个值。对于max，它直接到索引的末尾去找最后一行。以下情形中，min和max优化方法不适用

(1) max或min中的参数表达式不是一个列。

(2) max或min中的列不是索引的第一列。

(3) 查询中有其它聚集函数。

(4) 有group by子句。

示例：

（1）max(fldNumeric*2)和max(fldNumeric)*2，假设在fldNumeric上有非聚集索引。由于第一个例子的参数表达式不是一个列，需要对非聚集索引的叶子层扫描，而第二个例子则可以直接定位。

（2）对于如下语句，查询中有其它聚集函数，即使在price上有索引，该查询执行时也会做全表查询。 SELECT MAX(fldPrice),MIN(fldPrice)

FROM tblTitles

可以把查询改写为：

SELECT MAX(fldPrice)

FROM tblTitles

SELECT MAX(fldPrice)

FROM tblTitles

规则4-4：当连接不同数据类型的两列时，其中一列必须被转换为另一列的类型，当连接的表有不相同的数据类型时，其中有一个可以使用索引，但优化器不能选择已被转换列上的索引。

示例：如下情况，SQL Server会把int列转换成float列，这样_col上的索引就不能使用。因此在设计SQL语句时，应尽量避免数据类型的问题。

SELECT * FROM tblSmallTable, tblLargeTable

WHERE at_col = _col

规则4-5： tempdb是一个系统级资源，主要用于排序、创建工作表等的内部处理和用户创建的临时表或索引。

规则4-6：尽量减少每一个查询的读磁盘次数

建议4-1：在查询中，建议在查询子句比较时多使用'>='，少使用'>'。

说明：例如如下查询，在fldint_col建有索引：

SELECT * FROM table WHERE fldint_col > 3

该查询使用索引找到第一个fldint_col等于3的值，然后向前扫描。如果表中有许多行中Fldint_col的值为3，则该查询不得不扫描许多页后找到第一个fldint_col大于3的数据行。若把查询写成下面的形式，则会更有效：

SELECT * FROM table WHERE fldint_col >= 3

建议4-2：优化器不能使用转换后的参数值，因此最好使参数与要进行比较的列与有相同的数据类型。

示例：以如下存储过程为例。

CREATE PROC spExample @ptemp varchar(30)

AS

SELECT * FROM table

WHERE fldchar_col = @ptemp

该存储过程的性能就不如下面定义的存储过程

CREATE PROC spExample @ptemp char(30) --域是字符型，参数是变长字符型

AS

SELECT * FROM table

WHERE fldchar_col = @ptemp --都是字符型

5 事务

规则5-1：事务管理的一般原则

(1) 保持事务短小。把事务劈成几个短事务；避免在一个事务中update或delete大量纪录；事务中不要等待人工干预。

(2) 提高并发度。采用尽可能地的隔离级别；避免页级锁过早升级为表级锁。

(3) 进行物理设计时注意事项。尽可能使用存储过程；力争采用In-place

direct update。

规则5-2：对于如下一些容易填满日志的事务可以采用一些相应的改进方法：

(4) 大表中更改每一行：可以把语句分解，使得每次只影响表的一部分，例如增加where语句，每次更改后转储日志。最后转储数据库。

(5) 删除一个大的表：可以使用truncate语句而不用delete语句

(6) 基于子查询的插入：可以把语句分解，使得每次只影响表的一部分，例如增加Where语句，每次插入后转储日志，最后转储数据库。

(7) 有索引的大表的匹拷贝。 设置在检查点截断日志(执行sp_dboption

dbname,'trunc. log on chkpt.',true)，把拷贝语句分解。做完批拷贝后，设置禁止在检查点截断日志(执行sp_dboption dbname,'trunc. log on

chkpt.',false)。