WebTransactSQL编程规范.docx

1、WebTransactSQL编程规范Transact-SQL编程规范修改历史: 创建/修改日期修改内容修改人备注2009-11-2增加include索引命名规则许元锋2009-11-2修改声明变量的数据类型为小写许元锋，陈涛2009-11-10所有选择字段里涉及的逗号均放在前面对齐许元锋2009-11-10索引键或主键列列名中包含下划线，则索引或主键名称中将下划线去掉陈涛2009-11-12基本原则补充:除非必有，否则存储过程、视图、用户自定义函数等对象的定义部分不允许包含数据库名陈涛2009-11-12修改示例中不严谨的地方陈涛Thanks to 马知鹏2010-01-18存储过程中临时表的

2、使用陈涛Transact-SQL编程规范 1概述 41.1.基本原则 41.2.基本规范 4命名规范 41.3.对象命名 51.3.1.数据库 51.3.2.数据库文件 51.3.3.关系型数据仓库 51.3.4.数据架构 61.3.5.数据表 61.3.6.数据视图 61.3.7.数据列 71.3.8.存储过程 71.3.9.函数 81.3.10. 用户定义数据类型 81.3.11. DML触发器 81.3.12. DDL触发器 91.3.13. 主键、外键关系和索引 101.4.参数命名 111.4.1.数据列参数 111.4.2.非数据列参数 111.5.常见命名 121.5.1.常用字

3、段命名 122.SQL编写 132.1.大小写 132.2.使用“;” 142.3.存储格式 142.4.类型选择 142.5.默认值 152.6.字段长度 152.7.使用“” 152.8.语句缩进 152.9.语句换行 162.10. 语句分割 162.11. 使用“*” 172.12. 表名别名 172.13. 类型转换 172.14. 数值比较 172.15. 排序 172.16. Unicode字符串 172.17. BEGIN.END 块 182.18. TRY块 182.19. TOP子句 192.20. TRANSACTION编写 192.21. 存储过程 202.22. 存储

4、过程中临时表的使用 203.代码注释 213.1.代码头部注释 213.2.TRANSACTION注释 22附录A 命名规则 224.1.Pascal 大小写 224.2.Camel 大小写 224.3.匈牙利命名法 22概述进行 T-SQL 编程时常常会忽略编码标准，但这些标准却是开发小组顺利开展工作的关键工具。让我们从格式开始。表面上，T-SQL 代码的格式似乎并不重要，但一致的格式可以使您的同事（不论是同一小组的成员还是更大范围的 T-SQL 开发团队的成员）更轻松地浏览和理解您的代码。T-SQL 语句有一个结构，遵循一目了然的结构使您可以更轻松地查找和确认语句的不同部分。统一的格式还使

5、您可以更轻松地在复杂 T-SQL 语句中增删代码段，使调试工作变得更容易。1.1.基本原则以大小写敏感编写SQL语句。尽量使用Unicode 数据类型(针对多国语言)。优先使用连接代替子查询或嵌套查询。尽量使用参数化SQL查询(sp_executesql)代替语句拼接SQL查询。禁止使用拼音+英语的方式来命名SQL对象或变量。尽量使用存储过程代替SQL语句。（windows服务类型的SQL限定再讨论）除非必有，否则存储过程、视图、用户自定义函数等对象的定义部分不允许包含数据库名。1.2.基本规范建议采用Pascal样式命名数据库对象。大写T-SQL语言的所有关键字，谓词和系统函数。数据类型定义

6、使用全部小写。命名规范在一般情况下，采用Pascal样式或Camel样式命名数据库对象，使在开发基于数据库应用程序的时候通过ORM工具生成的数据访问代码不需要调整就符合程序开发语言（比如C#）命名规范。另外，关系型数据库同Xml结合得越来越紧密,规范的命名越来越重要。在实际数据库开发过程中，如果需求方已经提供数据库设计方案，建议以提供的方案为准；在原有数据库上进行升级开发时，在可行的情况下可适当做出设计调整以符合编程规范。1.3.对象命名1.3.1.数据库第一种方式，采用Pascal样式命名，命名格式为项目英文名称。示例：AdventureWorks第二种方式，采用Pascal样式命名，命名格

