MSSQL

数据类型

ntext,text,image数据类型不再允许以,请用nvarchar(max),varchar(max),varbinary(max),xml
在意隐式转换,使用展现的数据类型转换(CAST或CONVERT),特别是在WHERE语句被
变量、参数的路要和逻辑运算的字段类型一致

禁止现身对正规对象的DROP操作

分页方法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

索引

格式:IX_<TableName>_<Columns>

注释

单行注释,用—sqlcode
大多实践注释,用/sqlcode/
仓储过程、视图、用户定义函数必须发合理性之笺注,至少包括:创立人、创造日期、修改人口、修改日期、功效描述、参数表达

提示(Hint)

概念:指定的威吓选项或政策,由SQL
Server查询处理器针对SELECT/INSERT/UPDATE/DELETE语句执行。提醒将挂查询优化器可能为查询选用的任何履行计划。
通提醒:LOOP/HASH/MERGE/REMOTE
查询指示:USE PLAN N ‘xml_plan’
表提示:NOLOCK/ROWLOCK/INDEX=(index_value)

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

储存过程

格式:<Action><Name>
Action:Get,Delete,Update,Select等动词

常用函数

字符范围

英文字母、下划线和数字,首各项字符必须是英文字母
明令禁止采取空格、中横线、保留字

询问处理的逐一

 

图片 1

2

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

名次函数

主(外)键

主键:PK_<TableName>
外键:FK_<TableName1>_<TableName2>

WITH(NOLOCK)

SELECT中涉及的表和视图,在非事务和特其它完整性要求的左右文中,使用WITH(NOLOCK),而未NOLOCK
超服务器查询语句时,能够据此WITH(NOLOCK)

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

层次

本子有必不可少之缩进和换行,使用统一的缩进符

子查询

注意子查询、临时表和表变量的下
只有回去来要的排,不要含与拍卖要求无关的排列(*)
变量
变量赋值:请修改为SELECT TOP 1…
动态T-SQL处理告知句被,如若波及到变量,尽量利用sp_executesql,通过参数传递举办拍卖
储存过程的苗子部分概念Declare语句,这会要查询优化器,Reuse查询计划

科普问题集中

WITH(NOLOCK)
分段查询、临时表、表变量的运用
参数、变量的数据类型
UPDATE/DELETE代码中的WHERE的标准化
囤过程的使
目的(数据库、表、字段)引用格式

CHOOSE

力量:从参数列表中采取跟归一个价
语法:CHOOSE(index,val1,val2…)
少数场面可以用来替换CASE WHEN语句子
Eg.

SELECT CHOOSE(2,PKID,ShopID,Name,Money) FROM Charge WHERE PKID=257

尽可能以主键

当UPDATE和DELETE的WHERE子句被,尽量用主键

默认值

格式:DF_<TableName>_<ColumnName>

分页方法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;

列转行UNPIVOT

table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)

履行计划

概念:由优化器生成、用于确定如何处理一个加以查询的“工作计划”
功能:
查阅脚本的履等
每个运行符相关联的开百分比,包括IO、CPU等
目录的运用与不够失情状
Eg.
挑而执行之sql语句,右键采纳“显示猜测的施行计划”,执行sql

 

图片 2

1

 

性能相关

大小写

重中之重字、系统变量名、系统函数叫全副大写

挥洒规范

字段引用

字段名前用加上表名(别名)
SELECT语句被未同意出现*
INSERT语句被,必须指定插入列的列表

对象引用

目标前加owner(一般是dbo)
如波及到跨数据库,就需添加Database名称;已经当数据库被,不用加库名
如:dbo.TableName、DatabaseName.dbo.TableName
非允许出现DatabaseName..TableName

RANK()

   SELECT  RANK() OVER ( ORDER BY PKID DESC ) '排序' ,
            PKID ,
            EmployeeNo ,
            EmployeeName ,
            EmployeeType
    FROM    Employee

分页函数 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可以换使用

JOIN

JOIN时只顾筛选标准及连接条件的岗位
能够拔取INNER JOIN实现的拍卖,不要以外接连

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

队互转

游标

指出以SELECT语句,或While Loop语句子实现平等效果

剧本分析以及优化

储存过程有关

明令禁止采纳PRINT语句子
使SET NOCOUNT ON/OFF(关闭数据库提醒)语句

前缀

系对象的命名或欲极度标记时
诚如与下划线同时利用

DENSE_RANK()

大小写

每个单词的首字母大写

行转列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;

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

其余函数

靶命名规范

索引

优势:索引可以减小I/O开销、加快数据处理速度
坏处:增添数据表的囤空间、延长DML脚本的周转时
主键索引、聚集索引、非聚集索引、唯一索引四者的别
重组索引的创导标准
查阅索引使用意况
目碎片分析

相关文章