WebTransactSQL编程规范.docx

WebTransactSQL编程规范.docx

《WebTransactSQL编程规范.docx》由会员分享，可在线阅读，更多相关《WebTransactSQL编程规范.docx（17页珍藏版）》请在冰豆网上搜索。

WebTransactSQL编程规范

Transact-SQL编程规范

修改历史:

创建/修改日期

修改内容

修改人

备注

2009-11-2

增加include索引命名规则

许元锋

2009-11-2

修改声明变量的数据类型为小写

许元锋，陈涛

2009-11-10

所有选择字段里涉及的逗号均放在前面对齐

许元锋

2009-11-10

索引键或主键列列名中包含下划线，则索引或主键名称中将下划线去掉

陈涛

2009-11-12

基本原则补充:

除非必有，否则存储过程、视图、用户自定义函数等对象的定义部分不允许包含数据库名

陈涛

2009-11-12

修改示例中不严谨的地方

陈涛

Thanksto马知鹏

2010-01-18

存储过程中临时表的使用

陈涛

Transact-SQL编程规范1

概述4

1.1.基本原则4

1.2.基本规范4

命名规范4

1.3.对象命名5

1.3.1. 数据库5

1.3.2. 数据库文件5

1.3.3. 关系型数据仓库5

1.3.4. 数据架构6

1.3.5. 数据表6

1.3.6. 数据视图6

1.3.7. 数据列7

1.3.8. 存储过程7

1.3.9. 函数8

1.3.10.    用户定义数据类型8

1.3.11.    DML触发器8

1.3.12.    DDL触发器9

1.3.13.    主键、外键关系和索引10

1.4.参数命名11

1.4.1. 数据列参数11

1.4.2. 非数据列参数11

1.5.常见命名12

1.5.1. 常用字段命名12

2.SQL编写13

2.1.大小写13

2.2.使用“;”14

2.3.存储格式14

2.4.类型选择14

2.5.默认值15

2.6.字段长度15

2.7.使用“'”15

2.8.语句缩进15

2.9.语句换行16

2.10.  语句分割16

2.11.  使用“*”17

2.12.  表名别名17

2.13.  类型转换17

2.14.  数值比较17

2.15.  排序17

2.16.  Unicode字符串17

2.17.  BEGIN...END块18

2.18.  TRY块18

2.19.  TOP子句19

2.20.  TRANSACTION编写19

2.21.  存储过程20

2.22.  存储过程中临时表的使用20

3.代码注释21

3.1.代码头部注释21

3.2.TRANSACTION注释22

附录A 命名规则22

4.1.Pascal大小写22

4.2.Camel大小写22

4.3.匈牙利命名法22

概述

进行T-SQL编程时常常会忽略编码标准，但这些标准却是开发小组顺利开展工作的关键工具。

让我们从格式开始。

表面上，T-SQL代码的格式似乎并不重要，但一致的格式可以使您的同事（不论是同一小组的成员还是更大范围的T-SQL开发团队的成员）更轻松地浏览和理解您的代码。

T-SQL语句有一个结构，遵循一目了然的结构使您可以更轻松地查找和确认语句的不同部分。

统一的格式还使您可以更轻松地在复杂T-SQL语句中增删代码段，使调试工作变得更容易。

1.1.基本原则

以大小写敏感编写SQL语句。

尽量使用Unicode数据类型（针对多国语言）。

优先使用连接代替子查询或嵌套查询。

尽量使用参数化SQL查询（sp_executesql）代替语句拼接SQL查询。

禁止使用[拼音]+[英语]的方式来命名SQL对象或变量。

尽量使用存储过程代替SQL语句。

（windows服务类型的SQL限定再讨论）

除非必有，否则存储过程、视图、用户自定义函数等对象的定义部分不允许包含数据库名。

1.2.基本规范

建议采用Pascal样式命名数据库对象。

大写T-SQL语言的所有关键字，谓词和系统函数。

数据类型定义使用全部小写。

命名规范

在一般情况下，采用Pascal样式或Camel样式命名数据库对象，使在开发基于数据库应用程序的时候通过ORM工具生成的数据访问代码不需要调整就符合程序开发语言（比如C#）命名规范。

另外，关系型数据库同Xml结合得越来越紧密,规范的命名越来越重要。

在实际数据库开发过程中，如果需求方已经提供数据库设计方案，建议以提供的方案为准；在原有数据库上进行升级开发时，在可行的情况下可适当做出设计调整以符合编程规范。

1.3.对象命名

1.3.1. 数据库

第一种方式，采用Pascal样式命名，命名格式为[项目英文名称]。

示例：

AdventureWorks

第二种方式，采用Pascal样式命名，命名格式为[项目英文名称]+Db。

