Microsoft SQL Server 2008技术内幕：T-SQL查询 笔记
Microsoft SQL Server 2008技术内幕：T-SQL查询 笔记
目录
F
F
F
F
F
F
F
F
F
第一章 逻辑查询处理
F
F
F
outer join将保留表中没有找到匹配的行
F
在sql中谓词表达式的可能取值为true，false，unknown，这就是所谓的三值逻辑，是sql特有的属性
unknown的逻辑组通常是null
在check约束中unknown值实际被当作true对待，例如salary列的值必须大于0，向该表插入salary为null的行时可以成功，因为（null>0）等于unknown，在check约束中按true来处理
在unique约束，集合运算union和except，排序，分组操作中，认为两个null值是相等的
总之，为了避免以后出现麻烦，先了解一下unknown逻辑结果和null在不同语言元素中被处理的方式是有好处的
F
使用count(O.orderid)而不是count(*)因为是外联接，count(*)会把外部行也统计在内
使用count(O.orderid)将忽略null值
F
[
TOP (expression) [PERCENT]
[ WITH TIES ]
]
http://msdn.microsoft.com/zh-cn/library/ms189463.aspx
http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html
比如现在有一些数据. col 1 3 3 2 如果此时,我要取 top 3 col order by col 那结果就是1 2 3 只取一个3 如果在top里加上with ties时, 如果top N中的数还有相等的.也取出来. 所以如果在top 3 中也加上with ties的话,那就取1 2 3 3 两个3都取出来.
F
--在order by里指定select列表的位置序号,并不推荐这种做法
SELECT * FROM [dbo].[aaa] ORDER BY 2,1
F
表表达式包括：视图，内联表值函数，派生表，公用表表达式CTE
F
sqlserver允许 在同时指定了top选项的视图中使用order by子句，但因为这个top查询用于定义一个表表达式，所以这里的order by子句将只服务于top的逻辑含义
order by子句认为两个null是相等的，也就是说，所有的null值会排序在一起，至于null值比已知值大还是小，tsql认为null值比已知值要小（排序时排在前面）
apply，pivot，unpivot不是ansi标准运算符，他们是tsql中特有的扩展
F
表运算符
apply运算符
F
F
pivot
不带else子句的case表达式相当于包含一个隐式的ELSE NULL
F
UNPIVOT
F
OVER子句
over子句是四个分析排名函数必须要求的元素，row_number,rank,dense_rank,ntile
F
over子句会定义数据行的一个窗口，在这个窗口上可以执行聚合或排名函数计算
--使用开窗函数over就不需要在group by，也不需要在group by后面包含select列表的列 USE [AdventureWorks] SELECT orderid , custid , COUNT(*) OVER ( PARTITION BY custid ) AS numorders FROM sales.[SalesOrderDetail] WHERE shipcountry = 'spain'
也可以用在order by
--对订单总是降序排序 USE [AdventureWorks] SELECT orderid , custid , COUNT(*) OVER ( PARTITION BY custid ) AS numorders FROM sales.[SalesOrderDetail] WHERE shipcountry = 'spain' ORDER BY COUNT(*) OVER ( PARTITION BY custid ) DESC
F
集合运算符：union all，union，except，intersect 对应于数据集合论中的运算
F
intersect：返回在两个输入中都出现过的不同行
http://www.2cto.com/database/201307/231522.html
EXCEPT 运算符的集合是没有重复元素的，从第一个聚合中返回不在第二个集合中出现的所有不重复的行
--下面查询返回没有下过订单的客户 SELECT CUSTID FROM customer EXCEPT SELECT CUSTID FROM ORDERS
参与集合运算的两个输入中的相应位置上的列数据类型必须匹配或可以隐式转换，就像两个sql语句的custid都是int类型
F
第二章 集合论和谓词逻辑
数学
F
sqlserver提供了两种实数类型：real和float，不过这些数据类型都不是严格数学意义上的实数系统
F
前面说到sqlserver为数字提供的数据类型都不是严格数学意义上的实数系统，这里用一段代码来演示real类型确实不能真实地表示实数
DECLARE @a REAL= 0.001; DECLARE @b REAL= 9876543; DECLARE @c REAL= 1234567; SELECT @a * ( @b * @c ) AS [a(bc)] , ( @a * @b ) * @c AS [(ab)c] a(bc) (ab)c 1.219325E+10 1.219326E+10
注意到两个乘积应该是相等的，但对于sqlserver中实数和算术的表示，这两个式子则不相等，sqlserver在这一点上没有任何错误，
这样的结果符合浮点算术运算的重要IEEE标准，但数字的计算机表示对算术运算来说不是真实的，虽然他们可以满足大多数要求，但不能为所有问题提供“正确的”数学答案
就现有真实表示来说，和sqlserver为数字和算术运算提供的表示相比，sqlserver为数学集合以及运算的表示提供了相当多的真实性
罗素驳轮
F
有序对，元祖，笛卡儿积（cartesian product）
F
空集，交换律
F
集合的特征，集合的基数（Cardinality）
F
字母顺序，比较运算符都采用小于号<，这和数字比较一样，dejan<Itzik
在tsql中通过指定排序规则collation来显式应用某种语言规则
从输出可以看到，按字母顺序对一组名字( 'deszsSmetch' ),( 'desemeck' ),( 'Deszmetch' ), ( 'deszmetch' )进行排序，并没有一种唯一正确的方法
DECLARE @Names TABLE ( NAME VARCHAR(20) ); INSERT INTO @Names ( [NAME] ) VALUES ( 'deszsSmetch' ), ( 'desemeck' ), ( 'Deszmetch' ), ( 'deszmetch' ) SELECT [name] , RANK() OVER ( ORDER BY name COLLATE Latin1_General_BIN ) AS [lat..bin] , RANK() OVER ( ORDER BY name COLLATE Traditional_Spanish_CI_AS ) AS [tra..ci_as] , RANK() OVER ( ORDER BY name COLLATE Hungarian_CI_AS ) AS [hun..ci_as] , RANK() OVER ( ORDER BY name COLLATE Latin1_General_CS_AS ) AS [lat..cs_as] FROM @Names ORDER BY [NAME] COLLATE Latin1_General_BIN; name lat..bin tra..ci_as hun..ci_as lat..cs_as Deszmetch 1 2 2 3 desemeck 2 1 1 1 deszmetch 3 2 2 2 deszsSmetch 4 4 4 4
三分律
F
sql既为基数提供了相应的函数COUNT，也为顺序位置提供了相应的函数（row_number,rank,dense_rank）
集合运算符
如果L的元素按其xcol值进行排序，@x是xcol列中的某个值，则所有@x在L中的位置可以通过以下查询得到
WITH T AS ( SELECT ROW_NUMBER() OVER ( ORDER BY xcol ) AS rn , xcol FROM L ) SELECT RN FROM [T] WHERE XCOL = @X
F
集合论的推广
谓词逻辑
不过，要避免结果集中包含重复行则更困难，这需要把select语句的含义修改成目前使用的select dstinct语句，同时也会带来其他的复杂性，尤其是在进行聚合操作时，和大多数SQL语言一样，T-SQL在很多情况下都支持重复集合，但不总是这样，例如，TSQL不支持 except all和intersect all，只支持except distinct和intersect distinct
SELECT * FROM [dbo].[customer] EXCEPT DISTINCT SELECT * FROM [dbo].[employees]
谓词逻辑是一种数学框架，用于表示和处理结果为真或假的表达式：事实或假象
注意：真假值表达式称为布尔boolean表达式（根据逻辑学家George Boole 佐治布尔命名），布尔逻辑最初就是从研究布尔表达式而发展起来的
F
命题和谓词
形式逻辑（formal logic）
命题和谓词，谓词是包含一个或多个变量的命题，谓词是参数化的命题，命题和谓词都是布尔表达式
F
TSQL中布尔表达式
where ，on，having，check约束，if，while，case when
究竟是命题还是谓词
第43任美國總統
任期
2001年1月20日－2009年1月20日
http://zh.wikipedia.org/zh-hk/乔治·沃克·布什
F
量化语句
F
逆向思维
F
替代和推广
布尔代数：使用bit类型的1，0代替true，false
整数运算符：&,-,^|（与，非，异或，或）用于位运算
sqlserver的整数数据类型bit就经常用于逻辑计算，sqlserver提供了几种整数运算符（& ，- ，^ ， |）
可以用于位运算，或者对组成整数内部表示的各个位单独进行运算，简单的说，这四种运算符分别相当于 与，非，异或，或
可能和你想的一样
三值逻辑
TSQL支持三值逻辑：true，false，unknown
F
模糊逻辑
F
字符串varchar（20）和int类型的比较，字符串varchar（20）和字符串varchar（20）的比较
DECLARE @X VARCHAR(10) DECLARE @Y INT DECLARE @Z VARCHAR(10) SET @X = '1000' SET @Y = '2000' SET @Z = '3000' SELECT CASE WHEN @X < @Y THEN 'TRUE' ELSE 'FALSE' END AS [X<Y?] , CASE WHEN @Y < @Z THEN 'TRUE' ELSE 'FALSE' END AS [Y<Z?] , CASE WHEN @X < @Z THEN 'TRUE' ELSE 'FALSE' END AS [X<Z?] --------------------------------------------- DECLARE @X VARCHAR(10) DECLARE @Y INT DECLARE @Z VARCHAR(10) SET @X = '1000' SET @Y = '2000' SET @Z = '3000' SELECT CASE WHEN @X < @Y THEN 'TRUE' ELSE 'FALSE' END AS [CAST(X)<Y?] , CASE WHEN @Y < @Z THEN 'TRUE' ELSE 'FALSE' END AS [Y)<CAST(Z)?] , CASE WHEN @X < @Z THEN 'TRUE' ELSE 'FALSE' END AS [X<Z?] , CASE WHEN CAST(@X AS INT) < CAST(@Z AS INT) THEN 'TRUE' ELSE 'FALSE' END AS [CAST(X)<CAST(Z)?]
F
F
F
嵌套循环nested loops 运算符上的警告符号表明有一个没有join谓词的联接，当查询中包含cross join运算符时，总会出现这种警告，其实没有什么实质性的警告
no Join predicate
warning
F
第三章 关系模型
F
元组的定义：一个元组是其属性的集合，每个属性由三个部分代表：属性的名称，属性的类型，属性值
F
用户定义类型
F
关系模型快速摘要
表代表关系，关系数据库中的所有信息都存储在表中，而关系则代表现实世界中的实体，此外，关系的元祖代表命题，关系的标题则代表谓词
F
关系和基于关系的运算符共同组成了所谓的关系代数（relational algebra）
基本运算符
F
任何关系运算的结果还是一个关系，这一事实称为关系代数的闭包（closure）性质
codd最初定义的8个关系运算符：限制restrict，投影project，积product，并union，交intersect，减minus，联接join，除divide
投影 project：计算标量和sql中的select列表相对应，其实就是select出来的字段
https://www.cnblogs.com/lyhabc/articles/3960373.html
F
F
F
F
F
图论，欧拉
哥尼斯堡七桥
F
F
F
TSQL支持
除divide，tcolse，unpack，pack关系运算符还没直接等效的TSQL运算符
F
数据完整性
F
F
F
sql2008支持两种DML触发器：instead of触发器和after 触发器
ddl触发器：after触发器
F
instead of触发器可以更新join视图
默认值：default，time stamp，自增序列
各约束规则的执行顺序
检查schema（更新对表的schema是否有效）
检查数据类型
在执行真实的语句之前触发instead of触发器
应用default约束
检查能否为null
检查primary key和unique约束
实施foreign key和check约束
执行语句
触发after触发器
F
应该不应该使用null
三值逻辑
F
建模ORM和范式
F
F
F
F
F
F
反范式
从第一到第六范式
第六范式：时间数据
F
一般化和特殊化
F
为了解决冗余，可以使用超类型supertype
F
当无论如何定义两个关系，他们都不能表达同一事实时，就找到了最一般的超类型，一种更正式的定义是说，数据库应该符合正交设计准则（prnciple of orthogonal design）
F
第四章 查询优化
F
DBA关注系统的技术层面，如资源队列，资源利用率等
平均每个磁盘转轴上有多少个命令在等待，缓存命中率多少，阻塞，cpu利用率，缓存中数据页的平均停留时间
优化方向
分析实例级的等待
关联等待和队列
确定方案
细化到数据库/文件级
细化到进程级
优化索引/查询
F
sql2008引入了一个称为数据收集器（data collector）的组件
数据收集器会安装三个系统数据收集组（system data collection set）分别用于收集磁盘使用情况，服务器活动，以及查询统计信息
第一步分析实例级等待
sys.[dm_os_wait_stats]视图从sql实例启动到目前为止累计值，你可以执行DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)清空累计
DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR)
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.[dm_os_wait_stats] ORDER BY [wait_type]
F
如果使用大量的特定查询，而不是用存储过程和预先编译好的语句，就会因为这些特定的执行计划而消耗大量的内存，
CMEMTHREAD等待类型非常高（当某任务正在等待线程安全的内存对象时出现这种等待）
CMEMTHREAD:COMPILE MEMORY THREAD 编译 内存 线程
当sqlserver不能足够快写事务日志，会看到WRITELOG等待
在tempdb里面，PAGE_LATCH_UP出现较高的值，表示内部结构（IAM,GAM,SGAM,PFS页面）上出现了争用
F
分离重量级等待
从系统最后一次重启或计数器清空以来，该等待类型的总等待时间（以秒为单位）
该类型的等待时间占总等待时间的百分比
从最重量级的等待类型到当前等待类型的连续百分比
信号等待时间占等待时间的百分比（记住：wait_time_ms 包含signal_wait_time_ms）
--累计总和达到系统总等待时间80%的等待类型，而且至少要返回按等待时间排名前5位的等待 WITH Waits AS ( SELECT [wait_type] , [wait_time_ms] / 1000. AS waittime_s , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn , 100. * [signal_wait_time_ms] / [wait_time_ms] AS signal_pct FROM sys.[dm_os_wait_stats] WHERE [wait_time_ms] > 0 AND [wait_type] NOT LIKE '%sleep%' AND [wait_type] NOT LIKE '%idle%' AND [wait_type] NOT LIKE '%queue%' AND [wait_type] NOT IN ( 'clr_auto_event', 'request_for_deadlock_search', 'sqltrace_buffer_flush' ) ) SELECT [w1].[wait_type] , CAST([w1].[waittime_s] AS NUMERIC(12, 2)) AS 'wait_time_s 从系统最后一次重启或计数器清空以来，该等待类型的总等待时间' , CAST([w1].[pct] AS NUMERIC(5, 2)) AS 'running_pct该类型的等待时间占总等待时间的百分比' , CAST([w1].[signal_pct] AS NUMERIC(5, 2)) AS 'signal_pct信号等待时间占等待时间的百分比' FROM [Waits] AS w1 JOIN [Waits] AS w2 ON [w2].[rn] <= [w1].[rn] GROUP BY [w1].[rn] , [w1].[wait_type] , [w1].[waittime_s] , [w1].[pct] , [w1].[signal_pct] HAVING SUM([w2].[pct]) - [w1].[pct] < 80 --percentage threshold OR [w1].[rn] <= 5 ORDER BY [w1].[rn]
pct=percentage的缩写
F
使用数据收集收集等待信息
系统就会自动将等待统计信息收集到管理数据仓库中
F
使用自定义函数，添加一个时间间隔来收集等待信息
使用excel2007数据透视表和SSAS来分析等待数据
F
pageiolatch_sh等待类型代表读取操作的I/O等待时间
F
I/O闩锁的例子：每隔4小时运行一个存储过程sp_updatestats，把这个作业删除，一切正常这个例子在sql2005 tsql查询里面都有
性能监视器中正在等待的I/O命令不应该超过两个，缓存命中率应该尽可能要高
sqlserver：buffer manager对象的“page life expectancy”计数器可以告诉你没有被引用的页面在缓冲池中平均停留多少秒，
如果值较低，表示增加内存可以让页面长时间缓存在内存，值较高表示增加内存也没有用
通常来说，page life expectancy大于几百的值表示状态良好
F
sql2008将一些重要的性能计数器（包括一般的操作系统和sqlserver实例的计数器）都作为server activity收集组的一部分，如果你喜欢收集这些信息，可以使用性能监视器或者使用DMV
SELECT * FROM sys.[dm_os_performance_counters]
sys.[dm_os_performance_counters] 这个DMV没有提供较一般的操作系统计数器，例如cpu利用率，I/O队列，必须使用C#来收集这些计数器来分析
F
确定行动方案
和I/O相关的等待，包括I/O闩锁和写日志等待
并行等待cxpacket是并行查询计划中的等待，线程等待来自其他线程的交换包cxpacket：exchange packet才可以继续工作，
可能表示服务器cpu资源的使用不是最优的，尤其在oltp系统，并行等待值较高也可能使用超线程技术而引起的
降低最大并行度不一定能改善系统的吞吐量！！
细化到数据库/文件级别
使用SELECT * FROM sys.[dm_io_virtual_file_stats](DB_ID(),1)DMF来查看文件的状态
SELECT * FROM sys.[dm_io_virtual_file_stats](DB_ID(),1)
[sample_ms]：sqlserver实例自启动以来的毫秒数，可以用于比较从该函数返回的不同输出
[io_stall_read_ms]：用户在该文件上发出等待读取所用的总时间，以毫秒为单位
[io_stall]：用户在该文件上等待I/O完成的总时间，以毫秒为单位
[size_on_disk_bytes]：以字节为单位
[file_handle]：用于该文件的Microsoft Windows文件句柄，根据文件句柄来找到文件？？
这些值只显示文件的物理I/O,不是逻辑I/O
F
F
我在系统中启用了数据收集器和三个系统收集组，他们会将信息存储在管理数据仓库中，默认情况下，收集频率是60秒
事务日志是顺序写的，不是随机写的
tempdb存储临时表，表变量，spool，工作表，行版本，触发器，联机索引，mars
有两个系统收集组可以收集有关I/O的信息，server activity收集组负责收集一些关于I/O的性能计数器和查询[dm_io_virtual_file_stats]这个dmv，
[dm_io_virtual_file_stats]
disk usage收集组负责收集来自目录视图的
sys.[database_files] sys.[partitions] sys.[allocation_units] sys.[internal_tables]
DBCC SQLPERF(LOGSPACE)
DBCC SQLPERF(LOGSPACE)的信息
在sql2008还可以通过配置报表，如服务器活动历史记录和磁盘使用情况摘要
F
细化到进程级别
使用sqlserver profiler来调优
使用sqlserver profiler的注意事项
statement和row跟踪
使用跟踪筛选功能，过滤要跟踪的那个数据库，其他数据库不要，只选择必要的列
F
要注意过滤
SQL:BatchCompleted 整个批处理
SQL:StmtCompleted 单独一个SQL语句
如果操作是作为远程过程调用remote procedure call，则应该跟踪RPC:Ccompleted事件类 链接服务器
只有completed事件类才会携带性能信息，所以要跟踪completed事件类，不跟踪Starting事件类
我把代码封装在一个PerfworkloadTraceStart的存储过程中
封装跟踪的存储过程，把这个存储过程在服务器上创建，输入 数据库id和生成文件的路径
如果你忘记跟踪ID，那么你不能停止跟踪和关闭跟踪，不过，不用怕，你可以使用sys.[traces]视图来查看跟踪，从而找到跟踪ID
SELECT * FROM sys.[traces]
F
F
--使用sys.[fn_trace_gettable]('')查看跟踪输出的trc文件内容 SELECT CAST([TextData] AS NVARCHAR(MAX)) AS tsql_code , [Duration] AS duration INTO [dbo].WORKLOAD FROM sys.[fn_trace_gettable]('') WHERE [Duration] > 0 AND [EventClass] IN ( 41, 45 )
F
F
F
--sql2008提供了[sp_get_query_template]存储过程，他可以解析输入的查询字符串，并通过输出参数返回查询模版和参数定义 DECLARE @my_templatetext AS NVARCHAR(MAX) DECLARE @my_parameters AS NVARCHAR(MAX) EXEC [sys].[sp_get_query_template] 'select * from dbo.t1 where col1=3 and col2>78', @my_templatetext OUTPUT, @my_parameters OUTPUT; SELECT @my_templatetext AS qrystring,@my_parameters AS params
SQL CAT
SQL2005 TSQL程序设计貌似有这个案例，将参数转问# 号
F
调优步骤
实例级 等待-》数据库级别-》进程级别
f
RegexReplace函数 CLR生成查询签名比用TSQL函数快10倍
F
使用CLR函数 RegexReplace函数
F
筛选查询前面会非常损耗性能，因为sql语句可能会非常长，计算sql语句的签名会损耗cpu，这时候在每行添加一个checksum列来标识每行
然后用checksum来执行聚合操作，聚合checksum列
F
F
根据checksum列的匹配找出对该查询结果表和workload表进行联接，找出相关SQL
SELECT CHECKSUM([city]),[region] FROM [dbo].[customer]
F
查询统计
SELECT * FROM sys.[dm_exec_query_stats]
返回对应查询的聚合性能统计信息，与跟踪方法不同的是，对于执行计划不在缓存中的查询，这个DMV不会报告关于这些查询的任何信息。可以通过这个DMV得到自查询计划被缓存以来，非常有意思的聚合性能信息
SELECT b.[text] , [plan_generation_num] FROM sys.[dm_exec_query_stats] AS a CROSS APPLY sys.[dm_exec_sql_text]([sql_handle]) AS b ORDER BY a.[plan_generation_num]
如果启动了“查询统计”这个系统收集组，他就按照为他定义的收集频率定期收集这个[dm_exec_query_stats]信息，报表“查询统计历史信息”
F
offset 基于0开启的偏移值，按字节计算，每个字符占两个字节的存储空间
SELECT a.[execution_count] , a.[plan_generation_num] FROM sys.[dm_exec_query_stats] AS a JOIN sys.[dm_exec_cached_plans] AS b ON a.[plan_handle] = b.[plan_handle] ORDER BY a.[plan_generation_num]
a.[query_hash] 用于识别具有相同查询签名的查询，类似于前面为了跟踪数据而为查询创建的校验和
a.[query_plan_hash] 用于识别类似的查询计划
这两个哈希值在sql2008引入，在sql2005中是没有的
在sql2005中查询的时候会报错
消息 207，级别 16，状态 1，第 3 行 列名 'query_hash' 无效。 消息 207，级别 16，状态 1，第 4 行 列名 'query_plan_hash' 无效。
根据查询统计信息得到的最慢查询
F
优化索引和查询
F
F
查询优化的工具
SELECT * FROM sys.[dm_exec_cached_plans] SELECT * FROM sys.[dm_exec_plan_attributes]() SELECT * FROM sys.[dm_exec_sql_text]() SELECT * FROM sys.[dm_exec_query_plan]() SELECT * FROM sys.[syscacheobjects]
清空缓存
--在分析性能问题时，有时需要清空缓存，sqlserver提供了用于从缓存清空数据和执行计划的工具 --清空所有数据 DBCC DROPCLEANBUFFERS --清空执行计划 DBCC FREEPROCCACHE --清空特定数据库的执行计划 DBCC FLUSHPROCINDB(DBID) --清空特定缓存存储区中的执行计划 DBCC FREESYSTEMCACHE(<CACHESTORE>) --<CACHESTORE>可以指定以下输入值： --all[pool_name]:all选项表示要清空所有支持的缓存，pool_name值指定的是一个要清空的资源调控器（resource governor） --池缓存的名称 DBCC FREESYSTEMCACHE('ALL') --指定资源池也会报错 DBCC FREESYSTEMCACHE('pMAX_CPU_PERCENT_10') --消息 2560，级别 16，状态 9，第 1 行 --对于此 DBCC 语句，参数 1 不正确。 --object plans:指定要释放对象计划（存储过程，触发器，用户定义函数的执行计划） DBCC FREESYSTEMCACHE('object plans') --sql plans：指定要释放即席（ad-hoc）语句的计划，包括预编译的语句 DBCC FREESYSTEMCACHE('sql plans') --bound trees：指定要释放视图，约束，默认的计划 DBCC FREESYSTEMCACHE('bound trees') --力伟的文章 清空引用tempdb的session http://www.cnblogs.com/stswordman/p/3358496.html --DBCC FREESYSTEMCACHE('ALL')指定all即清空object plans、sql plans、bound trees --执行完上面的各自三条语句后可以看一下[cacheobjtype]和[objtype]列 SELECT OBJECT_ID([objid]) , * FROM [sys].[syscacheobjects] WHERE [dbid] <> 32767
在产品环境下使用这些命令之前，要慎重考虑，因为清空缓存会对系统性能产生影响
动态管理对象DMV DMF
F
SET STATISTICS IO ON
大型对象LOB的物理读，逻辑读，预读
F
--当需要交互的分析某个查询的时候就需要使用 DECLARE @starttime DATETIME2(2) DECLARE @endtime DATETIME2(2) SELECT @starttime=SYSDATETIME() --代码 SELECT 1 SELECT @endtime=SYSDATETIME() SELECT @endtime-@starttime AS '时间间隔' 消息 8117，级别 16，状态 1，第 11 行 操作数数据类型 datetime2 对于 subtract 运算符无效。
当需要交互的分析某个查询的时候就需要使用，使用SYSDATETIME()函数获取精确到100纳秒的时间制，这个时间值是DATETIME2类型，这个函数实际的精确度将取决于计算机的硬件和Windows版本，因为SYSDATETIME()函数内部会调用GetSystemTimeAsFileTime（） Windows API，这个API是依赖于硬件和操作系统的，当进行测量查询统计时，这个精度还不够高，可以在一个循环中重复运行查询，然后用总的运行时间除以迭代次数，求其平均值
http://msdn.microsoft.com/zh-cn/library/bb677335.aspx
http://www.codesky.net/article/201009/144919.html
http://irfen.iteye.com/blog/1291063
执行计划
ctrl +L 预估执行计划
ctrl +M 实际执行计划，按F5才能查看
预估执行计划和实际执行计划通常都是一样的
F
F
实际的执行计划才能返回运算符的实际行数
F
注意每个运算符的开销百分比，这个值是某个运算符的开销占查询全部开销的百分比，应该多留意一下百分比较高的运算符
F
在图形化执行计划里，在每个计划的顶部有一个百分比，表示该查询的估计开销占整个批处理总开销的百分比
F
actual number of rows（实际行数）从运算符返回的实际行数（只在实际的计划中显示）
estimated number of rows（估计行数）：该运算符预计返回的行数，在有些情况下，通过观察实际行数和估计行数之间的差异，
可用找出因为统计信息或其他原因而导致的开销问题
实际重绑次数和实际重绕次数：在nonclustered index spool，remote query，row count spool ，sort，table spool，table-valued function，assert，filter，对于这些运算符并且只有作为nested loop联接内侧的运算符时，这两个测量值才有效，这两个测量值表示内部init方法调用次数
重绑：联接的一个或多个参数发生更改之后，必须重新计算联接的内侧
重绕：任何相关参数都没法发生更改，可以重用之前的内侧结果集
F
SET SHOWPLAN_TEXT ON
F
SET SHOWPLAN_TEXT ON ----消息 1067，级别 15，状态 1，第 0 行 --SET SHOWPLAN 语句必须是批处理中仅有的语句。 --所以第二句，sqpprompt不会帮你提示 SET SHOWPLAN_ALL ON
SET STATISTICS PROFILE ON 将生成实际的执行计划，使用该选项会执行查询并生成输出
SET SHOWPLAN_XML ON
F
SET SHOWPLAN_XML ON是最丰富的,并且可以保存在.sqlplan文件里，其他的图形化计划也不够丰富，
SET SHOWPLAN_XML ON包括丢失的索引，计划是否平凡trival，查询使用的实际平行度，实际的内存授权memory grant等
查询提示
提示分为三类：联接提示，查询提示，表提示
联接提示：INNER MERGE JOIN
查询提示：在语句最后加OPTION(MAXDOP 1)
表提示：在表名后加WITH (INDEX=xx)
根据提示功能，将他们分为：索引提示，联接提示，并行，锁，编译，其他类型
F
OPTION (USE PLAN N'<XML_PLAN_GOSE_HERE>')
计划指南
跟踪profiler
数据库引擎优化顾问DTA（dta.exe命令行工具）
F
数据收集和管理数据仓库
sql2008引入了一个数据收集平台，通过他收集性能和其他信息，数据收集平台主要组成部分是数据收集器data collector，
数据收集器将安装三个系统数据收集组data collection set，用于收集性能，包括磁盘使用情况，服务器活动，查询统计信息，使用预定义的报表对收集到的性能进行分析
使用SMO复制统计信息，将客户的统计信息复制过来，通过使用SQLSERVER管理对象（smo，sqlserver management object）API提供的脚本功能（
具体地说 ，是ScriptingOptions.OptimizerData属性），就可以实现这样的需求
当行超过8060字节时，这些类型的值将被移动到一个称为行溢出分配单元的页面中，而在原页面保留24字节指针，
如果该列的值超过8000字节，将会放在lob页面，在原页面保留16字节的指针
F
预读可以在区级别，或是在更高的块级别上读取数据
I/O操作中开销最大的部分是磁盘臂移动disk arm，因此，读取一个页面和读取整个区所用的时间几乎一样长，
那么sqlserver预读的时候就算不是读取整个区，干脆把区里面的其他页面也读出来
HOBT以及非聚集索引可以在一个或多个分区partition的单元上实现，从技术上来说，各分区上的HOBT和每个非聚集索引可以是不相同的，
每个hobt和非聚集索引的各分区将数据存储在称为分配单元（allocation unit）的一组页面。分配单元有三种类型：in_row_data,row_overflow_data,lob_data
有SELECT * FROM sys.[system_internals_allocation_units]视图里的页面指针定位
SELECT * FROM sys.[system_internals_allocation_units]
F
F
平衡树概念：不存在叶子节点比其他叶子节点到根的距离要远得多的树
根据索引碎片级别，页面的磁盘文件顺序（物理顺序）可能与该链表维护的逻辑顺序不匹配！！
页面逻辑顺序不一定与物理顺序匹配
F
数据库中之所以会逐渐出现碎片（fragmentation 逻辑扫描碎片），主要因为页面在索引的叶级别不断地拆分
sqlserver将分配新页面，把大约一半的行从原来的页面移动到新页面
拆分后，新页面不保证一定就在被拆分的页面的后面，可能在数据文件后面的任何位置!!
中间级页面存放：指向他下一级页面的6字节指针，包含文件号和页面号，下一级页面的第一行的键值
查看索引的层级
SELECT INDEXPROPERTY(OBJECT_ID('testrow'),'ix_test_id','IndexDepth')
F
对于几千行的表，索引有2级
4000000 4百万行的表 索引有3级
4000000000 4十亿行的表 索引有4级
无论聚集索引还是非聚集索引
F
F
为什麽聚集索引上的非聚集索引指向聚集键，而不是RID
答：聚集键由被指向行的聚集索引键值和唯一标识符uniquifier（如果存在）组成，其原理是指向逻辑的行，而不是物理的行，就像逻辑数据页面号，
这种架构为OLTP系统而设计，在这种系统下，当插入和更新数据时，聚集索引会出现大量页拆分，
如果非聚集索引指向行的RID（行的物理指针）实际的聚集索引叶子页面（数据页面），那么RID指针就必须要更新，以反映新的RID，
而且在所有非聚集索引中的所有相关指针都要被更新，相反，如果sqlserver维护的是逻辑指针，则当数据行移动时，不需要更新相关指针，
这有点像forwarding pointer
聚集表上的非聚集索引查找：查找操作在非聚集索引中查找指定特定的键值（某个orderid值），最后到达相应的叶级行，再访问行定位符，
这种情况下找到的行定位符是被指向行的聚集键，为了真正取回被指向的行，lookup操作还要在聚集索引内根据得到的聚集键执行一次完整的查找操作，
这种查找操作叫“key lookup”，与前面的RID lookup不同
记住：对聚集索引执行lookup操作时，聚集索引的中间级层通常都已经位于缓存中了，通常大多数物理读是发生在叶子级别
F
索引访问方法
表扫描/无序聚集索引扫描
F
F
无序覆盖非聚集索引扫描
F
F
有序聚集索引扫描
F
注意：在执行计划中，ordered属性为true，这指示从运算符返回的数据应该是有序的
当运算符具有Ordered:True这一属性时，存储引擎只能以一种方式来执行扫描，通过索引顺序扫描（基于索引链表的扫描）
碎片越多，索引顺序扫描性能越差！！
优化器并不只限于有序向前操作，索引中的链表是一个双向链表，其中的每个页面都包含一个向后next，和向前previous的指针，如果请求了一个降序排序，依然会得到一个有序索引扫描
有序覆盖非聚集索引扫描
F
F
段运算符（segment）把数据划分为多个段，每次为下一个运算符（这个例子中是top）输出一个段，top运算符只有一个简单任务，收集每个段的最后一行
F
存储引擎扫描处理
关系引擎就是SQLSERVER的大脑，优化器是他的一部分，存储引擎就是SQLSERVER四肢，存储引擎执行执行计划中的具体指令，
优化器发出的指令有时会给存储引擎留下一定的选择空间
插件式的架构给MySQL带来了活力，做出牺牲便是在上层(MySQL)和下层(存储引擎)交互时带来的额外消耗，有时甚至上层和下层需要做一些重复工作。无法做Group Commit就是这其中的牺牲之一。
想想MYSQL插件式的存储引擎
具体使用分配顺序扫描还是索引顺序扫描，由存储引擎决定，不幸的是，存储引擎的实际选择并没有在执行计划中指示出来，
这里会解释存储引擎的决策过程，但执行计划显示的是关系引擎的指令，而不是存储引擎已经实施的行为，理解这一点很重要！！
分配顺序扫描不受索引中逻辑碎片的影响，因为他只是按文件进行扫描
就性能来说，存储引擎会优先选择分配顺序扫描
当索引非常小（最多64页 八个区）他会选择索引扫描
因为解释IAM页的开销要主要开销，对于小表，按照性能，分配顺序扫描也是首选
除了性能，存储存储引擎还需要考虑隔离级别和一致性
F
分配顺序扫描引起的脏读
http://www.cnblogs.com/lyhabc/p/3329535.html
F
在索引表中，由于在分配顺序扫描期间，分配顺序扫描可能在结果中多次返回某些行，也可能忽略某些行
F
对于分配顺序扫描的忽略行和多次读行，存储引擎还会执行一次Index Scan Ordered：False扫描
安全扫描类型
不安全扫描类型
当在已提交读或更高的隔离级别下，存储引擎将会选择索引顺序扫描，以避免由于页拆分而引起的数据不一致现象
存储引擎会采用不安全的分配顺序扫描
1、索引大小超过64页
2、执行计划显示index scan：ordered false
3、查找在未提交读隔离级别下运行
4、允许对数据进行修改 在select的同时有人在update同一块数据
当使用TABLOCK提示的时候，存储引擎知道是安全的，会使用分配顺序扫描
使用分配顺序扫描的另一个例子是，索引位于只读文件组或只读数据库中
索引大于64页，数据处于只读状态，使用tablock提示，只读文件组或数据库，存储引擎使用安全的分配顺序扫描
存储引擎有时也可以使用索引顺序扫描来处理ordered：false属性的index scan运算符
F
运行下面代码检查碎片
SELECT [avg_fragmentation_in_percent] FROM sys.[dm_db_index_physical_stats](DB_ID('tempdb'), OBJECT_ID('dbo.T1'), 1, NULL, NULL)
F
使用FOR XML PATH选项把叶级地址链表串接到一个字符串中
F
F
F
F
高文佳的文章《Transaction And Lock--READ COMMITTED隔离级别下的"脏读"》
http://www.cnblogs.com/TeyGao/p/3778522.html
如果加ORDER BY C0
--======================= --查询数据 SELECT C1,C2,C0 FROM TB106 ORDER BY [C0]
不会出现1001行
幻读
不加order by是false
除了插入会造成页拆分之外，对索引键更新也会造成页拆分，同样也会造成多读和忽略读
F
重复读
在未提交读隔离级别和已提交读隔离级别下，未提交读隔离级别不会加共享锁，在已提交读隔离级别下，虽然会获得共享锁，但一旦查询的时候读取完那一行，
那么SQL就会释放那一行的共享锁，即使查询还未完成，这意味着，在扫描过程读取完刚开始扫描的那些页面的瞬间，已经读取的行不会持有任何锁，
这时候对刚开始扫描的那些页面进行更新，就会发生页拆分，因为扫描还没有结束，那么当扫描到最后的页面的时候就会重复读取到刚开始扫描的那些页面的记录，就是重复读
在高于已提交读隔离级别上不会发生这种重复读的现象，共享锁会直到事务结束才释放，基于行版本的两种隔离级别也不会出现重复读
F
忽略行
忽略行：在未提交读，已提交读，重复读这些隔离级别中都有可能发生忽略行
可序列化隔离级别或基于快照的隔离级别不会发生这种现象
快照隔离级别根据事务来阻塞，所以update根据运行不了
F
非聚集索引查找+有序局部扫描+lookup
高选择性是指低百分比
低选择性是指高百分比
F
因为涉及大部分的I/O操作，lookup为每个找到的行在聚集索引中读取页面或执行完整的查找seek，而且lookup总是随机I/O,而不是连续I/O
lookup操作的开销等于
指定范围内的行数（这个例子是20）乘以1（如果是堆表）
指定范围内的行数（这个例子是20）乘以3 聚集索引的级数（如果是聚集表）
如果在堆上执行该查询应该共有23次逻辑读
如果在聚集表上执行该查询应该共有63次逻辑读，记住聚集索引的中间结点通常在内存中，对于聚集表方案这种表面上的高开销，你不用过于担心
F
F
只有当查询的选项非常高时（点查询point query或小范围查询），这种访问方法才是高效的
增大筛选器指定的范围，当增大到一定的时候，终究会达到一点，优化认为使用表扫描比索引查找更有效的那一点
F
无序非聚集索引扫描+lookup
Repartition Stream运算符：将生成多个记录流 Repartition 重分区
Gather Stream运算符：将会处理几个输入流，生成单个输出流
F
F
F
查询SQLSERVER自动在Orders表上建立的统计信息名称 SELECT [name] FROM sys.[stats] WHERE [object_id] = OBJECT_ID('dbo.Orders') AND [auto_created] = 1
估计的影响（改进）都超过99%，当优化器优化这一查询时，他会寻找他认为是最优的索引，如果找不到，则会报告缺失索引，
查询的XML显示计划会在MissingIndexes属性中报告缺失索引的信息；SSMS则会解析这一信息，将他图形化显示出来，类似的信息在sql2005里也有
SQLSERVER内部也会记录这样的缺失索引，通过DMV来访问这些信息
SELECT * FROM sys.[dm_db_missing_index_details] SELECT * FROM sys.[dm_db_missing_index_groups] SELECT * FROM sys.[dm_db_missing_index_group_stats] SELECT * FROM sys.[dm_db_missing_index_columns]()
F
SQL2005添加了like谓词匹配，sqlserver内部会为字符串列中的子字符串维护一种基数（cardinality）信息，为这种筛选器估计查询的选择性
聚集索引查找+有序局部扫描
尽管筛选器使用了等于运算符，但他本质上还是一个范围查询，因为表中存在多个满足条件的行，此外可以将点查询看作是范围查询的一种特殊情况
F
F
覆盖非聚集索引查找+有序局部扫描
f
SQLSERVER要保持树平衡，当修改表中的键值时，他必须在树中物理地移动数据并进行调整
包含性列
为了应对这种需要，SQLSERVER支持在索引中使用包含性列（included nonkey clumns）的概念，当创建索引时，可以分别指定哪些列用于构建键列，
哪些列只是处于覆盖而被包含到索引中（这些包含性列只位于索引的叶子级别），这样可以当update索引的键列的时候，而且页面是中间级，那么就不用修改中间级的覆盖列，因为覆盖列变成了包含性列
f
创建NVARCHAR(MAX)的包含性列
ALTER TABLE [testrow] ADD name NVARCHAR(MAX) CREATE INDEX idx_testrow ON [testrow]([name]) --消息 1919，级别 16，状态 1，第 1 行 --表 'testrow' 中的列 'name' 的类型不能用作索引中的键列。. CREATE INDEX idx_testrow ON [testrow](id) INCLUDE([name])
索引交集（index intersection）
f
自然地，指向相同行的行定位符值应该互相匹配，所以，可以将索引交集看作是按行定位符值进行匹配的内联接
过滤索引和过滤统计信息
f
f
在一个允许为null的列上 定义了一个unique约束，那么这个列上就只能有一行可以为null的解决方案
方法一：过滤索引
出于强制唯一性的目的，SQLSERVER支持的unique约束认为两个null是相等的，这意味着，如果在一个允许为null的列上 定义了一个unique约束，那么这个列上就只能有一行可以为null。
但在一些情况下，可能只需要对非null值强制实施唯一约束，允许存在多个null值，ANSI SQL确实支持这种unique约束，但sqlserver还未实现过这种约束，现在，有了过滤索引，这种需求解决起来不难
CREATE UNIQUE NONCLUSTERED INDEX idx_id_notnull
ON [testrow](id)
WHERE [id] IS NOT NULL
那么id列可以有多个null，但是不可以有多个非null值
方法二：触发器
方法三
《Microsoft SQL Server 2005技术内幕： T-SQ程序设计 》 视图那一章
使用索引视图
从一个查询创建视图，而该查询从原始列中筛选非NULL值
create view dbo.v1 with schemabinding as select keycol form dbo.T1 where keycol is not null go create unique clustered index idx_uc_keycol on dbo.v1(keycol)
f
索引视图
f
f
f
f
f
确定选择点
输入几个值确定sqlserver到底什么时候才开始使用非聚集索引查找
lookup使用随机I/O，表扫描使用顺序I/O
f
记住：索引顺序扫描的性能在很大程度上取决于索引的碎片级别
优化策略中的最佳优化级别是把orderid列定义为键列，并把其他列定义为包含性列
CREATE UNIQUE NONCLUSTERED INDEX idx_id_notnull ON [testrow](id) INCLUDE(orderdate,custid,empid,shipperid)
估计子树开销
该查询的开销从最低级别策略的19.3423下降到0.0080857，实耗时间从超过8秒下降到230毫秒
f
f
f
f
除了添加合适索引，你也要找出哪些索引使用非常频繁，哪些索引根本不使用或很少使用
SQLSERVER会在幕后收集索引使用信息，允许你通过DMV来查询这些信息
通过sys.[dm_db_index_operational_stats]和sys.[dm_db_index_usage_stats]视图来拼接sql来drop掉索引
--得到sss数据库的所有对象，索引，分区信息调用下面函数 SELECT * FROM sys.[dm_db_index_operational_stats](DB_ID('sss'),NULL,NULL,NULL) --[dm_db_index_usage_stats]提供了不同类型索引操作的使用计数 SELECT * FROM sys.[dm_db_index_usage_stats]
sys.[dm_db_index_physical_stats]()
都是dm_db_index开头
逻辑扫描碎片，平均碎片百分比或外部碎片，这类碎片表示索引中无序页所占的百分比，无序页是根据页面的物理顺序和页面在索引链表中的逻辑顺序来确定的，
本来顺序I/O变成随机I/O。碎片对索引的有序扫描操作具有非常显著的影响,他对那些不依赖索引链表的操作（例如，查找操作，lookup，分配顺序扫描等）没有影响，
对于有序索引扫描开销占比较大比例的查询，应该最小化索引的碎片级别，可以重建或重组索引
无序页：外部碎片
平均页密度：内部碎片
低页密度：对插入有利，oltp系统
高页密度：对读取有利，olap系统
f
使用重组索引还是重建索引，要使用 sys.[dm_db_index_physical_stats]视图来进行判断
SELECT * FROM sys.[dm_db_index_physical_stats](DB_ID('sss'), NULL, NULL, NULL, 'sampled')
重建索引效果最好，而且可以指定填充因子，如果服务器有多个CPU，SQLSERVER会使用并行索引重新生成索引（只有企业版）才有，操作完成速度比单线程快，但可能导致更多的逻辑碎片，
可以用maxdop提示来限制操作只使用单个CPU,这样虽然花费更多时间，但是获取更少碎片
在索引所在文件组中，SQLSERVER需要一定空间进行排序操作，如果文件组只有少量空闲空间，则在操作结束时，可能依然会有一些逻辑碎片，为了让碎片最小化，要保证有足够的空闲空间，或者使用SORT_IN_TEMPDB选项，请求重建索引时使用tempdb数据库空间来进行排序
联机索引重建实际上在幕后维护两个索引，完成后用新索引覆盖旧索引
重组索引比重建索引生成更多日志，而且更慢，整理不够理想
在非企业版中，这是唯一的联机碎片整理工具，而且不像重建索引，要获得整个期间的锁，一旦失败重建索引全部回滚，但是重组索引失败，可以在失败点继续
f
f
f
f
重绑：join运算符的一个或多个相关参数发生变化，内测必须重新计算，该过程发生了14次
重绕：没有相关参数发生变化，可以重用原来的内部结果集，该过程发生了999986次（1000000-14=999986）
f
TABLESAMPLE：SQLSERVER支持一个新特性，可以从现有的表中生成样本数据，这个工具名称为TABLESAMPLE子句，
可以在from子句中的表名后指定TABLESAMPLE子句以及相关选项
f
f
SELECT TOP (100) * FROM [dbo].[testrow] TABLESAMPLE (2000 ROWS)
f
f
f
f
F
SQLSERVER开发人员知道许多程序员和DBA会使用TOP选项，来强制优化器不拆开嵌套的子查询，如果在sqlserver未来版本或sp中，开发人员不再保持优化器的这种行为了，
那时，sqlserver可能在内部将我们的top查询翻译成逻辑上相等的MAX或MIN版本，最终得到的又是低效的计划
F
F
第五章 算法和复杂性
SQL是第四代，声明性的编程语言，SQL程序描述的是想要的结果
F
算法的开销依赖于他的输入大小，这称为算法的复杂度（complexity）
线性复杂度的算法
指数和超指数复杂度
F
次线性复杂度
二分查找法
常量复杂度
F
复杂度比较
现在的CPU，每秒可以执行几十亿条低级指令
勾选“执行后放弃结果”来测试实耗时间
F
F
排序算法
F
更快的排序算法
超排序（Ultra sort）
字符串查找
在千兆字节的BLOB数据中搜索病毒签名
F
识别测量数据的趋势
F
最长上升序列的长度问题
F
F
使用了sql2008的merge语句
F
第六章 子查询、表表达式、排名函数
这个子查询是自包含的，也就是说，他不依赖于外部查询
标量子查询和多值子查询
表表达式：CTE 派生表
F
按期望值分类：标量子查询，多值子查询
按对外部依赖分类：独立子查询，相关子查询
关系除法
F
F
f
相关子查询
决胜属性
决胜属性并不限于单个属性 max（reueredate），max（orderid）需要多个列才能获得排名
F
嵌套子查询
F
exists返回unknown
F
exists
exists（select * from sales.orders as o where o.custid=c.custid）
尽管使用*不是一个好习惯，不过这里放心，exists只关心行是否存在，短路，优化器将忽略子查询中的select列表，
如果加上select list，sqlserver需要检查列的许可访问权限，这会带来一些解析开销
exists和in哪个高效
对于在逻辑上相等的两个查询，优化器会为他们生成相同的执行计划
三值逻辑
当输入列中包含null，in实际上会产生一个unknown逻辑结果
例如：a in（b,c,null）的结果是unknown
F
not in和not exists的区别
top运算符：限定输出
图中包含一个top运算符，因为只需确定是否至少有一个订单与该客户匹配，这就是exists的短路能力，
当orders.custid列的密度比较高（包含大量重复）时，使用top特别有效，每个客户只发送一次查找，在索引叶子只扫描一行，以查找一个而不是匹配所有
F
not in会返回空集，只返回not true或not unknown，这两个结果都不会是true
只要列表包含null，查询就不会返回任何客户
当允许null 时，not exists和not in是不等价的，要把not exists和not in等价，就要吧列声明为not null或者在子查询添加列 is not null
最小缺失值 missing value
F
F
F
逆向思维解决SQL问题
F
CHECK (sn NOT LIKE '%[0-9]%')
否定逻辑reverse logic比使用肯定逻辑positive logic 更有效
--CHECK匹配序列号
--序列号固定
CHECK(sn LIKE '[0-9][0-9][0-9][0-9][0-9]')
--序列号不固定
CHECK(sn LIKE REPLICATE('[0-9]',LEN(SN)))
《Microsoft SQL Server 2005技术内幕： T-SQ程序设计 笔记》
行为不当的子查询
相关子查询的bug外部查询的列名和内部查询的列名相同，sqlserver不进行名称解析，没有加表别名，解决方案：加上表别名，引用列名的时候加表别名
F
F
不常用的谓词：any，some，all
any和some没有区别
in谓词可以看成一组由or运算符的等于谓词，v=x or v=y or v=z
any，some允许谓词有比较关系，
v<ANY(x,y,z)相当于v<x or v<y or v<z
all 谓词功能类似，只不过他相当于一组由and运算符分割的逻辑表达式
--v<>all(x,y,z)相当于v<>x and v<>y and v<>z
字面值的比较
SELECT v <>ANY(SELECT x UNION ALL SELECT y UNION ALL SELECT z)
F
表表达式
派生表
F
F
公用表表达式
CTE的with子句之前的语句必须以分号结束
F
CTE使用批处理中的参数
多CTE
多CTE用逗号进行分隔
WITH C1 AS ( SELECT * FROM [dbo].[testrow] ), C2 AS ( SELECT * FROM [dbo].[TB1] ) SELECT * FROM [C1] , [C2]
F
派生表和CTE都是虚拟的，SQLSERVER会重新组织查询直接访问底层对象
表值函数
SELECT * FROM [dbo].[testrow] CROSS JOIN (VALUES(1),(2)) AS num(n)
F
对CTE数据进行修改
派生表也支持修改语句
DELETE FROM custdupsrn FROM ( SELECT * FROM [dbo].[testrow] ) AS custdupsrn
F
递归的CTE
F
一个递归的CTE至少要包含两个查询（也称为成员）
定位点成员
递归成员
F
OPTION(MAXRECURSION 20)
(MAXRECURSION 的默认值是100
如果指定OPTION(MAXRECURSION 0)表示不限制递归次数
分析排名函数：row_number,rank,dense_rank,ntile
F
over子句定义窗口中的逻辑排序，基于窗口的计算就不会干扰外部集合的各个方面
通过可选的partition by子句，可以在行的每个分区（或组）上分别计算排名值，例如如果在partition by子句中指定mgrid，就会为属于每个经理的行独立地计算排名值
在order by子句中，可以为计算定义逻辑顺序（即，排名值分配的逻辑顺序）
F
行号
序列映射运算符（Sequence Project）：负责计算排名值，对于每个输入行，他需要两个标记
1、该行是否是分区中的第一行？如果是，序列映射运算符将重置排名值
2、该行中的排序值是否不同于上一行？如果是，序列映射运算符将按特定排名函数所规定的方式，递增相应的排名值
对于所有排名函数，Segment段运算符将生成第一个标记值
Segment段运算符主要用于确定分组边界
F
确定性：为保证确定性，必须要添加一个决胜属性：使分区列+排序列的取值成为唯一的
F
--ROW_NUMBER不允许指定常量 SELECT ROW_NUMBER() OVER(ORDER BY 1) --消息 5308，级别 16，状态 1，第 1 行 --开窗函数不支持整数索引作为 ORDER BY 子句表达式。 --ROW_NUMBER不允许指定常量，只能 加（select 1） SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) --当select 0的时候优化器知道行的顺序无关紧要,优化器也知道变量无关紧要 SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) DECLARE @v INT SET @v=0 SELECT ROW_NUMBER() OVER(ORDER BY (SELECT @v))
--指定多个row_number SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT @v ) ) , ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) FROM [testrow] --分区内计算排名值 SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT @v ) ) , ROW_NUMBER() OVER (PARTITION BY @v ORDER BY ( SELECT 1 ) ) FROM [testrow]
如果不用内建的排名函数来计算排名值，有几种替代方法，但他们存在一些限制
F
排名列一定要定义为not null，否则null会排在最前面
F
计算前n个正整数之和的公式是：（n+n2）/2
运行时间以n的2次方而增长的曲线
运算符优先级规定and在or之前进行计算
F
数字辅助表
F
F
性能
row_number>游标>子查询
F
有关identity和order by的详细讨论 知识库文章
http://support.microsoft.com/default.aspx?scid=kb;en-us;273586
http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx
sql2008之前，insert select会完整记录日志操作，select into会最小化日志记录
sql2008，无论insert select还是select into都可以最小化日志记录
F
4种在服务器端计算行号的不同方法
1、使用row_number函数
2、使用子查询
3、使用游标
4、使用identity
--已分区排序 CREATE TABLE #salesRN ( mgrid VARCHAR(5) , empid VARCHAR(5) , qty INT , rn INT IDENTITY ) --方法一 子查询 SELECT mgrid , empid , qty , rn - ( SELECT MIN(rn) FROM #salesRN AS s2 WHERE [s2].[mgrid] = s1.[mgrid] ) + 1 AS rn FROM [#salesRN] AS s1 --方法二 联接 SELECT s.[mgrid] , [empid] , [qty] , rn - [minrn] + 1 AS rn FROM [#salesRN] AS s JOIN ( SELECT [mgrid] , MIN(rn) AS minrn FROM [#salesRN] GROUP BY mgrid ) AS m ON [m].[mgrid] = [s].[mgrid]
F
F
sql2008的新功能：datetime2数据类型、mcs(微秒)数据部分，sysdatetime函数
sysdatetime函数调用Windows API返回更精确的时间
F
测试结果显示ROW_NUMBER函数最快
F
即席分页（ad hoc）只请求单独一页，不请求其他页面
多页分页
只扫描少量页面 执行计划：select《-过滤《-top《-序列映射
F
排名和密集排名函数
row_number函数中，当order by列表不能唯一决定排序顺序时，row_number是非确定性的
而rank和dense_rank总是确定性的
rank和dense_rank：具有相同排序值的行总是得到相同的排名值
rank的排序值可能有间断
dense_rank的排序值没有间断
USE [sss] SELECT * , RANK() OVER ( ORDER BY [id] ) AS rnk , DENSE_RANK() OVER ( ORDER BY id ) AS drnk FROM [testrow] ORDER BY [id]
f
组号tile number
f
分页和分组不一样
分页：每页大小已知，页数是将集合中的行数除以页大小的结果
分组：组的数量是已知，组的大小是将集合中的行数除以请求的组数的结果
NTILE函数
NTILE函数支接受一个输入：请求分组的数量，因为组号计算是基于行号的，所以NTILE函数也存在之前行号计算提到的确定性问题
--实际上top后面的 NTILE和WITH TIES不一样 SELECT TOP 1 NTILE(3) OVER ( ORDER BY [Company] ) AS tile FROM [Department] ORDER BY [Company] SELECT TOP 10 WITH TIES
F
NTILE函数：将剩余行分配给前面的组
均匀分布剩余行的技巧是：当计算组大小时，用实数除法而不是整数除法，使用小数点
均匀分布算法
F
F
数字辅助表
F
F
F
缺失范围和现有范围（间断和孤岛）
F
F
F
CREATE TABLE [dbo].BigNumSeq ( seqval INT NOT NULL CONSTRAINT PK_BigNumSeq PRIMARY KEY ) INSERT INTO [BigNumSeq] ( [seqval] ) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 SELECT * FROM [BigNumSeq] DELETE FROM [BigNumSeq] WHERE [seqval] =3 --找出间断值的前一个值 SELECT [seqval] FROM [dbo].[BigNumSeq] AS A WHERE NOT EXISTS ( SELECT * FROM DBO.[BigNumSeq] AS B WHERE B.[seqval] = A.[seqval] + 1 ) AND [A].[seqval] <(SELECT MAX([seqval]) FROM [BigNumSeq])
CREATE TABLE [dbo].BigNumSeq ( seqval INT NOT NULL CONSTRAINT PK_BigNumSeq PRIMARY KEY ) INSERT INTO [BigNumSeq] ( [seqval] ) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 SELECT * FROM [BigNumSeq] DELETE FROM [BigNumSeq] WHERE [seqval] =3 --找出间断值的前一个值 下面这个查询关键是没有order by，扫描的顺序不是从1到5的，是根据符合的情况 --因为1+1<>1 ,1+3 因为没有3，所以会报告出4 SELECT [seqval] FROM [dbo].[BigNumSeq] AS A WHERE EXISTS ( SELECT * FROM DBO.[BigNumSeq] AS B WHERE B.[seqval] = A.[seqval] + 1 ) AND [A].[seqval] <(SELECT MAX([seqval]) FROM [BigNumSeq]) SELECT * FROM DBO.[BigNumSeq] AS B WHERE B.[seqval] =B.[seqval] + 1 seqval 1 4
F
F
F
F
F
子查询
排名函数
游标
用游标处理每一行都会付出一定的开销，而对于基于集合的处理，则不存在这样的开销
F
返回单个缺失值
F
F
孤岛问题子查询解决方案：分组因子（grouping factor）
F
F
F
孤岛问题解决方案3
孤岛问题解决方案3：使用基于排名计算的组标识符
孤岛问题方案3也基于组表示符的概念，这种方法是基于包含间断的序列和行号序列之间具有的某种特殊关系
注意[NumSeq]表有[seqval]列和name 列
USE [sss] CREATE TABLE [dbo].NumSeq(seqval INT,name VARCHAR(20)) INSERT [dbo].[NumSeq] ( [seqval],name ) SELECT 1,'ni' UNION ALL SELECT 2,'ni' UNION ALL SELECT 3,'ni' UNION ALL SELECT 16,'ni' UNION ALL SELECT 17,'ni' UNION ALL SELECT 23,'ni' UNION ALL SELECT 24,'ni' UNION ALL SELECT 25,'ni' SELECT * FROM [dbo].[NumSeq] --生成差值 SELECT [seqval],[seqval]-ROW_NUMBER() OVER(ORDER BY [seqval]) AS diff FROM [dbo].[NumSeq] --孤岛问题解决方案3 得出每个孤岛的第一个值和最后一个值 WITH D AS (SELECT [seqval],[seqval]-ROW_NUMBER() OVER(ORDER BY [seqval]) AS grp FROM [dbo].[NumSeq]) SELECT MIN([seqval]) AS start_range,MAX([seqval]) AS end_range FROM [D] GROUP BY [grp]
F
对于重复值的序列，处理技巧是不为重复值增加排名值，为此，应该将row_number函数替换为dense_rank函数
F
F
F
第七章 联接和集合运算
旧语法sql89 用逗号分隔两表 没有join关键字和on关键字 只支持cross join和inner join
新语法sql92 引入join和on 支持outer join
F
交叉联接：第一个步骤笛卡儿积
内联接 第一个步骤笛卡儿积和第二个步骤on筛选器
外联接 第一个步骤笛卡儿积和第二个步骤on筛选器 和第三个步骤添加外部行
F
F
row_number子句中的表达式（select 0）的使用，他提示：行号的分配顺序并不重要，如果需要保证行号的分配顺序，应该在这个位置上指定需要的排序属性
F
F
在inner join的where子句指定逻辑表达式，还是在on子句指定逻辑表达式没有任何区别，除非指定了GROUP BY ALL
GROUP BY ALL:会把where子句过滤掉的组再添加结果集，但不会添加被on子句过滤掉的组，该选项是一个非标准的遗留功能，应该避免使用
内联接
性能方面，无论吧筛选表达式放到on还是where，都会得到相同的执行计划
表之间匹配的属性使用on子句
只对一个表的属性进行筛选应该放在where子句
--SQL89标准可以不加join，所以有时候只加一个逗号相隔，两张表的时候， --你需要判断是cross join还是inner join select c.custid,companname,orderid from sales.customers as c ,sales.orders as o where c.custid=o.custid and country='USA'
--使用SQL89进行inner join会有一个风险，如果忘记指定联接条件，会不幸----地得到一个交叉联接 select c.custid,companname,orderid from sales.customers as c ,sales.orders as o where country='USA'
当在on子句指定了 on C.orderid=C.orderid,SQLSERVER不会捕获这种错误
使用SQL89语法更容易犯错，在from子句中列出所有表名（用逗号分隔），再在where子句中把所有联接谓词都AND起来
from t1,t2,t3,t4 where 谓词1 and 谓词2 and 谓词3
用SQL92来写联接是一个最佳实践
外联接
F
--修改数据库兼容性 以支持sql2000的语法 ALTER DATABASE [sss] SET COMPATIBILITY_LEVEL =80 --sql2000的左外联接 SELECT c.custid,companyname,orderid FROM sales.customers AS c,sales.orders AS o WHERE c.custid*=o.custid
F
找出间断值的最小那个值
--子查询方式 SELECT MIN(a.keycol) +1 FROM t1 AS a WHERE NOT EXISTS (SELECT * FROM t1 AS b WHERE b.keycol=a.keycol+1)
F
ANSI还支持两种两种TSQL不支持的联接类型：自然联接和合并联接
F
自联接
不等联接：on 里除等号外的其他运算符 例如A .COL1 < B.COL1
F
F
用子查询或联接（扩展-压缩技术）
多联接：涉及三个或更多的表
F
force order
强制优化器根据你书写的表顺序来进行联接，而不接受优化器给你安排的联接顺序
SELECT * FROM [dbo].[testrow] AS a JOIN [dbo].[aaa] AS b ON a.[id] = b.[a] OPTION ( FORCE ORDER )
强制联接顺序的另一个办法打开SET FORCEPLAN ON ，这将会影响session中的所有查询
F
检查估计是否准确的一种方法是：比较执行计划中由各运算符得出的估计行数和实际行数
逻辑查询处理的三个阶段：1、笛卡儿积 cross join 2、ON筛选器 3、添加外部行 outer join
F
优化器不能对外联接应用联接排序优化（这需要按指定的顺序才能进行）
最后指定 ON子句 on o.custid=c.custid这样在逻辑上可以先处理其他联接，最后对customers表和其他表的内联接结果进行左外联接
F
使用圆括号改变join联接的顺序
交错关系（chiastic relationship）chiastic：交错法
如果不使用圆括号，这段查询的可读性非常差，而且为了写出有效的查询，还需要时刻注意保持交错关系，
相反，如果使用了圆括号，查询将变得更具可读性，也更直观，而且也不需要过多考虑交错关系，因为圆括号会强制你正确编写查询
F
浓密计划（bushy plan）
优化器总是只考虑一种树布局
右向深度树（right deep tree），其中每个联接的结果都用作下一个联接的内部输入
左向深度树（left deep tree），每个联接的结果都用作下一个联接的外部输入
SQLSERVER引擎团队提出了一种称为浓密树布局（bushy tree layout）的处理方案
F
除非明确指示使用浓密计划，否则优化器不会考虑这种计划布局，当要联接的表的数量是基数的时候这种浓密计划用不上，
浓密计划是让查询计划可以对两个联接的结果进行联接，
而不是一个联接结果和一个基表进行联接，这样就会快很多，当有多表联接的时候
加圆括号
--浓密计划 SELECT * FROM ( dbo.[T1] JOIN [dbo].[T2] ON T1.B = T2.B) JOIN ( dbo.T3 JOIN dbo.T4 ON T4.C = T3.C) ON T3.C = T1.C OPTION ( FORCE ORDER )
联接和联接做join
F
半联接 semi join
半联接根据一个表中是否存在匹配行，而从另一个表中返回行的联接，如果是从左表返回属性，这种联接称为左半联接，如果是从右表返回属性，这种联接称为右半联接
半联接：Inner join，子查询的exist或in，intersect集合运算
与半联接相反的是反半联接 anti -semi join 他根据一个表中是否不存在匹配行，而从另一个表中返回行：outer join，子查询的not exist或not in，except集合运算符
F
上一年度的滑动合计
F
F
联接算法是指sqlserver用于处理联接的物理策略，sql支持三种联接算法:嵌套循环，合并，哈希
联接算法由联接运算符的“physical operation”指示，逻辑联接类型由“logical operation”属性指示
F
嵌套循环（nested loops）
嵌套循环算法既可以在等值联机中使用，也可以在不等联接中使用，而其他算法至少需要一个等值联接谓词
嵌套循环的逻辑类型：交叉联接，内联接，左外联接，左半和反半联接，交叉和outer apply运算符
不能用于full outer join和右外联接
F
merge join 合并
合并联接：one to many ，many to many
如果优化器能确信联接两边的一侧在联接列上具有唯一性，他就可以使用一对多的合并联接
使用一对多，sql只需要对联接的两侧输入进行一次有序的扫描，在扫描两侧输入的同时对行进行合并，他从每个输入的第一行开始扫描，
只要没有到达输入的末尾，就检查两个行是否匹配，如果匹配，则返回一个结果行，再从“多”侧中读取另一行，如果不匹配，则从具有较小值的一侧读取下一行
merge join和nested loops的区别：我的理解是虽然大家都是根据左边的表去扫描右表的匹配行，但是因为合并联接右表的数据是已经排好序的，那么只需要扫特定范围的行，要不需要扫整个右表
而且左表也是排好序的，那么左表和右表扫描的数据就更少了，相比nested loops，nested loops在进行每一次的匹配的时候，都需要对右表进行全表扫描，效率低下
当合并联接是多对多的关系时，处理过程将变得更复杂，开销也更大，优化器还可能会使用带有重绕逻辑的merge join运算符，这时必须要使用一个工作表来保存一侧输入中的行，
当另一侧存在重复的匹配行时，就能够重新使用他们
合并联接要求至少有一个联接谓词是等值联接谓词（全外联接例外）
合并联接不能用于交叉联接，但可以用于内联接，外联接，半联接，有时查询表明看起来是交叉联接，但其实是内联接
F
当联接的两表的联接列上都有索引的时候，sql会先考虑合并联接，因为联接列已经预先排好序，当表非常小的时候，就算没有索引，sql也会使用sort运算符排序，进行合并联接
哈希联接 hash join
在数据仓库中，哈希联接算法通常有效
sqlserver不允许我们显式地创建哈希索引，只能创建B树索引，在sqlserver2014，因为有内存优化表就可以创建哈希索引
在内部建立哈希表
F
优化器使用较小的那个表来做生成输入（build input），再根据联接列上的值使用一个哈希函数，将生成输入中行（与查询相关属性）分布到哈希桶（hash bucket）中，
选择适当的哈希函数，创建预先决定数量而且大小相同的哈希桶，在优化器根据生成输入构建好哈希表之后，就会扫描或探测probe另一个输入（也称为探测输入）；
为了找到匹配项，只要对联接列值应用哈希函数，根据计算结果就可以知道应该扫描哈希表的哪个哈希桶
哈希联接要求至少有一个联接谓词是等值联接谓词
哈希联接不支持交叉联接，支持内联接，所有类型的半联接
生成输入-》探测输入
偶尔，会在执行计划中看到优化器决定建立一个临时索引（index spool运算符）
当没有现成的B树索引时，建立，使用和删除临时索引的开销比创建和使用哈希表的开销更高，总之，在一些情况下，使用哈希联接算法是由于缺少现成索引，但他可能是最佳选择
F
位图筛选器
bitmap运算符
在sql2008引入了更强大的“已优化的位图筛选器”
查询优化器在merge join和hash join都可以使用位图筛选器，但已优化的位图筛选器只能在哈希联接中使用
F
Opt_Bitmap1008 :Opt_开头就是“已优化的位图筛选器”
在启动sql服务的时候使用-P参数（该参数没有文档说明），使用这个参数可以指定sql应该启动多少个用户模式调度器（UMS,USER MODE SCHEDULER）,通常sql只为每个cpu启动一个UMS
net start MSSQLSERVER /P 1
--不能强制优化器不使用特定的联接算法，这一需求在联接级别上无法实现，只--能在查询中指定联接的算法 SELECT * FROM sys.[dm_os_schedulers] OPTION (LOOP JOIN,HASH JOIN)
F
F
F
F
F
SELECT CAST(arrid AS VARCHAR(10))AS arridd, REPLACE(array,',',CHAR(13)+CHAR(10)+CAST(arrid AS VARCHAR(10))+SPACE(10)) AS value FROM [dbo].Arrays
F
集合运算
F
union distinct
union all
except distinct
except all
F
intersect distinct
intersect all
F
F
集合运算使用into
F
F
F
第八章 数据聚合和透视
over子句
F
F
决胜属性
F
F
连续聚合
F
累计聚合
F
F
F
F
滑动聚合
F
年初至今（YTD）
F
透视转换 pivot
F
F
F
关系除法
F
F
聚合数据
F
F
逆透视转换unpivot
F
F
unpivot一定要删除null行
进行逆透视转换的所有源表属性必须具有相同的数据类型，如果想对定义为不同数据类型的属性进行逆透视转换，则可以创建一个派生表或CTE，先把所有这些属性转换为SQL_VARIANT类型，这样，用于保存逆透视转换值的目标列也被定义为SQL_VARIANT类型，在这个目标列中，这些值都将保留他们原来的数据类型
http://msdn.microsoft.com/zh-cn/library/dd776382.aspx
指定要构建到某一表中的一组行值表达式。 Transact-SQL 表值构造函数允许在单个 DML 语句中指定多行数据。 该表值构造函数可以在 INSERT 语句的 VALUES 子句中指定，在 MERGE 语句的 USING <源表> 子句中指定，以及在 FROM 子句的派生表定义中指定
C.在 FROM 子句中将多个值指定为派生表
下面的示例在 SELECT 语句的 FROM 子句中使用表值构造函数指定多个值。
SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
GO
-- Used in an inner join to specify values to return.
SELECT ProductID, a.Name, Color
FROM Production.Product AS a
INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name)
ON a.Name = b.Name;
F
自定义聚合
使用透视转换的自定义聚合
注意，除非组中有一列包含序列值，否则需要先计算出行号，以标识元素在组中的位置
F
F
聚合乘积在金融应用中很常见，例如计算复利利率（compound interset rate）interset：叠加求交
F
用户定义聚合函数
1、吧UDA定义为.NET语言中的一个类
2、将定义好的类进行编译，生成一个CLR程序集
3、使用TSQL的CREATE ASSEMBLY命令，在sqlserver中注册该程序集
4、使用TSQL中的CREATE AGGREGATE命令来创建该UDA，由他引用已经注册的程序集
f
f
f
f
f
f
f
修改服务器配置允许CLR
EXEC sys.[sp_configure] @configname = 'clr enable', -- varchar(35) @configvalue = 1 -- int RECONFIGURE WITH OVERRIDE
--检查程序集是否部署成功 SELECT * FROM sys.[assemblies] SELECT * FROM sys.[assembly_modules]
product：乘积
字符串串联聚合的专用解决方案使用FOR XML选项的PATH模式
字符串串联聚合的专用解决方案
f
字符串的有序路径（ordered path），由于为path提供了一个空字符串作为输入，所以不会生成包装器（wrapper element）元素
动态行转列
f
聚合乘积的专用解决方案：对数
f
聚合位操作的专用解决方案
聚合TSQL位操作（位或（|）位与（&）位异或（^））
实际上，sqlserver内部也用位图来存储元数据信息，例如索引的属性（聚集，唯一性等等），和数据库的属性（只读，访问限制，自动收缩）
位异或是唯一一个可逆的位运算符，这就是他可以用于奇偶计算和加密的原因，解密 加密
sqlserver没有提供位运算符的聚合版本
f
f
聚合位与
f
聚合位异或
中值
统计学的中值：
两个定义
1当组中包含奇数个元素时，中值是指中间的值
2当组中包含偶数个元素时，中值是指两个中间值的平均值
USE [sss] CREATE TABLE midtest(id INT,val INT) DROP TABLE midtest INSERT midtest SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 4,4 UNION ALL SELECT 5,5 UNION ALL SELECT 6,6 SELECT * FROM midtest SELECT AVG(1.*val) FROM midtest SELECT id,[val] FROM (SELECT id, (SELECT [val] FROM midtest ORDER BY id DESC) AS a, (SELECT [val] FROM midtest ORDER BY id ASC ) AS b WHERE (a-b)<=1) SELECT [val] INTO #tt FROM midtest ORDER BY id DESC SELECT a.[val] FROM midtest AS a JOIN [#tt]AS b ON a.val-b.val<=1 ORDER BY a.id SELECT AVG(1.*val) , ROW_NUMBER() OVER(ORDER BY [id] ASC) AS rna, ROW_NUMBER() OVER(ORDER BY [id] DESC) AS rnd FROM midtest WHERE rna-rnd<=1 --------------------------------------- INSERT midtest SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 4,4 UNION ALL SELECT 5,5 --求中值 关键是两个开窗函数一个顺序，一个倒序 然后两个相减 WITH RN AS ( SELECT ID ,[val], ROW_NUMBER()OVER ( ORDER BY VAL ASC) AS RNA ,--RN ASC ROW_NUMBER()OVER ( ORDER BY VAL DESC ) AS RND --RN DESC FROM midtest ) SELECT VAL FROM [RN] WHERE ABS(RNA-[RND])<=1
f
这里有一个有意思的数学关系，可以利用这一点，只有参与中值计算的元素，其两个行号之差的绝对值才小于或等于1，
例如，在元素个数为奇数的组中，只有中间行的 ABS(RNA-RND)等于0，对于其他行，这个表达式的值都大于1，在元素个数为偶数的组中，
只有两个中间行的行号之差等于1，而其他所有行都大于1
avg(1.*val):先将val转换为浮点数 （1.*val），然后求平均值实际上如果不转换为浮点数，那么表达式是avg(val)
--计算中值 WITH RN AS ( SELECT groupid,val, ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY val,memberid) AS rna, ROW_NUMBER() OVER(PARTITION BY groupid ORDER BY val DESC,memberid DESC) AS rna FROM dbo.Groups ) SELECT groupid,AVG(1.*val) AS median FROM RN WHERE ABS(RNA-RND)<=1 GROUP BY GROUPID
f
众数(Mode)：统计分布中的众数，众数是指一组数据中出现次数最多的值
f
如果想返回所有并列值只要用rank函数代替row_number函数，dense_rank不返回并列值
f
直方图（Histogram）:直方图是一种用于展示数据分布的强大分析工具
生成直方图等级的下限和上限需要以下数据:numstep有多少步，分多少个等级，步长stepsize
f
f
因为使用inner join，所以不会返回空的等级（即空集），如果也要返回空的等级，则应该使用外联接查询
f
分组因子
f
f
分组集
在sql2008之前，在同一查询中定义多个分组集的功能作为分组选项（with cube和with rollup）和助手函数grouping实现的
sql2008使用新特性，包括group by 子句的grouping sets，cube，rollup从属子句（不要和旧的with cube，和with rollup选项搞混了）以及助手函数grouping_id(),
他们与ISO标准兼容，比旧的非标准版本也更加灵活
F
sqlserver只对数据执行最少次数的扫描，计算基础聚合，在基础聚合计算的基础上，再计算超级聚合（聚合的聚合）
不用SSAS的话，使用sql2008支持的和分组特性相关的新技术来解决问题
F
SELECT [posttime],[eventtype],SUM( [lsn]) FROM [AuditDDLEvents] GROUP BY GROUPING SETS ( ([lsn],[posttime]), ([loginname],[schemaname]), ([eventtype]) )
F
定义了一个由元素a,b,c一起组成的分组集
GROUP BY GROUPING SETS
(
(a,b,c)
)
定义了3个分组集，一个只包含元素a，一个只包含元素b，一个只包含元素c
GROUP BY GROUPING SETS
(a,b,c)
F
空分组都可以，空分组为null
SELECT [posttime],[eventtype],SUM( [lsn]) FROM [AuditDDLEvents] GROUP BY GROUPING SETS ( ([posttime]), ([schemaname]), ([eventtype]), () )
F
使用多个union来代替GROUP BY GROUPING SETS，结果是等效的
sql2008允许在一个查询中最多定义4096个分组集
cube从属子句
sql2008引入了新的cube子句（不要和with cube选项搞混了）cube从属子句只是定义大量分组集的一种简洁方式，不必真正在GROUP BY GROUPING SETS从属子句中列出所有的分组集，
cube接受一系列元素作为输入，他会定义这些元素上所有可能的分组集，包括空分组集
集合的幂集
F
rollup从属子句
cube定义的是由输入元素组成的所有可能的分组集（幂集），而rollup只定义这个幂集中的一个子集
sql2005的with rollup和sql2008的rollup不一样
F
F
分组集代数
F
F
F
在应用中可能必须要标识出查询结果集中的每一行和哪个分组相关联，依靠null占位符一定程度上可以标识出分组集，但理解起来让难懂，
更不用说如果表中的列定义为允许null，结果中的null将引起混乱，sql2008引入SELECT GROUPING_ID()函数，这个函数接受一个不同的属性
F
F
sql2008之前使用GROUPING()函数只接受一个属性名称作为输入
F
F
排序
F
F
第九章 TOP和APPLY
有两种方法：一种是指定精确的行数，范围从top(0)到top(9223372036854775807)最大bigint值，或者使用一个float值，指定要返回百分之多少的行，
范围从top(0E0) percent到top(100E0) percent。sqlserver支持在top中使用任意独立的表达式，而不仅仅是常量
F
TOP和确定性
F
保证确定性的另一种方法是使用with ties选项，当使用with ties时，查询生成的结果集将包含额外的行，这些额外行在排序列上的值与top行中最后一行的排序列值相同
当order by列表不唯一时，总可以添加主键作为决胜属性，以确保确定性
F
set roucount已经进入被废弃状态，在sqlserver的下一个版本中set roucount将不再影响数据修改语句，现在应该使用top来限制数据修改语句将影响的行数
可以在关键字delete，update，insert之后指定top
在数据修改语句中不能使用order by子句，所以数据修改语句不能依赖什么逻辑顺序，sqlserver只影响碰巧先访问到的指定数量的行
虽然order by不能和update top，delete top一起使用，但通过创建一个CTE，在CTE中使用包含order by子句的SELECT TOP查询，然后对该CTE执行update或delete，就可以客服这个限制
F
WITH cte_del AS (SELECT TOP (10) * FROM some_table ORDER BY col1) DELETE FROM [cte_del]
把一个大型的delete事务拆分成多个小事务会带来一些好处，只要足够小，就可以避免锁升级（通常事务操作数千行会触发锁升级），通过测试带有top选项的delete操作，
并同时在profiler中监视lock escalation事件，可以很容易验证选择的数字是否会导致锁升级
http://www.cnblogs.com/ajiangg/p/3877451.html
WHILE 1=1 BEGIN DELETE TOP (5000) FROM TB WHERE SYSDATE <= '2013-12-31' IF @@ROWCOUNT < 5000 BREAK; WAITFOR DELAY '00:00:01' END
把大型delete拆分到多个批次中，为什么是5000，5000个锁升级为表锁，<5000就可以不升级到表锁
这样可以达到delete最多数据，也可以不升级为表锁，提高并发
每个删除5000行的操作都是在一个单独的事务中执行的
F
--批量修改只能在where字段和set字段一样的时候进行 WHILE 1 = 1 BEGIN UPDATE TOP ( 5000 ) dbo.LargeOrders SET custid = 123 WHERE custid = 55 IF @@ROWCOUNT < 5000 BREAK END
F
APPLY
APPLY 表运算符的作用是把右表表达式应用到左表表达式中的每一行，他不像联接那样可以先计算任一个表表达式，APPLY必须先逻辑地计算左表表达式，
通过对输入按逻辑顺序计算，能够把右表表达式关联到左表表达式
使用with ties的top选项以保证结果集的确定性
F
apply运算符有两种形式：cross apply和outer apply，他们分别类似于联接运算符的Inner join和left outer join
sqlserver引擎为支持apply运算符而采用的技术还带来个好的作用，可以把外部查询的列引用作为参数传递给表值函数
SELECT b.[text] FROM sys.[dm_exec_cached_plans] AS a CROSS APPLY sys.[dm_exec_sql_text]([a].[plan_handle]) AS b
一个决胜属性的问题，他要求返回每个雇员的最新订单，这个问题实际上是“为每个组返回前N行”
F
F
F
使用apply运算符，比子查询和内联接性能还要好
F
row_number()函数，先为每个订单计算行号，按empid进行分区，并按orderdate desc，orderid desc的顺序进行排序，然后在外部查询中，只筛选行号小于或等于3的行
根据orderid进行分组，再根据qty排序，productid作为决胜列，生成行号
SELECT orderid,productid,qty FROM (SELECT ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY qty DESC,productid) AS rownum, orderid,productid,qty FROM sales.orderdetails) AS D WHERE [rownum]<=3
F
匹配当前值和前一个值
F
F
F
多页分页 连续分页
上一页 下一页 单独用一个存储过程来实现方便执行计划重用
F
当访问完指定数量的行时，TOP运算符就停止扫描
下一页要提供锚点，出于确定性的原因，排序值必须是唯一的
F
F
随机行 返回随机行 返回随机行数时候可以尝试使用rand函数
F
getdate（），rand（）非确定性函数，每个查询只调用他们一次，而不是每行调用一次，这样所有行最终得到的rand值都相同，order by子句不会影响查询结果集的排序
《Microsoft SQL Server 2005技术内幕： T-SQ程序设计 笔记 》 逐行调用udf
如果想每行都调用rand函数可以使用udf
可以使用偏方，创建一个视图，该视图调用rand（）函数，然后在udf中查询该视图
f
如果为rand函数提供一个整数种子作为输入，结果可能让你吃惊，他并不是真正非确定性的，而是一种哈希函数hash function，对于相同的种子，
rand(<rand>)总会生成相同的结果，例如
SELECT RAND(5)
得到的结果是
0.713666525097956
--要得到随机值，下面的表达式会更好 SELECT CHECKSUM(NEWID()) --要得到从1到@n之间的一个随机值，可以这样写 SELECT ABS(CHECKSUM(NEWID()))%10+1 --10为n值
newid函数具有更好的分布特性，然而，到目前为止，还没有发现微软有任何文档支持这一点
有意思的是，不同与其他非确定性函数，如果在查询中调用newid函数，会为每一行单独计算newid的值，因此可以在order by子句中使用上述表达式来获取随机值
SELECT * FROM [dbo].[testrow] ORDER BY CHECKSUM(NEWID())
SELECT TOP (100e0*(CHECKSUM(NEWID())+2147483649/4294967296e0)) PERCENT FROM [dbo].[testrow]
F
中值
当元素是奇数时，用于偶数情况的两个子查询将返回相同的行，两个相等值的平均数显然还是同一个值，意思是基数只有一个中值，
把基数的情况可以看作是偶数的特殊情况，前一半的最大值和后一半的最小值都是同一个数
F
逻辑转换
F
F
将or改为and
把or逻辑改为and逻辑
当在or前面的条件一不满足时，or后面的列没有索引的话就会扫描全表
F
第十章 数据修改
增强的values子句
用values子句定义虚拟派生表，用values子句构造虚拟表，所以也称为表值构造器（table value constructor），
而在子句中指定的每一行则称为行值构造器（row value constructor）
F
insert values子句在内部要经过平展（由代数组件执行），就像insert select语句一样，使用union all集合运算把单独的各行统一起来，
在mysql里也是一样，多个values子句作为一个事务，插入速度会快很多
VALUES子句定义派生表
SELECT * FROM (VALUES (1,'s'),(2,'y')) AS C(ID,NAME)
--C.在 FROM 子句中将多个值指定为派生表 --下面的示例在 SELECT 语句的 FROM 子句中使用表值构造函数指定多个值。 SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b); GO -- Used in an inner join to specify values to return. SELECT ProductID, a.Name, Color FROM Production.Product AS a INNER JOIN (VALUES ('Blade'), ('Crown Race'), ('AWC Logo Cap')) AS b(Name) ON a.Name = b.Name;
values子句不支持CTE
--将select语句作为子查询嵌在insert语句的values子句中 INSERT INTO [dbo].[counttb] ( [id] , [TESTDATE] ) VALUES ( ( SELECT a FROM [dbo].[aaa] WHERE [a] = 2 ) , -- id - int GETDATE() -- TESTDATE - datetime ) SELECT a FROM [dbo].[aaa] WHERE [a]=2 SELECT * FROM [dbo].[counttb] --通常做法分两步 DECLARE @a AS INT SELECT @a = a FROM [dbo].[aaa] WHERE [a] = 1 INSERT INTO [dbo].[counttb] ( [id], [TESTDATE] ) VALUES ( @a, -- id - int GETDATE() -- TESTDATE - datetime )
select into不会复制约束，索引，触发器，如果要具有和源表一样的索引，约束，触发器，必须在执行完之后再添加他们
因为select into是sqlserver自动添加一张新表，新表肯定没有数据，而且不会复制约束，索引，触发器，而且只能
复制所有列，这在sqlserver控制范围之内，所以可以应用最小化日志记录
而insert select 目标表可能已经有数据，sqlserver不能控制，需要用户添加TF610 或tablock提示
为什麽不是有tablockx
f
《深入解析sqlserver2008》
标识列会被复制
CREATE TABLE testselectinto(id INT IDENTITY PRIMARY KEY ,name NVARCHAR(200)) INSERT INTO [dbo].[testselectinto] ( [name] ) SELECT '1212' GO 10 SELECT * INTO TESTSELECTINTO123 FROM [dbo].[testselectinto] INSERT INTO [dbo].[TESTSELECTINTO123] ( [name] ) SELECT '1212' GO 2 SELECT * FROM [dbo].[TESTSELECTINTO123]
F
select into会锁系统表，这点非常严重！！
在select into事务执行期间，系统表中的sysobject表，syscolumn表也要获得排他锁，
当处理的是一个大表的时候数据和元数据都要保持排他锁，对sys.objects或sys.columns进行完整扫描，事务也会被阻塞
在sql2008才引入了对insert select的最小日志记录的支持，数据库处于非full模式
--将动态sql或存储过程的结果集插入新表可以使用open query语句 --创建linked server --将linked server的名字填写到@server的地方 EXEC sys.[sp_serveroption] @server = <你的服务器>, -- sysname @optname = 'data access', -- varchar(35) @optvalue = N'true' -- nvarchar(128) SELECT * INTO [test] FROM OPENQUERY(<你的服务器>,'EXEC 存储过程或动态SQL') AS O
本地计算机名：joe --创建链接服务器 --要开启分发服务器上的Distributed Transaction Coordinator（MSDTC服务） USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'JOE_DIST', @srvproduct=N'sqlserver', @provider=N'SQLOLEDB', @datasrc=N'JOE' --计算机名 joe 表示创建一个自己电脑链接自己电脑的linked server GO EXEC master.dbo.sp_serveroption @server=N'JOE_DIST', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'JOE_DIST', @optname=N'rpc out', @optvalue=N'true' GO USE [master] GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'JOE_DIST', @locallogin = NULL , @useself = N'False', @rmtuser = N'sa', --要求是对distribution有db_owner权限的 实际应用时最好不要用sa @rmtpassword = N'testxxx' GO EXEC sys.[sp_serveroption] @server = 'JOE_DIST', -- sysname @optname = 'data access', -- varchar(35) @optvalue = N'true' -- nvarchar(128) SELECT * INTO [test] FROM OPENQUERY( 'JOE_DIST','EXEC 存储过程或动态SQL') AS O
F
BULK行集提供程序
SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', FORMATFILE = 'c:\test\values.fmt') AS a;
使用 BULK INSERT 或 OPENROWSET(BULK...) 导入大容量数据(SQL Server)
还可以使用其他引擎SQLNCLI和Microsoft.Jet.OLEDB.4.0
SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;', 'SELECT GroupName, Name, DepartmentID FROM AdventureWorks2012.HumanResources.Department ORDER BY GroupName, Name') AS a; SELECT CustomerID, CompanyName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; 'admin';'',Customers); GO
指定和大容量读取有关的其他选项：codepage，errorfile，firstrow，lastrow，maxerrors，rows_per_batch
ignore_constraints只针对bulk insert，bcp这些大容量导入bulk insert，而不能用于Trickle Insert，所以上次测试的时候为什麽会失败
insert select from openrowset(bulk..)语句比bulk insert或bcp.exe工具好的原因是可以在语句上使用join，apply，pivot，unpivot，where筛选，group by分组 等表运算符
F
数据类型：single_clob 普通的字符数据,single_nclob unicode数据,single_blob二进制数据
F
最小化日志记录：select into，索引操作，对大型对象值的操作，bulk insert，bcp.exe，insert select from openrowset（bulk...）,insert select（sql2008的新功能）
更多最小化日志的文章参考《sqlserver存储引擎博客》
http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/bulk+import_2f00_export/default.aspx
对于select into，唯一的要求是非full模式就可以启用最小日志了
其他的插入方法（bulk insert，bcp.exe，insert select from openrowset（bulk...）,insert select ）都会有一些额外的要求，才能使用最小化日志
F
bulk insert，bcp.exe，insert select from openrowset都支持大容量更新锁和定义批大小，设置导入多少行才提交，
而insert select只支持表排他锁，没有批大小这些选项
分析日志记录的没有文档说明的fn_dblog函数
fn_dblog函数接受两个参数，（一个指定起始的日志序列号，另一个指定结束的日志序列号），返回指定范围内的所有日志记录，
要得到事务日志中的所有记录，可以把两个输入参数都设置为null
CHECKPOINT; GO DECLARE @NUMRECORDS AS INT , @SIZE AS BIGINT , @DT AS DATETIME SELECT @NUMRECORDS = COUNT(*) , @SIZE = COALESCE(SUM([Log Record Length]), 0) , @DT = CURRENT_TIMESTAMP FROM sys.[fn_dblog](NULL, NULL) AS D WHERE [AllocUnitName] = 'TB' OR [AllocUnitName] LIKE '%TB%' --操作 SELECT COUNT(*) - @NUMRECORDS AS numrecords , CAST(( COALESCE(SUM([Log Record Length]), 0) - @SIZE ) / 1024. / 1024. AS NUMERIC(12, 2)) AS size_mb , CAST(DATEDIFF(MILLISECOND, @DT, CURRENT_TIMESTAMP) / 1000. AS DECIMAL(12, 3)) AS duration_sec FROM sys.[fn_dblog](NULL, NULL) AS D WHERE [AllocUnitName] = 'TB' OR [AllocUnitName] LIKE '%TB%'
F
使用直方图来分析事务日志
要使用到Nums辅助表
DECLARE @numsteps AS INT= 10; DECLARE @log AS TABLE ( id INT IDENTITY , size INT , PRIMARY KEY ( size, id ) ); INSERT INTO @log ( [size] ) SELECT [Log Record Length] FROM sys.[fn_dblog](NULL, NULL) AS D WHERE [AllocUnitName] = 'TB' OR [AllocUnitName] LIKE '%TB%'; WITH Args AS ( SELECT MIN([size]) AS mn , MAX([size]) AS mx , 1E0 * ( MAX([size]) - MIN([size]) ) / @numsteps AS stepsize FROM @log ), Steps AS ( SELECT n , mn + ( n - 1 ) * stepsize - CASE WHEN n = 1 THEN 1 ELSE 0 END AS lb , mn + n * stepsize AS hb FROM Nums CROSS JOIN Args WHERE n <= @numsteps ) SELECT n , lb , hb , COUNT(size) AS numrecords FROM Steps LEFT OUTER JOIN @log ON [size] > lb AND size <= hb GROUP BY n , lb , hb ORDER BY n;
--日志的聚合信息，列出了日志记录的平均长度（以100字节为单位），日志操作上下文 SELECT [Operation] , [Context] , AVG([Log Record Length]) AS AvgLen , COUNT(*) AS cnt FROM sys.[fn_dblog](NULL, NULL) AS D WHERE [AllocUnitName] = 'TB' OR [AllocUnitName] LIKE '%TB%' GROUP BY [Operation] , [Context] , ROUND([Log Record Length], -2) ORDER BY AvgLen , [Operation] , [Context]
--按对数级别(10s 100s 1000s)来划分明细列表 先计算包含日志记录长度的字符串长度，并以此作为分组表达式 USE [sss] SELECT [Operation] , [Context] , '1' + REPLICATE('0', -1 + LEN([Log Record Length])) + 's' AS [log entry sizes] , AVG([Log Record Length]) AS AvgLen , COUNT(*) AS cnt FROM sys.[fn_dblog](NULL, NULL) AS D WHERE [AllocUnitName] = 'aaa' OR [AllocUnitName] LIKE '%aaa%' GROUP BY [Operation] , [Context] , LEN([Log Record Length]) ORDER BY AvgLen , [Operation] , [Context]
创建好数据库之后，设置恢复模式为full，并备份数据库以截断日志的不活动部分
F
F
有25000条日志记录的日志操作lop为lop_format_page，日志上下文lcx为lcx_heap，这表明select into操作期间分配和填充了25000个堆页
lop_set_bits的上下文是lcx_gam,lcx_iam,lop_modify_row的上下文是lck_pfs，从这些了解到，在修改gam，iam，pfs页面
F
F
F
F
这个insert select语句带了一个order by子句，他可以确保目标B树的顺序来插入数据，在这个特殊的例子中，order by子句在优化方面可能起不到多大作用
F
F
操作是lop_delete_split ,上下文是lcx_clustered
操作是lop_delete_split ,上下文是lcx_index_interior
页拆分
interior：内部
打开跟踪标志610
--设置跟踪标志TF-610 DBCC TRACEON(610,-1) DBCC TRACESTATUS
如果想在sqlserver启动时就设置这一标志，可以在服务启动参数中指定“-T 610”
上下文是lcx_bulk_operation_page,操作是lop_modify_header
为了平衡B树，这个案例需要的日志量会多一些
F
insert select ，非空B树，tablock，合并的键值范围，这个案例需要把具有新键值的行合并到现有的页面中，在这个案例中，
无论是否打开TF-610标志，插入现有页中的行都需要进行完整日志记录
由于页拆分而导致行的移动也带来了大量的日志记录（25001次，每次大约4000字节），总的日志量超过了300MB
F
F
F
F
F
有一个疑问：为什麽在“简单恢复”模式，事务执行完毕，事务日志就会马上截断，为什麽还可以统计日志记录的数量
日志的具体操作呢？？ alter database testdb set recovery simple;
记住：有时会遇到两种情况混在一起的情形：一些要插入的键值范围是新的，一些插入的行要插入到现有页面，对于键值范围是新的那些行，
当打开TF-610时，无论是否使用了tablock提示，得到的都是最小化日志记录，对于插入到现有页的行，得到的总是完整的日志记录
F
insert select方法有时要优于select into，因为insert select 不会锁定元数据，而且还可以控制要创建的目标表的结构，而select into只能全部字段创建
sql2008引入了对最小日志记录的支持，当把数据插入到一个非空的B树（聚集或非聚集索引）时，当打开了TF-610,无论是否指定了tablock提示，
如果插入的新键值范围分配填充了新页面，会使用最小日志记录，对于这些新的键值范围，sqlserver内部会采用键范围锁（key-range lock），以确保其他进程不会运行冲突的插入操作
键范围锁定
http://msdn.microsoft.com/zh-cn/library/ms191272(v=SQL.105).aspx
rangeI-N
insert exec 和merge语句目前还不支持按最小化方式记录日志
F
使用#局部临时表实现分页缓存，不需要再查询基表
F
F
序列机制
identity属性 局限
标识值资源只在递增数值的瞬间由内部锁定，而事务持续期间并不锁定该资源，这样做成即使没有删除操作，序列值也会出现间断
F
自定义序列
update dbo.Sequence set @val=val=val+1
F
使用newid（）函数生成GUID:16字节的UNIQUEIDENTIFIER值
SELECT NEWID() --52F5801C-3155-43F8-9A6F-1B7553163300
F
建立一个保存点 save tran S1
rollback tran S1
F
newid（）生成的16字节值太长，会影响联接性能，而且不一定大于前一个值
可以使用新的 SELECT NEWSEQUENTIALID() 函数，但 SELECT NEWSEQUENTIALID() 只保证在一台计算机上生成的值一定大于之前生成的值，但不保证在多台计算机上也会这样
F
truncate和delete的比较
删除重复数据行
F
基于联接的delete
F
F
DECLARE @myod TABLE ( orderid INT NOT NULL , productid INT NOT NULL , PRIMARY KEY ( orderid, [productid] ) ) INSERT INTO @myod ( [orderid], [productid] ) VALUES ( 100, -- orderid - int 16 -- productid - int ) DELETE FROM @myod WHERE EXISTS ( SELECT * FROM @myod WHERE [orderid]=100 ) --sql2008r2没有报错 DELETE FROM myod FROM @myod AS myod WHERE EXISTS ( SELECT * FROM @myod WHERE [orderid]=100 ) ----------------------------------------- DELETE FROM myod FROM @myod AS myod WHERE EXISTS ( SELECT * FROM @myod WHERE [orderid]=1 ) SELECT * FROM @myod
在sql2008中，还可以使用新的merge语句来处理基于delete的联接
更新数据
基于联接的update
F
tsql不支持下面语法
UPDATE orders SET (shipcountry,shipregion,shipcity)= (SELECT country,region,city FROM customer) WHERE custid IN(SELECT custid FROM customers WHERE country='USA')
F
在sql2008中使用merge语句也可以实现基于update的联接语句
用merge语句执行更新，对同一目标行修改超过1次，sqlserver就会报错，语句执行失败，merge语句不允许执行这样的非确定性更新，而基于联接的update则可以进行这样的更新
F
更新大值数据类型
F
select和update语句赋值
F
F
再提一次，没有任何官方文档定义过多行赋值select语句的行为，更不用说在这样的语句中包含order by子句了
微软的conor cunningham写了一个博客
http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx
他在博客中表明这种没有文档说明的技术确实可以确保聚合串接的顺序
F
如果你不想按任何特定顺序来分配行号，以节省和排序有关的开销，则可以在row_number()指定order by (select 0)
sql2008引入 merge语句 合并数据
F
在oltp环境中，可以用merge语句把从外部数据源获取的数据合并到现有的目标表中，
在数据仓库olap中，可以用merge语句对聚合数据进行增量更新，处理缓慢变化维（slowly changing dimensions）等等
示例数据
USE [sss] GO CREATE TABLE [dbo].Customers ( custid INT NOT NULL, companyname VARCHAR(25) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, inactive BIT NOT NULL DEFAULT(0), CONSTRAINT PK_Customers PRIMARY KEY(custid) ) CREATE TABLE [dbo].CustomersStage ( custid INT NOT NULL, companyname VARCHAR(25) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(50) NOT NULL, CONSTRAINT PK_CustomersStage PRIMARY KEY(custid) ) INSERT INTO [dbo].Customers(custid,companyname,phone,[address]) VALUES (1,'cust 1','(111)111-1111','address 1'), (2,'cust 2','(222)222-2222','address 2'), (3,'cust 3','(333)333-3333','address 3'), (4,'cust 4','(444)444-4444','address 4'), (5,'cust 5','(555)555-5555','address 5') INSERT INTO [dbo].CustomersStage(custid,companyname,phone,[address]) VALUES (2,'AAAAA','(222)222-2222','address 2'), (3,'cust 3','(333)333-3333','address 3'), (5,'BBBBB','CCCCC','DDDDD'), (6,'cust 6(NEW)','(666)666-6666','address 6'), (7,'cust 7(NEW)','(777)777-7777','address 7') SELECT * FROM [dbo].CustomersStage SELECT * FROM [dbo].Customers
F
在merge into子句中需要指定操作的目标，他可以是表或视图，在Using子句中需要指定操作的数据来源，这里的Using子句类似于select查询中的from子句，
也可以指定表表达式（视图，派生表，CTE，表值函数 TVF openrowset，openxml），在Using子句也可以用表运算符，如join，apply，pivot，unpiovt来指定数据来源
on指定匹配谓词，when matched then 指的是on为true 可以执行update和delete
when not matched [by target] then 指的是on为unknown或false， 只能执行insert操作
F
USE [sss] SET NOCOUNT OFF BEGIN TRAN MERGE INTO [dbo].[Customers] AS TGT USING [dbo].[CustomersStage] AS SRC ON TGT.CUSTID=SRC.CUSTID WHEN MATCHED THEN UPDATE SET TGT.COMPANYNAME=SRC.COMPANYNAME WHEN NOT MATCHED THEN INSERT (custid,companyname,phone,address) VALUES (SRC.CUSTID,SRC.COMPANYNAME,SRC.PHONE,SRC.ADDRESS); --消息 10713，级别 15，状态 1，第 13 行 --MERGE 语句必须以分号(;)结尾。 SELECT * FROM [Customers] ROLLBACK TRAN
F
USE [sss] SET NOCOUNT OFF BEGIN TRAN MERGE INTO [dbo].[Customers] AS TGT USING [dbo].[CustomersStage] AS SRC ON TGT.CUSTID=SRC.CUSTID WHEN MATCHED THEN UPDATE SET TGT.COMPANYNAME=SRC.COMPANYNAME; --消息 10713，级别 15，状态 1，第 13 行 --MERGE 语句必须以分号(;)结尾。 SELECT * FROM [Customers] ROLLBACK TRAN
显然，使用merge语句的优点是不需要访问数据两次，而且merge语句是作为原子操作进行处理的，避免了显式声明事务的需要
F
额外增加谓词
避免触发器在inserted表和deleted表中包含这些行
F
USE [sss] SET NOCOUNT OFF BEGIN TRAN MERGE INTO [dbo].[Customers] AS TGT USING [dbo].[CustomersStage] AS SRC ON TGT.CUSTID = SRC.CUSTID WHEN MATCHED AND ( TGT.COMPANYNAME <> SRC.COMPANYNAME ) THEN UPDATE SET TGT.COMPANYNAME = SRC.COMPANYNAME; SELECT * FROM [Customers] ROLLBACK TRAN
根据SQL中的三值逻辑，表达式可以返回true，false，unknown，当参与运算的其中一方为null时，才会得到unknown
steve kass向微软提出了一个增强功能的请求，以实现ansi sql的一些支持两值逻辑的运算符
http://connect.microsoft.com/SQLServer/feedbackdetail/view/286422/add-language-and-optimizer-support-for-iso-distinct-predicate
SELECT T1.this, T2.that FROM T1 JOIN T2 ON EXISTS( SELECT T1.entry INTERSECT SELECT T2.entry);
http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx
merge语句最多可以支持两个when matched子句，当使用两个when matched子句时，第一个子句必须带有一个额外的谓词，而第二个子句既可以带有一个额外的谓词，也可以不带有额外的谓词，如果同时指定了两个when子句，只有当on谓词和第一个when子句的额外谓词均为true，merge语句才会应用第一个when子句中的操作，如果on谓词为true，但第一个when子句的额外谓词为false或unknown，则继续处理第二个when子句
USE [sss] SET NOCOUNT OFF BEGIN TRAN MERGE INTO [dbo].[Customers] AS TGT USING [dbo].[CustomersStage] AS SRC ON TGT.CUSTID = SRC.CUSTID WHEN MATCHED AND ( TGT.COMPANYNAME <> SRC.COMPANYNAME ) THEN UPDATE SET TGT.COMPANYNAME = SRC.COMPANYNAME WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT ( custid , companyname , phone , address ) VALUES ( SRC.CUSTID , SRC.COMPANYNAME , SRC.PHONE , SRC.ADDRESS ); SELECT * FROM [Customers] ROLLBACK TRAN
只支持一个WHEN NOT MATCHED
F
WHEN NOT MATCHED BY SOURCE：当一个目标行找不到与之匹配的来源行时，应该采取的操作
USE [sss] SET NOCOUNT OFF BEGIN TRAN MERGE INTO [dbo].[Customers] AS TGT USING [dbo].[CustomersStage] AS SRC ON TGT.CUSTID = SRC.CUSTID WHEN MATCHED AND ( TGT.COMPANYNAME <> SRC.COMPANYNAME ) THEN UPDATE SET TGT.COMPANYNAME = SRC.COMPANYNAME WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT ( custid , companyname , phone , address ) VALUES ( SRC.CUSTID , SRC.COMPANYNAME , SRC.PHONE , SRC.ADDRESS ) WHEN NOT MATCHED BY SOURCE THEN --更新目标行 UPDATE SET inactive = 1; SELECT * FROM [Customers] SELECT * FROM [CustomersStage] ROLLBACK TRAN
F
merge values
无论使用 if exist和insert，还是检查@@rowcount值和insert之间都有可能有另一个事务有机会引入一个新行，
为了解决这个问题，你可能用序列化隔离级别，都会造成很多死锁
在sql2008可以使用merge来实现
WHEN NOT MATCHED BY SOURCE：也支持delete和update操作，可以最多指定两个WHEN NOT MATCHED BY SOURCE子句，
其使用规律类似于使用两个WHEN MATCHED 子句时的规则
F
USE [sss] GO CREATE PROCEDURE dbo.AddCust ( @custid INT , @companyname VARCHAR(25) , @phone VARCHAR(20) , @address VARCHAR(50) ) AS MERGE [dbo].[Customers] AS TGT USING ( VALUES ( @custid , @companyname , @phone , @address ) ) AS SRC ( custid, companyname, phone, address ) ON TGT.custid = SRC.custid WHEN MATCHED AND ( TGT.COMPANYNAME <> SRC.COMPANYNAME ) THEN UPDATE SET TGT.COMPANYNAME = SRC.COMPANYNAME WHEN NOT MATCHED THEN INSERT ( custid , companyname , phone , address ) VALUES ( SRC.CUSTID , SRC.COMPANYNAME , SRC.PHONE , SRC.ADDRESS ); BEGIN TRAN EXEC [dbo].AddCust @custid = 8, @companyname = 'CUST 8(NEW)', @phone = '888888', @address = 'address 8' SELECT * FROM [dbo].[Customers] ROLLBACK TRAN
F
注意：即使两个不同的子句都调用了update操作，但对于所有更新过的行，update触发器只调用了一次，在sqlserver中，一个触发器只为每条语句触发一次，
上面的结果与这一事实相一致
output子句
output子句可以用在insert，update，delete，merge
在merge语句中，可以引用什么表要取决于语句调用的操作，输出的结果可以返回到调用者（客户端应用程序），表或者两者都有
F
带有output的insert
对于一个具有标识列的表，如果用一条insert语句一次插入多行，同时又想捕获新的标识值，这时使用带有output子句的insert语句就非常方便，
对于多行insert，可以使用output子句返回新的标识值，或将他们插入到一个表中
这个CustomerDim表代表数据仓库中的客户维度
为什么不用自然键而用代理键
在该insert语句将带有两个output子句
DECLARE @NewCusts TABLE ( custid INT NOT NULL PRIMARY KEY , KEYcol INT NOT NULL UNIQUE ); INSERT INTO [dbo].CustomersDim(custid,companyname) OUTPUT [Inserted].custid,[Inserted].keycol INTO @NewCusts --OUTPUT [Inserted].custid,[Inserted].keycol SELECT custid,[Company] FROM Sales.Customers WHERE country=N'UK' SELECT custid,keycol FROM @NewCusts
F
带有output的delete
F
带有output的update
当使用output子句把输出保存到一个表时，这个表不能具有触发器或check约束，也不能是某个外键约束的任何一侧，
如果目标表无法满足这些要求，可以把输出放到临时表，再插到基表
为了保证select 和delete之间不会新增匹配筛选器的行（幻读），必须用可序列化隔离级别来锁定要存档的数据，
而使用output子句，不仅可以获得更好的性能，而且也不用担心幻读问题，因为sqlserver能保证你能从output子句中准确获取删除的数据
F
SELECT ABS(CHECKSUM(NEWID()))
F
带有output的merge
merge语句的output，merge可以调用多个修改操作，你需要确定一个输出行是由insert，delete，update中的哪个操作生成的，
为此，sqlserver提供了$action函数，他可以为输出行返回一个字符串，表示生成该输出行的操作：“insert”，“delete”，“update”
BEGIN TRAN MERGE INTO dbo.Customers AS TGT USING [dbo].CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED THEN UPDATE SET TGT.COMPANYNAME = SRC.COMPANYNAME WHEN NOT MATCHED THEN INSERT ( custid , companyname , phone , address ) VALUES ( SRC.custid , SRC.companyname , SRC.phone , SRC.address ) OUTPUT $action AS action , [Inserted].custid , [Deleted].companyname AS ccompanyname , [Inserted].companyname AS icompanyname; ROLLBACK TRAN
F
可组合的DML
你需要修改数据，但只想为修改过的行的一个子集生成输出行，例如你只想审核一些符合条件的行
composable DML
insert into 目标表 select... from (<带有output的修改语句>) as D where <筛选谓词>
目标可以是持久化表，临时表，表变量，但是不能是表表达式，如视图，不能带有触发器，不能有主外键关系，不能参与合并复制 或事物复制的可更新订阅
F
CREATE TABLE [dbo].CustomersAudit ( custid INT NOT NULL ) ------------------------------------------------------ BEGIN TRAN INSERT INTO CustomersAudit(custid) SELECT custid FROM (MERGE INTO dbo.Customers AS TGT USING [dbo].CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED THEN UPDATE SET TGT.COMPANYNAME = SRC.COMPANYNAME WHEN NOT MATCHED THEN INSERT ( custid , companyname , phone , address ) VALUES ( SRC.custid , SRC.companyname , SRC.phone , SRC.address ) OUTPUT $action AS action , [Inserted].custid , [Deleted].companyname AS ccompanyname , [Inserted].companyname AS icompanyname) AS D WHERE ACTION='INSERT' SELECT * FROM CustomersAudit ROLLBACK TRAN
merge语句不能直接select的
BEGIN TRAN SELECT * FROM (MERGE INTO dbo.Customers AS TGT USING [dbo].CustomersStage AS SRC ON TGT.custid = SRC.custid WHEN MATCHED THEN UPDATE SET TGT.COMPANYNAME = SRC.COMPANYNAME WHEN NOT MATCHED THEN INSERT ( custid , companyname , phone , address ) VALUES ( SRC.custid , SRC.companyname , SRC.phone , SRC.address ) OUTPUT $action AS action , [Inserted].custid , [Deleted].companyname AS ccompanyname , [Inserted].companyname AS icompanyname) AS D WHERE ACTION='INSERT' -- 消息 10729，级别 15，状态 1，第 2 行 --在非 INSERT 语句直接行源的 SELECT 语句中不允许嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句。 ROLLBACK TRAN
F
第十一章 查询分区表
分区减少操作时间：备份，DBCC CHECKFILEGROUP,索引维护只重组某一分区的索引
分区视图
如果组成分区视图的所有表都位于同一个sql实例上，则称这种分区视图为本地分区视图，如果各个表位于两个或多个sql实例上，则称这种分区视图为分布式分区视图
F
分区表和分区视图的区别：
1、数据定义语言的不同
2、查询编译执行方面
分区表
TPC-H表，TPC-H是有transaction processing performance council(TPC)事务性能处理委员会定义的一套决策支持性能基准测试
www.tpc.org
F
F
sql2005和sql2008分区表的执行计划的差别
常量扫描运算符列举访问到的各个分区，再通过表扫描访问各个分区
sql2008的执行计划比sql2005少了常量扫描（枚举分区）和嵌套循环（依次对每个分区执行扫描）
sql2005里存储引擎对上层的分区表一无所知
F
在sql2008r2里面无实际分区数
F
sqlserver要求在交换分区中的目标表的必须是空的，还有表索引要对齐
对于交换分区时的源表，相当于switch out
对于交换分区时的目标表，相当于switch in
交换分区之后，检查 SELECT [rowmodctr] FROM sys.[sysindexes]
[rowmodctr] 列增加的值等于switch out或switch in操作中切换了的行的数量
统计信息的变化
1、表的大小从0行增加到多行
2、原来收集统计信息时，表的行数是500或更少，而一个或多个switch命令累计增加或删除行数超过500
3、原来收集统计信息时，表的行数超过500，[rowmodctr]列的变化量超过500，而且其中20%的行在表中
《深入解析sql2008》 [rowmodctr] ：row modify counter sql2005
F
ALTER DATABASE [sss] SET AUTO_UPDATE_STATISTICS EXEC sys.[sp_autostats] @tblname = N'', -- nvarchar(776) @flagc = '', -- varchar(10) @indname = NULL -- sysname USE AdventureWorks2008R2; GO CREATE STATISTICS ContactMail1 ON Person.Person (BusinessEntityID, EmailPromotion) WITH SAMPLE 5 PERCENT,NORECOMPUTE; --NORECOMPUTE --为 statistics_name 禁用自动统计信息更新选项 AUTO_STATISTICS_UPDATE。如果指定此选项，则查询优化器将完成 statistics_name 的任何正在进行中的统计信息更新并禁用将来的更新。 --若要重新启用统计信息更新，请使用 DROP STATISTICS 删除统计信息，然后运行 CREATE STATISTICS 但不使用 NORECOMPUTE 选项。 --CREATE STATISTICS statistics_name --ON { table_or_indexed_view_name } ( column [ ,...n ] ) -- [ WHERE <filter_predicate> ] -- [ WITH -- [ [ FULLSCAN -- | SAMPLE number { PERCENT | ROWS } -- | STATS_STREAM = stats_stream ] [ , ] ] -- [ NORECOMPUTE ] -- ] ; --<filter_predicate> ::= -- <conjunct> [AND <conjunct>] --<conjunct> ::= -- <disjunct> | <comparison> --<disjunct> ::= -- column_name IN (constant ,…) --<comparison> ::= -- column_name <comparison_op> constant --<comparison_op> ::= -- IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< UPDATE STATISTICS
如果需要经常执行switch操作，最好关闭自动更新统计信息选项，用异步更新，在插入或删除大量行的过程中只手工运行一次更新
如果在单独的时间窗口定期从表中删除或增加大量行，应该考虑在数据修改结束时为受影响的表更新统计信息
在sqlserver7.0的时候，统计信息只是在创建索引时生成的副产品
这种逻辑一直持续到从7.0到2008
F
在分区表上创建分区索引时，sqlserver现在的做法是为每个分区创建一个单独的索引树
当分区索引的前导列和分区列不是同一列时，sql2005和sql2008在创建索引时都无法创建正确的直方图（histogram），
在以后发布的sqlserver补丁包或新版本sqlserver中可能会修复这个问题
要慎重选择索引的第一个字段，最好选择一个重复记录最少的字段。这是因为-》索引上的统计信息只保存索引第一个字段/索引前导列的数据直方图，《SQL Server2012实施与管理实战》
（直方图是索引中键值的抽样图，帮助sqlserver选择哪个最合适的索引）
https://www.cnblogs.com/lyhabc/articles/3914213.html
select * from tb where cola=xx and colb=xx and colc=xx
create index ixa(cola,colc)
create index ixb(colb,colc)
数据直方图可以协助sqlserver判断到底是使用ixa还是使用ixb
假如cola字段的重复值很多，colb字段的重复值很少，那么sqlserver可以通过直方图选择使用索引ixb
唯一索引没有直方图，因为唯一索引没有重复值
--从元数据表查询分区列不是索引的前导列的所有分区索引 SELECT OBJECT_NAME(IX.[object_id]) AS TABLE_NAME , [IX].[name] AS INDEX_NAME FROM sys.[index_columns] AS IC JOIN SYS.[indexes] AS IX ON IC.[object_id] = [IX].[object_id] AND [IX].[index_id] = [IC].[index_id] WHERE IC.[partition_ordinal] = 1 AND IC.[key_ordinal] <> 1
分区消除
在sql2008中，枚举分区的常量扫描运算符不会图形化地显示出来
F
为range列PtnId1000计算第一个Range表达式，将生成正确的分区号，而聚集索引查找运算符只会访问这个分区，因为每个分区都会有独立的索引树
F
F
F
F
http://www.orczhou.com/index.php/2012/11/mysql-innodb-source-code-optimization-1/
2 SQL语句到MySQL的内部对象 Bison在做语法解析后，会将解析结果（一颗解析树/AST）存储在THD::LEX中。这里将通过考察存储WHERE的数据结构来查看语法解析的结果。 2.1 著名的Item对象 在了解MySQL的解析树之前，我们需要先来认识一个重要的数据结构Item。这是一个基础对象，在优化器部分代码，满地都是。在MySQL Internal Manual中也单独介绍：The Item Class。 Item是一个基础类，在他的基础上派生了很多子孙。这些子类基本描述所有SQL语句中的对象，他们包括： •一个文本字符串/数值对象 •一个数据表的某一列（例如，select c1,c2 from dual...中的c1，c2） •一个比较动作，例如c1>10 •一个WHERE子句的所有信息
MySQL源代码：关于MySQL的Item对象
http://www.orczhou.com/index.php/2012/11/more-about-mysql-item/
F
F
F
f
f
对于sql2005，执行计划的常量扫描运算符的values列显示，为了获取查询结果，访问编号4，5，6的分区
对于sql2008，打开聚集索引查找运算符的属性后，可以发现区别：
对于between查询，range列PtnId1000存在两个不同的Range表达式，一个名为start，一个名为end
F
分区和并行查询
只有具有多个可用的处理器（多个CPU,内核，超线程或这些任意组合）sqlserver才考虑并行查询
如果是低开销的查询运算符，并行查询得不偿失，对于估计开销小于5的运算符，优化器不会考虑并行查询
如果并行处理能够让计划的开销降到 5 以下，则有可能看到估计开销小于5的并行查询计划
每个parallelism运算符会创建一个线程边界，每个输入流和输出流都得分盘一个单独的工作线程，在查询执行期间，活动的工作线程数总是受到并行度的限制
同一并行查询计划可以在不同的并行度下执行，sql在启动查询时才决定要使用的并行度，具体结果受到当时可用资源的影响
要监视特定查询执行实例的实际并行度，可以使用sqlserver profiler的performance事件类别中的degree of parallelism事件
F
对分区表执行并行查询时，sql2005和sql2008之间的一个重要区别
实际的执行计划比估计的执行计划，在单个运算符和联接的属性内部，会包含一些关于执行过程的额外信息
8个线程，另外5个线程是空闲的，那么就浪费了5个线程
F
stream aggregate运算符，这里也可以将其称为partial aggregation（部分聚合）
部分聚合—》全聚合
sql2008里面八个线程都会处理数据，不会存在空闲线程
F
在8核计算机上处理访问3个分区的查询就会有其他5个线程空闲，如果要处理更多分区那么只有等待，如果处理10个分区，
那么8核计算机的sql2008更有优势
如果只访问一个分区，那么他会生成跟访问一个未分区表一样的查询计划
这时候，sql2005里八个线程都会用到
sql2008 当为分区分配线程时，他实现了一种轮询策略，很好地解决了上述分区表的并行查询问题，这样，同一个查询计划既能用于一个分区，也能用于多个分区，
而且在启动查询时，可以进一步调整线程分配
表分区函数使用range来表达
分区类型 循环赛RoundRobin
F
第十二章 图、树、层次结构和递归查询
F
Microsoft SQL Server 2008技术内幕：T-SQL查询 笔记
F
F
F
F
F
F