7、式为项目英文名称 + Db。示例：AdventureWorksDb BizTalkRuleEngineDb建议采用第一种方式。1.3.2.数据库文件数据文件：数据库名称 + _Data.mdf日志文件：数据库名称 + _Log.ldf示例：AdventureWorks_Data.mdf AdventureWorks_Log.ldf1.3.3.关系型数据仓库采用Pascal样式命名，命名格式为项目英文名称 + DW。示例：AdventureWorksDW1.3.4.数据架构除SQL Server 系统定义的数据架构外，新建架构采用Pascal样式命名，命名格式为架构名。示例：HumanResou

8、rces Production对数据库对象 Table，View，Procedure，Function等使用数据架构进行归类。在SQL Server 2000中dbo为默认架构。1.3.5.数据表A.采用Pascal样式命名，命名格式为表名。示例：Employee Product表名以英文单数命名，主要是参考SQL Server 2005示例数据库，个人理解不采用复数是为了更好的使用ORM工具生成符合编程规范的代码（比如C#）。示例：使用Product而不是ProductsB. 可以设置过滤前缀，如设置Ref_，则Ref_Group将不会生成实体，举例：Ref_Table1.3.6.数据视图视

9、图名称采用Pascal样式命名，命名格式为V + 视图名称。示例：VEmployee VSalesPerson1.3.7.数据列A.列名称命名采用英文单词或缩写，英文单词只来自于具体业务定义，尽量表达清楚含义。采用Pascal样式命名，命名格式为列名称。示例：AddressId PostalCodeB. 除非是外键字段，否则不能包含下划线(外键在可见的分布式架构中去掉),将来系统全部删除外键C.在只有INSERT的大容量的数据表中，不要使用自增列标识(Id)D. 尽量不要设置字段允许为NULL1.3.8.存储过程A. 存储过程名： Schema.Class_Method的形式B. 特别约定：B

10、rand_GetXxxx，采用ExecuteReader，否则采用ExecuteNonQueryBrand_GetItem，默认返回BrandItemBrand_GetList，默认返回BrandCollectionBrand_GetXxxListByXxx，默认返回BrandCollectionBrand_GetXxxListForXxx，默认返回BrandCollectionC. 特别处理：Brand_Update，Brand_Create，参数都要是对应表的字段D. 自定义存储过程结果集的方式，在过程声明前添加：-PressItem Press;-PressCommentCollecti

11、on PressComments;-E. 分页的存储过程 Brand_GetPagedListBrand_GetPagedListByXxxBrand_GetPagedListForXxx预定义的参数：PageIndex int, 第几页，第一页为1PageSize int, 页面大小TotalCount int OUTPUT 1.3.9.函数自定义函数采用Pascal样式命名，命名格式为函数名，系统函数使用全部大写。示例：SELECT ISNULL(LastName,Unknown last name);GETDATE()1.3.10. 用户定义数据类型采用Pascal样式命名，命名格式为自

12、定义数据类型名称。示例：Flag NameStyle1.3.11. DML触发器DML触发器是当数据库服务器中发生数据操作语言 (DML) 事件时要执行的操作。DML 事件包括对表或视图发出的 UPDATE、INSERT 或 DELETE 语句。根据事件不同命名规则使用前缀进行区分,格式为 u|i|d + 表名|视图名示例：uEmployee iEmployee dEmployee另外一种方式为，AFTER 触发器：TR_表名_后面插入加I,修改加U,删除加D。INSTEAD OF 触发器：TR_表名或视图名_OF后面插入加I,修改加U,删除加D1.3.12. DDL触发器响应各种数据定义语言

13、 (DDL) 事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。采用Camel样式命名，命名单词能够描述DDL触发器功能。示例：CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT You must disable Trigger safety to drop or alter tables! ROLLBACK ;另外一种方式为添加ddl前缀，示例：CREATE TRIGGER

14、 ddlDatabaseTriggerLog ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS1.3.13. 主键、外键关系和索引主键: PK_表名称_主键；如果是组合主键，使用PK_表名_主键1_主键2。示例：PK_Store_CustomerId PK_StoreContact_CustomerId_ContactId外键关系：FK_从表名称_主表名称_外键列名称。示例：FK_StoreContact_Store_CustomerId聚集索引：PK_表名称_主键；如果是组合主键，使用PK_表名_主键1_主键2。示例：PK_Store_Customer

