TSQL编程规范sqlserver.docx
- 文档编号:6217224
- 上传时间:2023-01-04
- 格式:DOCX
- 页数:13
- 大小:22.63KB
TSQL编程规范sqlserver.docx
《TSQL编程规范sqlserver.docx》由会员分享,可在线阅读,更多相关《TSQL编程规范sqlserver.docx(13页珍藏版)》请在冰豆网上搜索。
TSQL编程规范sqlserver
Sql2005的Transact-SQL编程规范
1. 基本原则
以大小写敏感编写SQL语句。
尽量使用Unicode数据类型。
优先使用连接代替子查询或嵌套查询。
尽量使用参数化SQL查询代替语句拼接SQL查询。
禁止使用[拼音]+[英语]的方式来命名SQL对象或变量。
2. 命名规范
在一般情况下,采用Pascal样式或Camel样式命名数据库对象,使在开发基于数据库应用程序的时候通过ORM工具生成的数据访问代码更好的符合命名规范。
另外,关系型数据库同Xml结合得越来越紧密,规范的命名很有必要。
在实际数据库开发过程中,如果需求方已经提供数据库设计方案,以提供的方案为准;在原有数据库上进行升级开发时,在可行的情况下可适当做出设计调整以符合编程规范。
1.1.对象命名
1.1.1. 数据库
第一种方式,采用Pascal样式命名,命名格式为[项目英文名称]。
示例:
AdventureWorks
第二种方式,采用Pascal样式命名,命名格式为[项目英文名称]+Db。
示例:
AdventureWorksDb
BizTalkRuleEngineDb
建议采用第一种方式。
1.1.2. 关系型数据仓库
采用Pascal样式命名,命名格式为[项目英文名称]+DW。
示例:
AdventureWorksDW
1.1.3. 数据架构
除SQLServer系统定义的数据架构外,新建架构采用Pascal样式命名,命名格式为[架构名]。
示例:
HumanResources
Production
对数据库对象Table,View,Procedure,Function等使用数据架构进行归类。
在SQLServer2000中dbo为默认架构。
1.1.4. 数据表
采用Pascal样式命名,命名格式为[表名]。
示例:
Employee
Product
表名以英文单数命名。
典型的例子,使用Product,而不是Products。
1.1.5. 数据视图
视图名称采用Pascal样式命名,命名格式为v+[视图名称]。
示例:
vEmployee
vSalesPerson
1.1.6. 数据列
列名称命名采用英文单词或缩写,英文单词只来自于具体业务定义,尽量表达清楚含义。
采用Pascal样式命名,命名格式为[列名称]。
示例:
AddressID
PostalCode
尽量避免使用拼音命名,如果不可避免,对于比较短的列名,采用拼音全写,如果拼音列名比较复杂,可以采用首个字用全拼,其它字用首字母大写表示。
示例:
宁波Ningbo
经营方式JingYFS
1.1.7. 存储过程
建议采用Pascal样式命名,命名格式为[存储过程名称]。
示例:
GetUser
AddUser
1.1.8. 函数
自定义函数采用Pascal样式命名,命名格式为[函数名],系统函数使用全部大写。
示例:
SELECTISNULL(@LastName,'Unknownlastname');
GETDATE()
1.1.9. 用户定义数据类型
采用Pascal样式命名,命名格式为[自定义数据类型名称]。
示例:
Flag
NameStyle
1.1.10. 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.1.11. DDL触发器
响应各种数据定义语言(DDL)事件而激发。
这些事件主要与以关键字CREATE、ALTER和DROP开头的Transact-SQL语句对应。
执行DDL式操作的系统存储过程也可以激发DDL触发器。
采用Camel样式命名,命名单词能够描述DDL触发器功能。
示例:
CREATETRIGGERsafety
ONDATABASE
FORDROP_TABLE,ALTER_TABLE
AS
PRINT'YoumustdisableTrigger"safety"todroporaltertables!
'
ROLLBACK
另外一种方式为添加dll前缀,
示例:
CREATETRIGGER[ddlDatabaseTriggerLog]
ONDATABASE
FORDDL_DATABASE_LEVEL_EVENTS
AS
1.1.12. 主键、外键关系和索引
主键:
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
不唯一非聚集索引:
PK_[表名称]_[列名称]。
示例:
IX_Store_SalesPersonID
主XML索引:
PXML_[表名称]_[Xml类型列名称]。
示例:
PXML_Store_Demographics
备注:
一般只需设计器自动生成,不需要额外修改。
1.2.参数命名
1.2.1. 数据列参数
命名格式为@+[列名称]。
示例:
@EmployeeID
在列名不符合Pascal样式时(早期遗留系统),例如使用全部大写的列名称,或使用“_”进行连接的字段名称,参数名称定义使用@+[列名称]。
1.2.2. 非数据列参数
在参数无法跟列名称进行关联时,使用能够反映该参数功能的英文单词或单词组合,采用Pascal样式命名。
示例:
@ErrorID
@Flag
1.3.常用字段命名
这里的常用字段是指在建表时频繁使用的表名或列名,下表对常用字段进行建议性定义,
列名称
数据类型
说明
CreatedDate
datetime
纪录创建日期,一般使用GetDate()自动生成。
ModifiedDate
datetime
纪录最后修改日期,首次使用GetDate()
rowguid
uniqueidentifier
唯一标识行的ROWGUIDCOL号,用于支持合并复制示例。
ID
int
使用ID代替Id或id。
一般为自增长主键列。
3. SQL编写
3.1.大小写
大写所有的T-SQL关键字,包括T-SQL函数。
变量名称及游标名称使用Pascal样式。
数据类型定义使用全部小写。
示例:
DECLARE@LastNamenvarchar(32);
3.2.使用“;”
使用“;”作为Transact-SQL语句终止符。
虽然分号不是必需的,但使用它是一种好的习惯。
示例:
USEAdventureWorks;
GO
DECLARE@findvarchar(30);
SET@find='Man%';
SELECTLastName,FirstName,Phone
FROMPerson.Contact
WHERELastNameLIKE@find;
3.3.存储格式
尽量采用Unicode数据存储格式,提高可移植性和兼容性,实际应用中使用nchar、nvarchar、ntext代替char、varchar、text。
3.4.类型选择
如果字符具有明确的长度,使用nchar代替nvarchar;char代替varchar。
在只有两个可能数值时,使用bit代替int或smallint。
在特殊的数据表结构中可考虑xml数据类型。
3.5.默认值
在建立数据表时,尽量使用默认值代替NULL值。
比如设置CreatedDate列默认值为GETDATE()。
在可避免的情况下设置字段为不允许空。
3.6.字段长度
始终指定字符数据类型的长度,并确保允许用户可能需要的最大字符数,避免超出最大长度时出现字符丢失现象。
对于字符型数据,建议采用2的n次方来定义数据长度。
示例:
nvarchar(32)
varchar(64)
3.7.使用“'”
在T-SQL代码中为字符常量使用单引号,避免使用双引号。
3.8.语句缩进
一个嵌套代码块中的语句使用四个空格的缩进。
在同一语句中开始新行时,使SQL关键字右对齐。
将代码编辑器配置为使用空格,而不是使用制表符。
这样,不管使用何种程序查看代码,格式都是一致的。
3.9.语句换行
建议SQL代码每行以关键字或“'”开头。
示例:
SELECT[ShiftID]
,[Name]
,[StartTime]
,[EndTime]
,[ModifiedDate]
FROM[AdventureWorks].[HumanResources].[Shift]
3.10. 语句分割
使用一个(而不是两个)空行分隔T-SQL代码的逻辑块。
3.11. 使用“*”
尽量避免在任何代码中使用“SELECT*”。
3.12. 表名别名
表名别名要简短,但意义要尽量明确。
通常使用大写的表名作为别名,使用AS关键字指定表或字段的别名。
3.13. 类型转换
不要依赖任何隐式的数据类型转换,不要假定T-SQL会进行必要的转换。
例如,而把数字变量赋予字符值。
相反,在为变量赋值或比较值之前,应使用适当的CONVERT函数使数据类型相匹配。
3.14. 数值比较
不要将空的变量值直接与比较运算符(符号)比较。
如果变量可能为空,应使用ISNULL或ISNOTNULL进行比较,或者使用ISNULL函数。
3.15. 排序
决不要依赖SELECT语句会按任何特定顺序返回行,除非在ORDERBY子句中指定了顺序。
通常,应将ORDERBY子句与SELECT语句一起使用。
可预知的顺序(即使不是最方便的)比不可预知的顺序强,尤其是在开发或调试过程中。
在返回行的顺序无关紧要的情况下,可以忽略ORDERBY的开销。
3.16. Unicode字符串
在Unicode字符前面使用N前缀,避免引起数据的不一致。
示例:
--AssumesthedefaultcodepageisnotGreek
CREATETABLE#t1(c1nchar
(1))
INSERT#t1VALUES(N'Ω')
INSERT#t1VALUES('Ω')
SELECT*FROM#t1
输出结果:
c1
----
Ω
O
3.17. BEGIN...END块
在SQL代码快中尽量使用BEGIN...END语句块,提高代码可阅读性。
3.18. TRY块
在SQLServer2005中增加TRY块,Transact-SQL语句组可以包含在TRY块中。
如果TRY块内部发生错误,则会将控制传递给CATCH块中包含的另一个语句组。
示例:
BEGINTRY
SQL语句组1
ENDTRY
BEGINCATCH
SQL语句组2
ENDCATCH;
3.19. TOP子句
尽量使用TOP(变量)来减少SQL拼串的使用。
3.20. TRANSACTION编写
只要在例程中使用多个数据库修改语句,包括在一个循环中多次执行一个语句,就应考虑声明显式事务。
实例:
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@@TRANCOUNT>0
BEGIN
ROLLBACKTRANSACTION;
END
EXECUTE[dbo].[uspLogError];
ENDCATCH;
3.21. 存储过程
在编写存储过程时,使用PROCEDURE代替PROC简写。
示例:
CREATEPROCEDURE[dbo].[存储过程名字]
4. 代码注释
4.1.代码头部注释
在SQL代码块(sql文件或存储过程)的头部进行注释,标注创建人(Author)、创始日期(Createdate)、修改信息(Modify[n])。
格式:
--=============================================
--Author:
--Createdate:
--Description:
--Modify[n]:
--=============================================
示例:
--================================================
--Author:
Zhanghaifeng
--Createdate:
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注释
建议在每个事务的开头进行注释,说明该事务的功能。
--
BEGINTRANSACTION;
5. 附录1:
命名规则
常见命名规则有四种样式:
完全大写、完全小写、Pascal大小写和Camel大小写。
5.1.Pascal大小写
组成标识符的每个单词的首字母大写,其余字母小写的书写约定。
对于缩写的双字母单词,要求全部大写。
例如:
ApplicationException
ID
5.2.Camel大小写
标识符的首字母小写,每个后面连接的单词的首字母大写,其余字母小写的书写约定。
对于缩写的双字母单词,要求它们出现在标识符首部时全部小写,否则全部大写。
例如:
applicationException
id
5.3.匈牙利命名法
匈牙利命名法由匈牙利程序员发明,他在微软工作了多年,此命名法就是通过微软的各种产品和文档传出来。
多数有经验的程序员,不管他们用的是哪门语言,都或多或少在使用它。
基本原则:
变量名=属性+类型+对象描述
即一个变量名是由三部分信息组成,这样,程序员很容易理解变量的类型、用途,而且便于记忆。
附录2:
优化技巧
T-SQL脚本
∙使用SET对变量赋值,不使用SELECT
∙对于SELECT/UPDATE语句必须显示的定义所有的列,避免使用星号
∙在执行SELECT/INSERT/UPDATE/DELETE语句时,请考虑执行规划的重用,考虑用SP-EXECUTESQL存储过程
∙优先使用SELECT...INTO,然后使用INSERT...SELECT,以避免大量死锁
∙如果需要删除所有的数据,用TRUNCATETABLE代替DELETE
∙避免使用DISTINCT语句
∙如果你需要有限的记录,通过TOPN代替SETROWCOUNT来控制排序取值
∙避免使用SARGABLE的语句在WHERE子句,比如:
OR,<>,!
=,!
<,>!
ISNULL,NOT,NOTIN,NOTLIKE和LIKE,因为这些操作很难利用已知的索引
∙避免使用NOTIN,可以采用IN,EXISTSNOTEXISTS和LEFTJOIN加空值判断
--NOTEXISTS,效率最高
SELECTa.hdr_key
FROMhdr_tbla
WHERENOTEXISTS(SELECT*FROMdtl_tblbWHEREa.hdr_key=b.hdr_key)
--LEFTJOIN
SELECTa.hdr_key
FROMhdr_tbla
LEFTJOINdtl_tblbONa.hdr_key=b.hdr_key
WHEREb.hdr_keyISNULL
--NOTIN,效率最低
SELECThdr_key
FROMhdr_tbl
WHEREhdr_keyNOTIN(SELECThdr_keyFROMdtl_tbl)
∙使用EXISTS判断记录是否存在
--不好的写法:
IF(SELECTCOUNT(*)FROMtable_nameWHEREcolumn_name='xxx')
--正确的写法:
IFEXISTS(SELECT*FROMtable_nameWHEREcolumn_name='xxx')
∙避免在GROUPBY中使用HAVING语句
∙GROUPBY的语句要尽量简单,不要进行GROUPBY语句的嵌套,避免在GROUPBY中包含多余的列
∙考虑在GROUPBY的列,进行ORDERBY排序,特别在多用户的环境下.
∙如果需要在一个包含JOIN的SELECT语句进行GROUPBY,请考虑用子查询代替JOIN.如果必须使用GROUPBY,GROUPBY的应该列在同一张表
∙如果WHERE条件语句有多个AND条件,请确保至少有一个列有索引,如果没有可以建立多列复合INDEX
∙对于SQL无法执行自动优化的WHERE条件语句,可以通过HINTS显示的制定INDEX来提高查询的效率
--可能不好的写法:
SELECT*FROMtblTaskProcesses
WHEREnextprocess=1
ANDprocessidIN(8,32,45)
--正确的写法:
SELECT*FROMtblTaskProcesses(INDEX=IX_ProcessID)
WHEREnextprocess=1
ANDprocessidIN(8,32,45)
∙尽可能避免在WHERE条件语句中使用函数计算
--不好的写法:
WHERESUBSTRING(firstname,1,1)='m'
--正确的写法:
WHEREfirstnamelike'm%'
∙在WHERE条件语句中,避免在函数中包列,如果无法避免,请考虑在该列建立INDEX
--不好的写法:
SELECTmember_number,first_name,last_name
FROMmembers
WHEREDATEDIFF(yy,datofbirth,GETDATE())>21
--正确的写法:
SELECTmember_number,first_name,last_name
FROMmembers
WHEREdateofbirth ∙在WHERE条件语句中,避免使用NOT --不好的写法: WHERENOTcolumn_name>5 --正确的写法: WHEREcolumn_name<=5 ∙在WHERE条件语句中,推荐使用10位的日前函数
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- TSQL 编程 规范 sqlserver