对象命名规范
字符范围
英文字母、下划线和数字,首位字符必须是英文字母
禁止使用空格、中横线、保留字
大小写
每个单词的首字母大写
前缀
相关对象的命名或需要特殊标记时
一般与下划线同时使用
存储过程
格式:<Action><Name>
Action:Get,Delete,Update,Select等动词
索引
格式:IX_<TableName>_<Columns>
主(外)键
主键:PK_<TableName>
外键:FK_<TableName1>_<TableName2>
默认值
格式:DF_<TableName>_<ColumnName>
书写规范
层次
脚本有必要的缩进和换行,使用统一的缩进符
大小写
关键字、系统变量名、系统函数名全部大写
对象引用
对象前加owner(一般是dbo)
如果涉及到跨数据库,就需要加上Database名称;已经在数据库中,不用加库名
如:dbo.TableName、DatabaseName.dbo.TableName
不允许出现DatabaseName..TableName
注释
单行注释,用—sqlcode
多行注释,用/sqlcode/
存储过程、视图、用户定义函数必须有合理的注释,至少包括:创建人、创建日期、修改人、修改日期、功能描述、参数说明
字段引用
字段名前需要加上表名(别名)
SELECT语句中不允许出现*
INSERT语句中,必须指定插入列的列表
禁止出现对正式对象的DROP操作
性能相关
WITH(NOLOCK)
SELECT中涉及的表和视图,在非事务和特别的完整性要求的上下文中,使用WITH(NOLOCK),而非NOLOCK
跨服务器查询语句时,可以用WITH(NOLOCK)
尽量利用主键
在UPDATE和DELETE的WHERE子句中,尽量用主键
JOIN
JOIN时注意筛选条件与连接条件的位置
能使用INNER JOIN实现的处理,不要使用外连接
游标
建议使用SELECT语句,或While Loop语句实现相同功能
子查询
注意子查询、临时表和表变量的使用
只返回出必须的列,不要包含与处理需求无关的列(*)
变量
变量赋值:请修改为SELECT TOP 1…
动态T-SQL处理语句中,如果涉及到变量,尽量使用sp_executesql,通过参数传递进行处理
存储过程的起始部分定义Declare语句,这会使查询优化器,Reuse查询计划
数据类型
ntext,text,image数据类型不再允许使用,请用nvarchar(max),varchar(max),varbinary(max),xml
注意隐式转换,使用显示的数据类型转换(CAST或CONVERT),特别是在WHERE语句中
变量、参数的类型要与逻辑运算的字段类型一致
存储过程相关
禁止使用PRINT语句
使用SET NOCOUNT ON/OFF(关闭数据库提示)语句
常用函数
排名函数
RANK()
SELECT RANK() OVER ( ORDER BY PKID DESC ) '排序' , PKID , EmployeeNo , EmployeeName , EmployeeType FROM Employee
DENSE_RANK()
ROW_NUMBER() 分页
WITH TB
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY PKID DESC ) AS 'RowNumber' , PKID , EmployeeNo , EmployeeName , EmployeeType FROM Employee WITH(NOLOCK) ) SELECT PKID , EmployeeNo , EmployeeName , EmployeeType FROM TB WHERE TB.RowNumber BETWEEN 0 AND 5;
参考:http://jingyan.baidu.com/article/597035521ff2ec8fc107404b.html
TOP 分页
返回指定数目的行
SELECT TOP (10)
EmployeeNo ,
EmployeeName ,
EmployeeType
FROM Employee
ORDER BY PKID DESC WITH(NOLOCK)
返回指定百分比的行
SELECT TOP (16) PERCENT
EmployeeNo ,
EmployeeName ,
EmployeeType
FROM Employee WITH(NOLOCK)
WITH TIES参数
--结果可能多余16个
SELECT TOP (16) WITH TIES EmployeeNo , EmployeeName , EmployeeType FROM Employee WITH(NOLOCK) --必须包含ORDER BY语句 ORDER BY PKID --结果可能多余16% SELECT TOP (16) PERCENT WITH TIES EmployeeNo , EmployeeName , EmployeeType FROM Employee WITH(NOLOCK) --必须包含ORDER BY语句 ORDER BY PKID
分页方法1
NOT IN
--获取第21~40个
SELECT TOP 20
PKID
FROM Employee WHERE ( PKID NOT IN ( SELECT TOP 20 PKID FROM Employee ORDER BY PKID ) ) ORDER BY PKID
分页方法2
PKID大于前X个的最大值
--获取第21~40个
SELECT TOP 20
PKID
FROM Employee WHERE ( PKID > ( SELECT MAX(PKID) FROM ( SELECT TOP 20 PKID FROM Employee ORDER BY PKID ) AS T ) ) ORDER BY PKID;
分页函数 OFFSET/FETCH NEXT
跳过指定的行数
--跳过前10个,取后面的全部数据
SELECT PKID
FROM Employee
ORDER BY PKID OFFSET 10 ROWS
跳过指定的行数,再返回指定的行数
SELECT PKID
FROM Employee
ORDER BY PKID OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
注意
OFFSET子句必须与ORDER BY子句组合使用,且不可以与TOP同时使用
ROW和ROWS可以互换使用
FIRST和NEXT可以互换使用
行列互转
行转列PIVOT
--将Name为'费用1'和'费用2'的行转列,统计Money总和
SELECT *
FROM ( SELECT PKID,ChargeDate,ChargeID,Money,Name FROM Charge WHERE IsDelete != 1 ) sc PIVOT ( SUM(Money) FOR Name IN ( [费用1], [费用2] ) ) AS PI;
列转行UNPIVOT
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
FOR XML PATH
功能:将数据以XML格式进行展示
应用场景:合并多行数据显示为一行数据
基本语法:
FOR XML PATH
FOR XML PATH(‘My’) --修改每条记录的最大节点名称(行节点)
FOR XML PATH(‘’)
可以与STUFF函数配合使用
参考资料:http://www.cnblogs.com/wangjingblogs/archive/2012/05/16/2504325.html
其他函数
IIF
语法:IIf(Logical EXPression, Numeric Expression1, Numeric Expression2)
如果 Logical Expression 取值为 TRUE,则此函数返回 Numeric Expression1,否则,返回 Numeric Expression2
某些场合可以用来替换CASE WHEN语句
Eg.
--统计费用表中,单笔费用超过2000元的单数
SELECT SUM(IIF(Money>2000, 1,0)) as number from Charge ---等同于 SELECT SUM(1) AS number FROM Charge WHERE Money>2000
CHOOSE
功能:从参数列表中选择和返回一个值
语法:CHOOSE(index,val1,val2…)
某些场合可以用来替换CASE WHEN语句
Eg.
SELECT CHOOSE(2,PKID,ShopID,Name,Money) FROM Charge WHERE PKID=257
脚本分析与优化
STATISTICS TIME/IO
--从缓冲池中删除所有缓冲区
DBCC DROPCLEANBUFFERS
参考资料:https://msdn.microsoft.com/zh-cn/library/ms187762.aspx
--删除计划缓存中的所有(特定)计划
DBCC FREEPROCCACHE
参考资料:https://msdn.microsoft.com/zh-cn/library/ms174283.aspx
--显示有关由Transact-SQL 语句生成的磁盘活动量的信息
SET STATISTICS IO ON SET STATISTICS IO OFF
先将开关打开,之后执行sql语句,在消息中,就会显示磁盘活动量
Eg.
表 'Charge'。扫描计数 0,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
--显示分析、编译和执行各语句所需的毫秒数
SET STATISTICS TIME ON SET STATISTICS TIME OFF
先将开关打开,之后执行sql语句,在消息中,就会显示分析和编译、执行时间
Eg.
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
查询处理的顺序
![](http://upload-images.jianshu.io/upload_images/9128511-b2bf272261f48e85.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/700)
执行计划
定义:由优化器生成、用于确定如何处理一个给定查询的“工作计划”
功能:
查看脚本的执行阶段
每个运行符相关联的开销百分比,包括IO、CPU等
索引的使用和缺失情况
Eg.
选择要执行的sql语句,右键选择“显示估计的执行计划”,执行sql
![](http://upload-images.jianshu.io/upload_images/9128511-280aa76a58956046.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/700)
提示(Hint)
定义:指定的强制选项或策略,由SQL Server查询处理器针对SELECT/INSERT/UPDATE/DELETE语句执行。提示将覆盖查询优化器可能为查询选择的任何执行计划。
联接提示:LOOP/HASH/MERGE/REMOTE
查询提示:USE PLAN N ‘xml_plan’
表提示:NOLOCK/ROWLOCK/INDEX=(index_value)
索引
优势:索引可以减少I/O开销、加快数据处理速度
弊端:增加数据表的存储空间、延长DML脚本的运行时间
主键索引、聚集索引、非聚集索引、唯一索引四者的区别
组合索引的创建原则
查看索引使用情况
索引碎片分析
常见问题汇总
WITH(NOLOCK)
子查询、临时表、表变量的运用
参数、变量的数据类型
UPDATE/DELETE代码中的WHERE的条件
存储过程的使用
对象(数据库、表、字段)引用格式