TSQL编码规范.doc

Transact-SQL编程规范Version 1.1发布日期：2007-10-26 更新日期：2007-10-261.概述1.1.基本原则以大小写敏感编写SQL语句。尽量使用Unicode 数据类型。优先使用连接代替子查询或嵌套查询。尽量使用参数化SQL查询代替语句拼接SQL查询。禁止使用拼音+英语的方式来命名SQL对象或变量。尽量使用存储过程代替SQL语句。1.2.基本规范建议采用Pascal样式或Camel样式命名数据库对象。大写T-SQL语言的所有关键字，谓词和系统函数。2.命名规范在一般情况下，采用Pascal样式或Camel样式命名数据库对象，使在开发基于数据库应用程序的时候通过ORM工具生成的数据访问代码不需要调整就符合程序开发语言（比如C#）命名规范。另外，关系型数据库同Xml结合得越来越紧密,规范的命名越来越重要。在实际数据库开发过程中，如果需求方已经提供数据库设计方案，建议以提供的方案为准；在原有数据库上进行升级开发时，在可行的情况下可适当做出设计调整以符合编程规范。1.3.对象命名1.3.1.数据库第一种方式，采用Pascal样式命名，命名格式为项目英文名称。示例：AdventureWorks第二种方式，采用Pascal样式命名，命名格式为项目英文名称 + Db。示例：AdventureWorksDbBizTalkRuleEngineDb建议采用第一种方式。1.3.2.数据库文件数据文件：数据库名称 + _Data.mdf日志文件：数据库名称 + _Log.ldf示例：AdventureWorks_Data.mdf AdventureWorks_Log.ldf1.3.3.关系型数据仓库采用Pascal样式命名，命名格式为项目英文名称 + DW。示例：AdventureWorksDW1.3.4.数据架构除SQL Server 系统定义的数据架构外，新建架构采用Pascal样式命名，命名格式为架构名。示例：HumanResources Production对数据库对象 Table，View，Procedure，Function等使用数据架构进行归类。在SQL Server 2000中dbo为默认架构。1.3.5.数据表采用Pascal样式命名，命名格式为表名。示例：Employee Product表名以英文单数命名，主要是参考SQL Server 2005示例数据库，个人理解不采用复数是为了更好的使用ORM工具生成符合编程规范的代码（比如C#）。示例：使用Product而不是Products1.3.6.数据视图视图名称采用Pascal样式命名，命名格式为view_主表名称。示例：View_Employee View_SalesPerson1.3.7.数据列列名称命名采用英文单词或缩写，英文单词只来自于具体业务定义，尽量表达清楚含义。采用Pascal样式命名，命名格式为列名称。示例：AddressID PostalCode尽量避免使用拼音命名，如果不可避免，对于比较短的列名，采用拼音全写，如果拼音列名比较复杂，可以采用首个字用全拼，其它字用首字母大写表示。示例：宁波 Ningbo经营方式 JingYFS1.3.8.存储过程建议采用Pascal样式命名，命名格式为proc_存储过程名称。示例：Proc_GetUser Proc_AddUser备注：在SQL Server 2005示例数据库中使用Camel样式命名。1.3.9.函数自定义函数采用Pascal样式命名，命名格式为函数名，系统函数使用全部大写。示例：SELECT ISNULL(LastName,Unknown last name);GETDATE()1.3.10. 用户定义数据类型采用Pascal样式命名，命名格式为自定义数据类型名称。示例：Flag NameStyle1.3.11. DML触发器DML触发器是当数据库服务器中发生数据操作语言 (DML) 事件时要执行的操作。DML 事件包括对表或视图发出的 UPDATE、INSERT 或 DELETE 语句。根据事件不同命名规则使用前缀进行区分,格式为 TR_表名|视图名_ForInsert|Update|Delete示例：TR_Employee_ForInsertTR_Employee_ForUpdateTR_Employee_ForInsertUpdate1.3.12. DDL触发器响应各种数据定义语言 (DDL) 事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。采用Camel样式命名，命名单词能够描述DDL触发器功能。示例：CREATE TRIGGER TR_safety_ForDropAlterTable ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT You must disable Trigger safety to drop or alter tables! ROLLBACK ;1.3.13. 主键、外键关系和索引主键: PK_表名称_主键；如果是组合主键，使用PK_表名_主键1_主键2。示例：PK_Store_CustomerIDPK_StoreContact_CustomerID_ContactID外键关系：FK_从表名称_主表名称_外键列名称。示例：FK_StoreContact_Store_CustomerID聚集索引：PK_表名称_主键；如果是组合主键，使用PK_表名_主键1_主键2。示例：PK_Store_CustomerID PK_StoreContact_CustomerID_ContactID唯一非聚集索引:AK_表名称_列名称。示例：AK_Store_rowguid不唯一非聚集索引：PK_表名称_列名称。示例：IX_Store_SalesPersonID主 XML索引：PXML_表名称_Xml类型列名称。示例：PXML_Store_Demographics备注：以上命名参考Sql Server 2005示例数据库，一般只需设计器自动生成，不需要额外修改。1.4.参数命名1.4.1.数据列参数命名格式为 + 列名称。示例：EmployeeID在列名不符合Pascal样式时（早期遗留系统），例如使用全部大写的列名称，或使用“_”进行连接的字段名称，参数名称定义使用 + 列名称，这里的列名称尽量符合Pascal样式命名。1.4.2.非数据列参数在参数无法跟列名称进行关联时，使用能够反映该参数功能的英文单词或单词组合, 采用Pascal样式命名。示例：ErrorID Flag1.5.常见命名1.5.1.常用字段命名这里的常用字段是指在建表时频繁使用的表名或列名，下表对常用字段进行建议性定义,列名称 数据类型 说明CreatedDate datetime 纪录创建日期，一般使用GETDATE()自动生成ModifiedDate datetime 纪录最后修改日期，首次使用GETDATE()DeletedDate datetime 记录删除（标记删除）日期StartDate datetime 开始日期EndDate datetime 结束日期StartTime datetime 开始时间EndTime datetime 结束时间rowguid uniqueidentifier 唯一标识行的ROWGUIDCOL号，用于支持合并复制ID int 使用ID代替Id或id。一般为自增长主键列ParentID int 父IDStatus int 状态3.SQL编写3.1.大小写大写T-SQL 语言的所有关键字，谓词和系统函数。变量名称及游标名称使用Pascal样式。数据类型定义使用全部小写。示例：DECLARE LastName nvarchar(32);3.2.使用“;”使用“;”作为 Transact-SQL 语句终止符。虽然分号不是必需的，但使用它是一种好的习惯。示例：USE AdventureWorks;GODECLARE find varchar(30);SET find = Man%;SELECT LastName, FirstName, PhoneFROM Person.ContactWHERE LastName LIKE find;3.3.存储格式尽量采用Unicode数据存储格式，提高可移植性和兼容性，实际应用中尽量使用nchar、nvarchar、ntext代替char、varchar、text。3.4.类型选择如果字符具有明确的长度，使用nchar代替nvarchar；char代替varchar。在只有两个可能数值时，使用bit代替int或smallint。在SQL Server 2005中，使用nvarchar(MAX)代替ntext；varchar(MAX)代替text；varbinary(MAX)代替image。在特殊的数据表结构中可考虑xml数据类型，达到事半工倍的效果。3.5.默认值在建立数据表时，尽量使用默认值代替NULL值。比如设置CreatedDate列默认值为GETDATE()。在可行的情况下设置字段为不允许空。3.6.字段长度始终指定字符数据类型的长度，并确保允许用户可能需要的最大字符数，避免超出最大长度时出现字符丢失现象。对于字符型数据，建议采用2的n次方来定义数据长度。示例：nvarchar(32)varchar(64)3.7.使用“”在 T-SQL 代码中为字符常量使用单引号,避免使用双引号。3.8.语句缩进一个嵌套代码块中的语句使用四个空格的缩进。使用Microsoft SQL Server Management Studio ,选择“工具”菜单，打开“选项”菜单，在选项对话框中选择文本编辑器-纯文本-制表符,选中“插入空格单选框”,设置“制表符大小”为4，缩进大小为“4”。3.9.语句换行建议SQL代码每行以关键字或“”开头。示例：SELECT ShiftID ,Name ,StartTime ,EndTime ,ModifiedDateFROM AdventureWorks.HumanResources.Shift3.10. 语句分割使用一个（而不是两个）空行分隔 T-SQL 代码的逻辑块。3.11. 使用“*”尽量避免在任何代码中使用 “SELECT *”。3.12. 表名别名表名别名要简短，但意义要尽量明确。通常使用大写的表名作为别名，使用 AS 关键字指定表或字段的别名。3.13. 类型转换不要依赖任何隐式的数据类型转换，不要假定 T-SQL 会进行必要的转换。例如，把数字变量赋予字符值。相反，在为变量赋值或比较值之前，应使用适当的 CONVERT 函数使数据类型相匹配。3.14. 数值比较不要将空的变量值直接与比较运算符（符号）比较。如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。3.15. 排序决不要依赖 SELECT 语句会按任何特定顺序返回行，除非在 ORDER BY 子句中指定了顺序。通常，应将 ORDER BY 子句与 SELECT 语句一起使用。可预知的顺序（即使不是最方便的）比不可预知的顺序强，尤其是在开发或调试过程中。在返回行的顺序无关紧要的情况下，可以忽略 ORDER BY ，减少资源开销。3.16. Unicode字符串在Unicode字符前面使用N前缀，避免引起数据的不一致。示例：- Assumes the default code page is not GreekCREATE TABLE #t1 (c1 nchar(1)INSERT #t1 VALUES(N)INSERT #t1 VALUES()SELECT * FROM #t1输出结果：c1 - O3.17. BEGIN.END 块在SQL代码快中尽量使用BEGIN.END 语句块，提高代码可阅读性。3.18. TRY块在SQL Server 2005中对一些可能执行失败的语句尽量使用TRY块。Transact-SQL 语句组可以包含在 TRY 块中，如果 TRY 块内部发生错误，则会将控制传递给 CATCH 块中包含的另一个语句组。示例：BEGIN TRY SQL 语句组1END TRYBEGIN CATCH SQL 语句组2END CATCH;3.19. TOP子句在SQL Server 2005中加强了TOP的使用，尽量使用TOP(变量)来减少SQL拼串现象。3.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 TRANCOUNT 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE dbo.uspLogError; END CATCH;3.21. 存储过程在编写存储过程时，使用PROCEDURE 代替 PROC 简写。示例：CREATE PROCEDURE dbo.存储过程名字4.代码注释4.1.代码头部注释在SQL代码块（sql文件或存储过程）的头部进行注释，标注创建人(Author)、创始日期(Create date)、修改信息(Modify n)。格式：- =- Author: - Create date: - Description: - Modify n: - =示例：- =- Author: Zhanghaifeng- Create date: 2006-12-25- Description: H2000报关单回执处理- Modify 1: 郑佐, 2006-12-31, 简化逻辑判断流程- Modify 2: 郑佐, 2007-01-20, 更新条件判断- =注：日期格式使用 yyyy-MM-dd。Modify n n代表修改序号，从1开始，每次修改加1。4.2.TRANSACTION注释建议在每个事务的开头进行注释，说明该事务的功能。- BEGIN TRANSACTION;5.附录A 命名规则常见命名规则有四种样式：完全大写、完全小写、Pascal 大小写和 Camel 大小写。5.1.Pascal 大小写组成标识符的每个单词的首字母大写，其余字母小写的书写约定。对于缩写的双字母单词，要求全部大写。例如：ApplicationException ID5.2.Camel 大小写标识符的首字母小写，每个后面连接的单词的首字母大写，其余字母小写的书写约定。对于缩写的双字母单词，要求它们出现在标识符首部时全部小写，否则全部大写。例如：applicationException id5.3.匈牙利命名法匈牙利命名法由匈牙利程序员发明，他在微软工作了多年，此命名法就是通过微软的各种产品和文档传出来。多数有经验的程序员，不管他们用的是哪门语言，都或多或少在使用它。基本原则：变量名 = 属性 类型 对象描述即一个变量名是由三部分信息组成，这样，程序员很容易理解变量的类型、用途，而且便于记忆。6.附录B 参考资源6.1.Microsoft SQL Server 2005 联机丛书/downloads/details.aspx?displaylang=zh-cn&FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F6.2.SQL Server 2005示例数据库AdventureWorksAdventureWorksDW6.3.编写可移植的 Transact-SQL 代码/china/msdn/library/data/sqlserver/USsqldnsqldevdev_06112004L.mspx6.4.T-SQL 编码标准/china/msdn/library/data/sqlserver/sp04l9.mspx进行 T-SQL 编程时常常会忽略编码标准，但这些标准却是开发小组顺利开展工作的关键工具。这里介绍的编码标准是我多年的开发成果。它们当然还没有得到普遍接受，而且不可否认，有些标准带有主观色彩。我的目的实际上更多的是为了提高大家的意识，而不是吹捧自己是 T-SQL 样式方面的仲裁者：最重要的是要建立某些合理的编码标准并遵循这些标准。您在这篇文章中会发现有关 T-SQL 编程的一系列不同的编码标准、技巧和提示。它们并未以任何特定的优先级或重要性顺序列出。让我们从格式开始。表面上，T-SQL 代码的格式似乎并不重要，但一致的格式可以使您的同事（不论是同一小组的成员还是更大范围的 T-SQL 开发团队的成员）更轻松地浏览和理解您的代码。T-SQL 语句有一个结构，遵循一目了然的结构使您可以更轻松地查找和确认语句的不同部分。统一的格式还使您可以更轻松地在复杂 T-SQL 语句中增删代码段，使调试工作变得更容易。下面是 SELECT 语句的格式示例： SELECT C.Name, E.NameLast, E.NameFirst, E.Number, ISNULL(I.Description,NA) AS DescriptionFROM tblCompany AS CJOIN tblEmployee AS EON C.CompanyID = E.CompanyIDLEFT JOIN tblCoverage AS VON E.EmployeeID = V.EmployeeIDLEFT JOIN tblInsurance AS ION V.InsuranceID = I.InsuranceIDWHERE C.Name LIKE NameAND V.CreateDate CONVERT(smalldatetime, 01/01/2000)ORDER BY C.Name, E.NameLast, E.NameFirst, E.Number, ISNULL(I.Description,NA)SELECT Retain = ERROR, Rows = ROWCOUNTIF Status = 0 SET Status = Retain一个嵌套代码块中的语句使用四个空格的缩进。（上述代码中的多行 SELECT 语句是一个 SQL 语句。）在同一语句中开始新行时，使 SQL 关键字右对齐。将代码编辑器配置为使用空格，而不是使用制表符。这样，不管使用何种程序查看代码，格式都是一致的。大写所有的 T-SQL 关键字，包括 T-SQL 函数。变量名称及光标名称使用混和大小写。数据类型使用小写。表名别名要简短，但意义要尽量明确。通常，使用大写的表名作为别名，使用 AS 关键字指定表或字段的别名。当一个 T-SQL 语句中涉及到多个表时，始终使用表名别名来限定字段名。这使其他人阅读起来更清楚，避免了含义模糊的引用。当相关数字出现在连续的代码行中时（例如一系列 SUBSTRING 函数调用），将它们排成列。这样容易浏览数字列表。使用一个（而不是两个）空行分隔 T-SQL 代码的逻辑块，只要需要就可以使用。声明 T-SQL 局部变量（例如 lngTableID）时，使用适当的数据类型声明和一致的大写。始终指定字符数据类型的长度，并确保允许用户可能需要的最大字符数，因为超出最大长度的字符会丢失。始终指定十进制数据类型的精度和范围，否则，将默认为未指定精度和整数范围。 使用错误处理程序，但要记住行首 (BOL) 中的错误检查示例不会象介绍的那样起作用。用来检查 ERROR 系统函数的 T-SQL 语句 (IF) 实际上在进程中清除了 ERROR 值，无法再捕获除零之外的任何值。（即使示例起作用，它们也只能捕获最后发生的一个错误，而不是您更想捕获的第一个错误。）必须使用 SET 或 SELECT 立即捕获错误代码，如前面示例所示。如果状态变量仍然为零，应转换到状态变量。 避免使用“未声明的”功能，例如系统表中未声明的列、T-SQL 语句中未声明的功能或者未声明的系统存储过程或扩展的存储过程。 不要依赖任何隐式的数据类型转换。例如，不能为数字变量赋予字符值，而假定 T-SQL 会进行必要的转换。相反，在为变量赋值或比较值之前，应使用适当的 CONVERT 函数使数据类型相匹配。另一个示例：虽然 T-SQL 会在进行比较之前对字符表达式进行隐式且自动的 RTRIM，但不能依赖此行为，因为兼容性级别设置非字符表达式会使情况复杂化。不要将空的变量值直接与比较运算符（符号）比较。如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。不要使用 STR 函数进行舍入，此函数只能用于整数。如果需要十进制值的字符串形式，应先使用 CONVERT 函数（转至不同的范围）或 ROUND 函数，然后将其转换为字符串。也可以使用 CEILING 和 FLOOR 函数。使用数学公式时要小心，因为 T-SQL 可能会将表达式强制理解为一个不需要的数据类型。如果需要十进制结果，应在整数常量后加点和零 (.0)。决不要依赖 SELECT 语句会按任何特定顺序返回行，除非在 ORDER BY 子句中指定了顺序。通常，应将 ORDER BY 子句与 SELECT 语句一起使用。可预知的顺序（即使不是最方便的）比不可预知的顺序强，尤其是在开发或调试过程中。（部署到生产环境中之前，可能