15、Id PK_StoreContact_CustomerId_ContactId唯一非聚集索引:AK_表名称_列名称。示例：AK_Store_rowguid不唯一非聚集索引：IX_表名称_列名称。示例：IX_Store_SalesPersonId主 XML索引：PXML_表名称_Xml类型列名称。示例：PXML_Store_Demographic包含性列的索引IX_表名称_列名称_INCLUDE_第一列列名称_第二列列名称示例：CREATE INDEX IX_Document_Title_ Revision _INCLUDE_FileNameON Production.Document (Tit

16、le, Revision) INCLUDE (FileName); 备注：1. 如果索引键或主键列列名中包含下划线，则索引或主键名称中将下划线去掉示例:PK_UserInfo_UserId (Site.UserInfo表的User_Id列为聚集索引键)1.4.参数命名1.4.1.数据列参数命名格式为 + 列名称。示例：EmployeeId在列名不符合Pascal样式时（早期遗留系统），参数名称定义使用 + 列名称，这里的列名称尽量符合Pascal样式命名。1.4.2.非数据列参数在参数无法跟列名称进行关联时，使用能够反映该参数功能的英文单词或单词组合, 采用Pascal样式命名。示例：Erro

17、rId Flag1.5.常见命名1.5.1.常用字段命名这里的常用字段是指在建表时频繁使用的表名或列名，下表对常用字段进行建议性定义:列名称 数据类型 说明CreatedDate smalldatetime纪录创建日期，一般使用GETDATE()CreatedDateTime datetimedatetime建立的时间（包括日期）, 一般使用GETDATE()CreatorId int创建者ModifierId int修改者ModifiedDate smalldatetime 纪录最后修改日期，首次使用ETDATE()ModifiedDateTime datetime最后修改的时间（包括日期）

18、，getdate()Position int排序位置Count int用来存储数量XxxxCount int用来存储子表的数量DeletedDate smalldatetime记录删除（标记删除）日期StartDate smalldatetime开始日期EndDatesmalldatetime结束日期StartTime datetime开始时间EndTime datetime结束时间rowguid uniqueidentifier 唯一标识行的ROWGUIDCOL号，用于支持合并复制Idint使用Id代替ID或id。一般为自增长主键列ParentIdint父IdStatusbit/tinyin

19、t状态RowVersion timestamp用来控制版本一些字段名称的特殊约定：IsRead 是否已经被阅读IsReplied 是否已经被回复IsReviewed 是否已经被查看IsApproved 是否已经被审核IsSensitive 是否包含敏感词IsDeleted 是否已经被删除2.SQL编写2.1.大小写大写T-SQL 语言的所有关键字，谓词和系统函数。变量名称及游标名称使用Pascal样式。数据类型定义使用全部小写。示例：DECLARE LastName nvarchar(32);2.2.使用“;”使用“;”作为 Transact-SQL 语句终止符。虽然分号不是必需的，但使用它是

20、一种好的习惯。示例：USE AdventureWorks;GODECLARE find varchar(30);SET find = Man%;SELECT LastName, FirstName, PhoneFROM Person.ContactWHERE LastName LIKE find;2.3.存储格式尽量采用Unicode数据存储格式，提高可移植性和兼容性，实际应用中尽量使用nchar、nvarchar、ntext代替char、varchar、text。2.4.类型选择类型的选择要本着节省存储，提高性能的基础上考虑，具体大小请参照官方文档:如果字符具有明确的长度，使用nchar代替

21、nvarchar；char代替varchar。在只有两个可能数值时，使用bit代替int或smallint。在SQL Server 2005中，使用nvarchar(MAX)代替ntext；varchar(MAX)代替text；varbinary(MAX)代替image。在特殊的数据表结构中可考虑xml数据类型，达到事半工倍的效果。2.5.默认值在建立数据表时，尽量使用默认值代替NULL值。比如设置CreatedDate列默认值为GETDATE()。在可行的情况下设置字段为不允许NULL。2.6.字段长度始终指定字符数据类型的长度，并确保允许用户可能需要的最大字符数，避免超出最大长度时出现字符

