对象命名规范

字符范围

英文字母、下划线和数字，首位字符必须是英文字母禁止使用空格、中横线、保留字

大小写

每个单词的首字母大写

前缀

相关对象的命名或需要特殊标记时一般与下划线同时使用

存储过程

格式：<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_sourceUNPIVOT(value_columnFOR pivot_columnIN(<column_list>))

FOR XML PATH

功能：将数据以XML格式进行展示应用场景：合并多行数据显示为一行数据基本语法：FOR XML PATHFOR 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 毫秒。

查询处理的顺序

  2

执行计划

定义：由优化器生成、用于确定如何处理一个给定查询的“工作计划”功能：查看脚本的执行阶段每个运行符相关联的开销百分比，包括IO、CPU等索引的使用和缺失情况Eg.选择要执行的sql语句，右键选择“显示估计的执行计划”，执行sql

  1

 

提示（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的条件存储过程的使用对象（数据库、表、字段）引用格式

转载于:https://www.cnblogs.com/Lulus/p/7905305.html

相关资源：mssql索引优化工具