示例：

AdventureWorksDb

 BizTalkRuleEngineDb

建议采用第一种方式。

1.3.2. 数据库文件

数据文件：

[数据库名称]+_Data.mdf

日志文件：

[数据库名称]+_Log.ldf

示例：

AdventureWorks_Data.mdf

    AdventureWorks_Log.ldf

1.3.3. 关系型数据仓库

采用Pascal样式命名，命名格式为[项目英文名称]+DW。

示例：

AdventureWorksDW

1.3.4. 数据架构

除SQLServer系统定义的数据架构外，新建架构采用Pascal样式命名，命名格式为[架构名]。

示例：

HumanResources

    Production

对数据库对象Table，View，Procedure，Function等使用数据架构进行归类。

在SQLServer2000中dbo为默认架构。

1.3.5. 数据表

A.采用Pascal样式命名，命名格式为[表名]。

示例：

Employee

    Product

表名以英文单数命名，主要是参考SQLServer2005示例数据库，个人理解不采用复数是为了更好的使用ORM工具生成符合编程规范的代码（比如C#）。

示例：

使用Product

 而不是Products

B.可以设置过滤前缀，如设置Ref_，则Ref_Group将不会生成实体，举例：

Ref_Table

1.3.6. 数据视图

视图名称采用Pascal样式命名，命名格式为V+[视图名称]。

示例：

VEmployee

    VSalesPerson

1.3.7. 数据列

A.列名称命名采用英文单词或缩写，英文单词只来自于具体业务定义，尽量表达清楚含义。

采用Pascal样式命名，命名格式为[列名称]。

示例：

AddressId

    PostalCode

B.除非是外键字段，否则不能包含下划线（外键在可见的分布式架构中去掉）,将来系统全部删除外键

C.在只有INSERT的大容量的数据表中，不要使用自增列标识（Id）

D.尽量不要设置字段允许为NULL

1.3.8. 存储过程

A.存储过程名：

[Schema].[Class_Method]的形式

B.特别约定：

Brand_GetXxxx，采用ExecuteReader，否则采用ExecuteNonQuery

Brand_GetItem，默认返回BrandItem

Brand_GetList，默认返回BrandCollection

Brand_GetXxxListByXxx，默认返回BrandCollection

Brand_GetXxxListForXxx，默认返回BrandCollection

C.特别处理：

Brand_Update，Brand_Create，参数都要是对应表的字段

D.自定义存储过程结果集的方式，在过程声明前添加：

--

--PressItemPress;

--PressCommentCollectionPressComments;

--

E.分页的存储过程

Brand_GetPagedList

Brand_GetPagedListByXxx

Brand_GetPagedListForXxx

预定义的参数：

@PageIndexint,第几页，第一页为1

@PageSizeint,页面大小

@TotalCountintOUTPUT

1.3.9. 函数

自定义函数采用Pascal样式命名，命名格式为[函数名]，系统函数使用全部大写。

示例：

SELECTISNULL（@LastName,'Unknownlastname'）;

GETDATE（）

1.3.10.    用户定义数据类型

采用Pascal样式命名，命名格式为[自定义数据类型名称]。

示例：

Flag

    NameStyle

1.3.11.    DML触发器

DML触发器是当数据库服务器中发生数据操作语言（DML）事件时要执行的操作。

DML事件包括对表或视图发出的UPDATE、INSERT或DELETE语句。

根据事件不同命名规则使用前缀进行区分,格式为[u|i|d]+[表名|视图名]

示例：

uEmployee

 iEmployee

     dEmployee

另外一种方式为，

AFTER触发器：

TR_表名_[后面插入加I,修改加U,删除加D]。

INSTEADOF触发器：

TR_表名或视图名_OF[后面插入加I,修改加U,删除加D]

1.3.12.    DDL触发器

响应各种数据定义语言（DDL）事件而激发。

这些事件主要与以关键字CREATE、ALTER和DROP开头的Transact-SQL语句对应。

执行DDL式操作的系统存储过程也可以激发DDL触发器。

采用Camel样式命名，命名单词能够描述DDL触发器功能。

示例：

CREATETRIGGERsafety

ONDATABASE

FORDROP_TABLE,ALTER_TABLE

AS

  PRINT'YoumustdisableTrigger"safety"todroporaltertables!

'

  ROLLBACK;

另外一种方式为添加ddl前缀，

示例：

CREATETRIGGER[ddlDatabaseTriggerLog]

ONDATABASE

FORDDL_DATABASE_LEVEL_EVENTS

AS

1.3.13.    主键、外键关系和索引

主键:

PK_[表名称]_[主键]；如果是组合主键，使用PK_[表名]_[主键1]_[主键2]。

