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 毫秒。

查询处理的顺序

执行计划

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

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

MSSQL

标签：ber   cpu   占用   ash   serve   XML   dex   选项   lib