22、丢失现象。对于字符型数据，建议采用2的n次方来定义数据长度。示例：nvarchar(32) varchar(64)2.7.使用“”在 T-SQL 代码中为字符常量使用单引号,避免使用双引号。2.8.语句缩进A.一个嵌套代码块中的语句使用四个空格的缩进。使用Microsoft SQL Server Management Studio ,选择“工具”菜单，打开“选项”菜单，在选项对话框中选择文本编辑器-纯文本-制表符,选中“插入空格单选框”,设置“制表符大小”为4，缩进大小为“4”。B. 程序全部采用左对齐 C. 程序采用缩进风格书写、对于主块（比如BEGIN END之间）统一采用1个tabD.

23、同一级别换行必须保持左对齐2.9.语句换行A.建议SQL代码每行以关键字或“”开头。示例：SELECT ShiftId ,Name ,StartTime ,EndTime ,ModifiedDateFROM HumanResources.ShiftB.相对独立的程序块之间必须加空行，同时增加注释。 C. 长表达式应在低先级操作符处换行，操作符或关键字放在新行之首。划分出新行应当适当地缩进，使排版整齐，语句可读。 D. BEGIN、END 独立成行E.所有选择字段里涉及的逗号均放在前面对齐2.10. 语句分割使用一个（而不是两个）空行分隔 T-SQL 代码的逻辑块。2.11. 使用“*”避免在任

24、何代码中使用 “SELECT *”。2.12. 表名别名表名别名要简短，但意义要尽量明确。通常使用大写的表名作为别名，使用 AS 关键字指定表或字段的别名。2.13. 类型转换不要依赖任何隐式的数据类型转换，不要假定 T-SQL 会进行必要的转换。例如，把数字变量赋予字符值。相反，在为变量赋值或比较值之前，应使用适当的 CONVERT 函数使数据类型相匹配。2.14. 数值比较不要将空的变量值直接与比较运算符（符号）比较。如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。2.15. 排序决不要依赖 SELECT 语句会按任何特定顺序返

25、回行，除非在 ORDER BY 子句中指定了顺序。通常，应将 ORDER BY 子句与 SELECT 语句一起使用。可预知的顺序（即使不是最方便的）比不可预知的顺序强，尤其是在开发或调试过程中。在返回行的顺序无关紧要的情况下，可以忽略 ORDER BY ，减少资源开销。2.16. Unicode字符串在Unicode字符前面使用N前缀，避免引起数据的不一致。示例：- Assumes the default code page is not GreekCREATE TABLE #t1 (c1 nchar(1)INSERT #t1 VALUES(N)INSERT #t1 VALUES()SELEC

26、T * FROM #t1输出结果：c1 - O2.17. BEGIN.END 块在SQL代码快中尽量使用BEGIN.END 语句块，提高代码可阅读性。2.18. TRY块在SQL Server 2005中对一些可能执行失败的语句尽量使用TRY块。Transact-SQL 语句组可以包含在 TRY 块中，如果 TRY 块内部发生错误，则会将控制传递给 CATCH 块中包含的另一个语句组。示例：BEGIN TRY SQL 语句组1END TRYBEGIN CATCH SQL 语句组2END CATCH;2.19. TOP子句在SQL Server 2005中加强了TOP的使用，尽量使用TOP(变量

27、)来减少SQL拼串现象。2.20. TRANSACTION编写只要在例程中使用多个数据库修改语句，包括在一个循环中多次执行一个语句，就应考虑声明显式事务。在SQL SERVER 2005 中，增加了TRY块可进行很好的应用。实例： BEGIN TRY BEGIN TRANSACTION; UPDATE HumanResources.Employee SET Title = Title ,HireDate = HireDate ,CurrentFlag = CurrentFlag WHERE EmployeeId = EmployeeId; INSERT INTO HumanResources.EmployeePayHistory (EmployeeId ,RateChangeDate ,Rate ,PayFrequency) VALUES (EmployeeId, RateChangeDate, Rate, PayFrequency); COMMIT TRANSACTION; END TRY BEGIN CATCH - Rollback any active or uncommittable transactions before - inserting information in the ErrorLog IF TRA