示例：

PK_Store_CustomerId

 PK_StoreContact_CustomerId_ContactId

外键关系：

FK_[从表名称]_[主表名称]_[外键列名称]。

示例：

FK_StoreContact_Store_CustomerId

聚集索引：

PK_[表名称]_[主键]；如果是组合主键，使用PK_[表名]_[主键1]_[主键2]。

示例：

PK_Store_CustomerId

 PK_StoreContact_CustomerId_ContactId

唯一非聚集索引:

AK_[表名称]_[列名称]。

示例：

AK_Store_rowguid

不唯一非聚集索引：

IX_[表名称]_[列名称]。

示例：

IX_Store_SalesPersonId

主XML索引：

PXML_[表名称]_[Xml类型列名称]。

示例：

PXML_Store_Demographic

包含性列的索引

IX_[表名称]_[列名称]_INCLUDE_[第一列列名称]_[第二列列名称]

示例：

CREATEINDEXIX_Document_Title_Revision_INCLUDE_FileName

ONProduction.Document（Title,Revision）

INCLUDE（FileName）;

备注：

1.如果索引键或主键列列名中包含下划线，则索引或主键名称中将下划线去掉

示例:

PK_UserInfo_UserId（Site.UserInfo表的User_Id列为聚集索引键）

1.4.参数命名

1.4.1. 数据列参数

命名格式为@+[列名称]。

示例：

@EmployeeId

在列名不符合Pascal样式时（早期遗留系统），参数名称定义使用@+[列名称]，这里的列名称尽量符合Pascal样式命名。

1.4.2. 非数据列参数

在参数无法跟列名称进行关联时，使用能够反映该参数功能的英文单词或单词组合,采用Pascal样式命名。

示例：

@ErrorId

    @Flag

1.5.常见命名

1.5.1. 常用字段命名

这里的常用字段是指在建表时频繁使用的表名或列名，下表对常用字段进行建议性定义:

列名称     

  数据类型   

   说明

CreatedDate    

smalldatetime

纪录创建日期，一般使用GETDATE（）

CreatedDateTimedatetime

datetime

建立的时间（包括日期）,一般使用GETDATE（）

CreatorId

int

创建者

ModifierId

int

修改者

ModifiedDate      

smalldatetime

纪录最后修改日期，首次使用ETDATE（）

ModifiedDateTime

datetime

最后修改的时间（包括日期），getdate（）

Position

int

排序位置

Count

int

用来存储数量

XxxxCount

int

用来存储子表的数量

DeletedDate               

smalldatetime

记录删除（标记删除）日期

StartDate                

smalldatetime

开始日期

EndDate                    

smalldatetime

结束日期

StartTime                

datetime     

开始时间

EndTime                   

datetime  

结束时间

rowguid        

uniqueidentifier     

唯一标识行的ROWGUIDCOL号，用于支持合并复制

Id             

int

使用Id代替ID或id。

一般为自增长主键列

ParentId       

int           

父Id

Status      

bit/tinyint       

状态

RowVersion

timestamp

用来控制版本

一些字段名称的特殊约定：

IsRead是否已经被阅读

IsReplied是否已经被回复

IsReviewed是否已经被查看

IsApproved是否已经被审核

IsSensitive是否包含敏感词

IsDeleted是否已经被删除

2.SQL编写

2.1.大小写

大写T-SQL语言的所有关键字，谓词和系统函数。

变量名称及游标名称使用Pascal样式。

数据类型定义使用全部小写。

示例：

DECLARE@LastNamenvarchar（32）;

2.2.使用“;”

使用“;”作为Transact-SQL语句终止符。

虽然分号不是必需的，但使用它是一种好的习惯。

示例：

USEAdventureWorks;

GO

DECLARE@findvarchar（30）;

SET@find='Man%';

SELECTLastName,FirstName,Phone

FROMPerson.Contact

WHERELastNameLIKE@find;

2.3.存储格式

尽量采用Unicode数据存储格式，提高可移植性和兼容性，实际应用中尽量使用nchar、nvarchar、ntext代替char、varchar、text。

2.4.类型选择

类型的选择要本着节省存储，提高性能的基础上考虑，具体大小请参照官方文档:

如果字符具有明确的长度，使用nchar代替nvarchar；char代替varchar。

在只有两个可能数值时，使用bit代替int或smallint。

在SQLServer2005中，使用nvarchar（MAX）代替ntext；varchar（MAX）代替text；varbinary（MAX）代替image。

在特殊的数据表结构中可考虑xml数据类型，达到事半工倍的效果。

2.5.默认值

在建立数据表时，尽量使用默认值代替NULL值。

比如设置CreatedDate列默认值为GETDATE（）。

在可行的情况下设置字段为不允许NULL。

2.6.字段长度

始终指定字符数据类型的长度，并确保允许用户可能需要的最大字符数，避免超出最大长度时出现字符丢失现象。

对于字符型数据，建议采用2的n次方来定义数据长度。

示例：

nvarchar（32）

 varchar（64）

2.7.使用“'”

在T-SQL代码中为字符常量使用单引号,避免使用双引号。

2.8.语句缩进

A.一个嵌套代码块中的语句使用四个空格的缩进。

使用MicrosoftSQLServerManagementStudio,选择“工具”菜单，打开“选项”菜单，在选项对话框中选择文本编辑器->纯文本->制表符,选中“插入空格单选框”,设置“制表符大小”为4，缩进大小为“4”。

B.程序全部采用左对齐

C.程序采用缩进风格书写、对于主块（比如BEGINEND之间）统一采用1个tab

D.同一级别换行必须保持左对齐

2.9.语句换行

A.建议SQL代码每行以关键字或“'”开头。

示例：

SELECT[ShiftId]

     ,[Name]

     ,[StartTime]

     ,[EndTime]

     ,[ModifiedDate]

FROM[HumanResources].[Shift]

B.相对独立的程序块之间必须加空行，同时增加注释。

C.长表达式应在低先级操作符处换行，操作符或关键字放在新行之首。

划分出新行应当适当地缩进，使排版整齐，语句可读。

D.BEGIN、END独立成行

E.所有选择字段里涉及的逗号均放在前面对齐

2.10.  语句分割

使用一个（而不是两个）空行分隔T-SQL代码的逻辑块。

2.11.  使用“*”

避免在任何代码中使用“SELECT*”。

2.12.  表名别名

表名别名要简短，但意义要尽量明确。

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

2.13.  类型转换

不要依赖任何隐式的数据类型转换，不要假定T-SQL会进行必要的转换。

例如，把数字变量赋予字符值。

相反，在为变量赋值或比较值之前，应使用适当的CONVERT函数使数据类型相匹配。

2.14.  数值比较

不要将空的变量值直接与比较运算符（符号）比较。

如果变量可能为空，应使用ISNULL或ISNOTNULL进行比较，或者使用ISNULL函数。

2.15.  排序

决不要依赖SELECT语句会按任何特定顺序返回行，除非在ORDERBY子句中指定了顺序。

通常，应将ORDERBY子句与SELECT语句一起使用。

可预知的顺序（即使不是最方便的）比不可预知的顺序强，尤其是在开发或调试过程中。

在返回行的顺序无关紧要的情况下，可以忽略ORDERBY，减少资源开销。

2.16.  Unicode字符串

在Unicode字符前面使用N前缀，避免引起数据的不一致。

示例：

--AssumesthedefaultcodepageisnotGreek

CREATETABLE#t1（c1nchar

（1））

INSERT#t1VALUES（N'Ω'）

INSERT#t1VALUES（'Ω'）

SELECT*FROM#t1

输出结果：

c1  

----

Ω

O

2.17.  BEGIN...END块

在SQL代码快中尽量使用BEGIN...END语句块，提高代码可阅读性。

2.18.  TRY块

在SQLServer2005中对一些可能执行失败的语句尽量使用TRY块。

Transact-SQL语句组可以包含在TRY块中，如果TRY块内部发生错误，则会将控制传递给CATCH块中包含的另一个语句组。

示例：

BEGINTRY

   SQL语句组1

ENDTRY

BEGINCATCH

   SQL语句组2

ENDCATCH;

2.19.  TOP子句

在SQLServer2005中加强了TOP的使用，尽量使用TOP（变量）来减少SQL拼串现象。

2.20.  TRANSACTION编写

只要在例程中使用多个数据库修改语句，包括在一个循环中多次执行一个语句，就应考虑声明显式事务。

在SQLSERVER2005中，增加了TRY块可进行很好的应用。

实例：

   BEGINTRY

       BEGINTRANSACTION;

       UPDATE[HumanResources].[Employee]

       SET[Title]=@Title

           ,[HireDate]=@HireDate

           ,[CurrentFlag]=@CurrentFlag

       WHERE[EmployeeId]=@EmployeeId;

       INSERTINTO[HumanResources].[EmployeePayHistory]

           （[EmployeeId]

           ,[RateChangeDate]

           ,[Rate]

           ,[PayFrequency]）

       VALUES（@EmployeeId,@RateChangeDate,@Rate,@PayFrequency）;

       COMMITTRANSACTION;

   ENDTRY

   BEGINCATCH

       --Rollbackanyactiveoruncommittabletransactionsbefore

       --insertinginformationintheErrorLog

       IF@@TRA