原文地址http://blog.csdn.net/JavaProgramers/archive/2008/01/18/2051935.aspx
大型系统的生产环境，一般情况下，我们评价一条查询是否有效率，更多的是关注逻辑IO(至于为什么，回头补一篇)。我们常说，“要建彪悍的索引”、“要写高效的SQL”，其实最终目的就是在相同结果集情况下，尽可能减少逻辑IO。
1.1 where条件的列上都得有统计信息。
没统计信息SQLServer就无法估算不同查询计划开销优劣，而只能采用最稳妥的Scan（不管是table scan还是clustered index scan）。一般情况下我们不会犯这种错误——where条件里不使用非索引列是个常识。索引上的统计信息是无法删除的。
1.2 尽量不使用不等于（!=）或者NOT逻辑运算符。
这条规则被广为传颂，原因据联机文档和百敬同学的书讲，也是SQLServer无法评估不同查询计划开销的优劣。但是SqlServer2k5聪明了很多，试验发现尽管用了!=或者not，查询还是会被优化。如下：
create table tb1
(
col1 int identity(1,1) primary key,
col2 int not null,
col3 varchar(64) not null
)
create index ix_tb1_col2 on tb1
(
col2
)
create index ix_tb1_col3 on tb1
(
col3
)
declare @f int
set @f = 0
while @f < 9999
begin
insert into tb1 (col2, col3) values(1, 'ssdd')
set @f = @f + 1
end
insert into tb1 (col2, col3) values(0, 'aadddd')
insert into tb1 (col2, col3) values(2, 'bbddd')
insert into tb1 (col2, col3) values(3, 'bbaaddddddaa')
通过上述代码，各位可以看到数据分布。col2值为1的有9999条；col2值为0、2、3的分别有1条。
按照本条规则，!= 和NOT带来的应该是个scan操作，但实际情况是：
SQL2k5很聪明，它依据统计信息分析得出来，应该采用index seek而不是index scan。（稍微解释解释index seek和index scan：索引是一颗B树，index seek是查找从B树的根节点开始，一级一级找到目标行。index scan则是从左到右，把整个B树遍历一遍。假设唯一的目标行位于索引树（假设是非聚集索引，树深度2，叶节点占用k页物理存储）最右的叶节点上（如上例）。index seek引起的IO是4，而index scan引起的IO是K，性能差别巨大。关于索引，可以仔细读读联机文档关于物理数据库体系结构部分）。
1.3 查询条件中不要包含运算
这些运算包括字符串连接（如：select * from Users where UserName + ‘pig’ = ‘张三pig’），通配符在前面的Like运算（如：select * from tb1 where col4 like ‘%aa’），使用其他用户自定义函数、系统内置函数、标量函数等等（如：select * from UserLog where datepart(dd, LogTime) = 3）。
SQLServer在处理以上语句时，一样没办法估算开销。最终结果当然是clustered index scan或者table scan了。
1.4 查询条件中不要包含同一张表内不同列之间的运算
所谓的“运算”包括加减乘除或通过一些function（如：select * from tb where col1 – col2 = 1997），也包括比较运算（如：select * from tb where col1 > col2）。这种情况下，SQLServer一样没办法估算开销。不论col1、col2上都有索引还是创建了col1、col2上的覆盖索引还是创建了col1 include col2的索引。
但是这种查询有解决办法，可以在表上多创建一个计算字段，其值设置为你的“运算”结果，再在该字段上创建一个索引，就Ok了。
To Be Continue…
(II)中将介绍统计信息值分布不均匀对查询的影响和如何避免这些影响，捎带更多的说说返回多行结果时，为啥SQLServer有时会选择index seek，而有时会选择index scan。
(III)中主要介绍传说中的“Foldable”和“NonFoldable”表达式。并举例说说所谓的“Nonfoldable"表达式某些情况下也不是那么可怕。
(IV)中则主要说说在程序中执行SQL。如：安全性，拼SQL、参数化SQL和存储过程之间对DB来说有什么区别，参数化SQL的一些技巧。捎带着，会大概介绍介绍SQLServer的Buffer Pool
上回我们说到评估一条语句执行效率主要看逻辑IO（啥是逻辑IO，啥是物理IO见联机文档），这次我们继续。
我们先说说，返回多行结果时，为什么SQLServer有时会选择index seek，有时会选择index scan。
以nonclustered index为例说明。
像所有的索引B树一样，非聚集索引树也包括完全由索引数据组成的根节点和中间级节点；但是和聚集索引树不同的是，聚集索引树叶节点包含的是基础表的数据页（我们常说，表的物理存储顺序和聚集索引相同，就是这个原因）,非聚集索引树叶节点是索引页。SQLServer通过非聚集索引查找数据时，会通过这个非聚集索引键值去搜索聚集索引，进而检索基础表数据行。
假设有这样一张表，非聚集索引树深度为2，一层根节点（1个索引页），一层叶节点（4个索引页）。聚集索引树深度为3，一层根节点（1个索引页），一层中间级节点（2个索引页），一层叶节点（250页，也就是基础表物理存储页）表的数据假设1w行。注：所有数据均为假设，只为说明原理。
我们首先，再强调一遍，SQLServer获取数据，总是以页为单位，就算是只读取一行也会获取整张页（见《写有效率的SQL查询（I）》）
现在有一条简单查询(如：select * from tb where col2 = 99，col2是tb表中的非聚集索引)，假设会返回100行。
Ok，我们来分析如果以Index seek来查找这100行会有多少IO。index seek每次都从索引树根节点开始查找，找到中间级节点（99对应的索引行），然后从该节点行开始连续遍历所有col2为99的索引行。在遍历这些行时，每拿到一条，都会通过该条索引行中聚集索引键值去聚集索引树中index seek，然后从数据页中获取数据。在最坏的情况下，col2为99对应的索引行跨越了全部4个叶级非聚集索引页（当然，这没啥可能性，举例而已，切勿深究）；每次通过聚集索引树进行index seek，IO开销最坏情况下是一个根节点，一个中间级节点，一个数据页,一共要seek100次，开销300个逻辑IO。综上，通过nonclustered index seek总共开销是305个IO。
要知道，我们的基础表数据页一共才250页，这说明了啥？说明就算是我从头到尾扫描一遍表也比noncustered index seek快。这时，SQL2k5会产生一个完完全全的clustered index scan执行计划来搞定表扫描。
好了，现在我们再来分析select * from tb1 where col2 = 1。假设它的结果集为5行。如果这时还是进行nonclustered index seek的话，逻辑IO按照上面相似的分析，应该是19个IO，远远要小于整个的clustered index scan。这时，SQLServer自然会采用nonclustered index seek。
我们再来看聚集索引。聚集索引和非聚集索引最大的不同在于聚集索引的存储顺序就是基础表的物理存储顺序。还是上面的表tb，假设聚集索引建在了col1上.如果where条件是col1 = XX的话，自然是index seek，因为IO最小，撑死了只有3（一个聚集索引根节点页，一个聚集索引中间级节点页，一个数据页）；如果where条件是col1 > XX的话，不管行集是多大，SQLServer总是首先通过index seek拿到XX对应的数据页，然后挨梆往后遍历基础表数据页到尾巴就OK了。最坏情况XX恰好比表中最小的col1小，那就读取所有行。如果where条件是col1 < XX，那就倒着检索聚集索引，无他。
OK，到这里，我们明白了为啥SQLServer会选择index seek和index scan。也顺便明白了通过非聚集索引查询时，结果集相对总行数多寡对查询计划选择的巨大影响。
（结果集/总行数）被称为选择性，比值越大，选择性就越高。
你得到了它，本文的重点就是选择性。
统计信息，说白了，就是表中某个字段取某个值时有多少行结果集。统计信息可以说是一种选择性的度量，SQLServer就是根据它来估算不同查询计划的优劣。
后面将通过一个实际的例子来说明统计信息对查询计划的影响。
以下是示例表的表结构：
各位可以注意到，该表上有一个identity字段charge_no，聚集索引就创建在它上面。有两个非聚集索引indx_category_no，indx_provider_no，我们重点关注indx_provider_no。现在来看看provider_no字段的统计信息（有点长，我前边粘一部分，后边粘一部分）：
（上述各字段含义，见联机文档对DBCC SHOW_STATISTICS的描述）
从上面的贴图可以看到，表中总行数为1w，采样行数为1w。provider_no值为21的只有1行，而值为500的行则有4824行。下面两张图是两条SQL的查询计划，我就不多嘴解释了。
那么问题来了：
我们知道，SQLServer会缓存查询计划，假如有这么一个存储过程：
create proc myproc
(
@pno int
)
as
select * from charge where provider_no = @pno
第一次我们传进来一个21，OK，它会缓存该存储过程的执行计划为nonclustered index seek那个。后来我们又传进来一个500，完蛋了，服务器发现它有一个myproc的缓存，so，又通过nonclustered index seek执行，接着你的同伙看到你的查询花费了巨量的IO，于是，你被鄙视了。
这说明了啥？说明如果你的查询选择性变动剧烈，你应该告诉SQLServer不要缓存查询计划，每次都应该重新评估、编译。实现方法很简单，查询的尾巴上加一个option（recompile）好了。而且SQL2k5还有一个nb的feature，可以每次只重新编译存储过程的一部分（当然，你也可以选择重新编译整个存储过程，这取决于你的需求。详见联机文档。）
=======彪悍的分割线================================
后面blog会提到索引优化。其实百敬同学那本《SQL性能调校》这方面讲的不少了。那本书唯一的缺憾就是某些规则在SQL2k5中不适合。我想我会尽力都写出来。
先说说这些误区。所谓“误区”，有一些是新手很容易犯的错误或者很容易忽略的问题，另外一些，则是像“耗子吃了盐会变成蝙蝠”一样，让我们从小就认为是正确的事情。如下：
1、 表上不管用得着用不着，都加个聚集索引。
我们知道，表以两种方式组织物理存储：有聚集索引的“聚集表”；没有聚集索引的“堆”。在聚集表中，数据行按照聚集索引的顺序存储（这也是为啥一张表最多只能有一个聚集索引的原因）；堆中，数据行的存储可以认为是不确定的。
在偶《写有效率的SQL查询（II）》中曾经介绍过DB引擎如何在聚集表中通过非聚集索引查找目标数据：从非聚集索引树根开始seek，查找到目标索引行，然后通过索引行上存储的聚集索引键值，爬聚集索引树，并最终通过聚集索引行上的指针拿到目标数据。
但是堆上的非聚集索引存储的不是聚集索引键值，它存储的是指向目标行的指针。也就是说，如果在同样的表是堆，通过非聚集索引seek数据将省掉爬聚集索引树的损耗，而可以直接通过非聚集索引行上的行指针直接拿到目标数据。也就是说，在某些情况下，使用堆可以提高系统效率。
这个“某些情况”，就是你的需求，你的系统行为。一般情况下，所有人对要在什么样的字段上创建聚集索引都非常了解；但是不是所有的人都对应该在什么样的系统行为下，不创建聚集索引了解。假设你的表中有字段col1, col2,col3,col4等等，col1、col2的分布密度很低。你观察了系统行为，发现一半的查询是XXXX where col1 = YYYY，另一半的查询是XXXX where col2 = YYYY。这种情况下，使用堆就是更好的选择。
2、 primary key就是聚集索引。
primary key上是得有索引，但是这个索引可不见得一定得是聚集索引。尽管语句
create table testPK
(
id int identity(1,1) primary key,
fname varchar(64)
)
会在id列上创建聚集索引。当然，一般主键都是聚集索引，但也仅仅是“一般”而已。个人感觉，聚集索引的唯一目标就是数据检索，它应该建在什么字段上，完全由系统行为决定。“一般主键都是聚集索引”也仅仅是因为多数情况下，primary key字段上建所有更有益于效率而已。
create table testPK
(
id int identity(1,1) primary key nonclustered,
fname varchar(64)
)
可以创建primary key为非聚集索引
3、 Log类的表，有事没事加个自增的Id列。
这事相信干过的人很多，哈，而且一般还会顺手在这个Id列上加上个primary key的约束，聚集索引也就被无意识的建上了。就像一个记录用户活动的日志表，一般会有这么几个典型字段：Id、LogTime、UserId。实际上对这种表的查询，大多集中在LogTime和UserId上，Id完全没有实际意义。你的客服系统查找的，可能仅仅是某个用户的操作记录（一般按时间排序的），或者你的报表系统要生成每天的用户操作统计。想想看，如果干脆砍了Id列，并直接把聚集索引创建在LogTime上多爽。
4、 是个表就给加个primary key约束
就像3中的例子，primary完全没必要。呵呵，这条看着简单是简单了，犯这错误的人，那也不比3少。
5、 在where条件里对同一个表中的列做运算或比较，以为创建某种类型的索引可以提高效率。（这种情况下，任何索引都无法提升性能。解决办法见偶前面的“写有效率的sql查询”）
见过了无数的这种写法。最常见的，如：一张用户表里有用户注册时间（t1,YYYYDD），有退订时间（t2,YYYYDD），现在让你获取存活时间大于3天的用户总数：很多人一不注意，就整一个select count(*) from Users where t1 – t2 > 3出来。而且常常会臆测在t1、t2上建个涵盖索引（或者分别在t1、t2上建索引）会让性能提升。
6、 在表上创建了col1、col2顺序的涵盖索引（聚集的或非聚集的），但是where条件里就一个col2 > XXX。这种情况下，就不如分别在col1、col2上创建索引。
以上的误区，都是在工作中常常犯或遇到的，没遇到的肯定还有，欢迎各位安达补充：）
OK，接下来我们说说“涵盖索引”和include索引。
所谓的涵盖索引，就是传统方式在多个列上创建的索引。“inlude索引”是SQL2k5提供的新功能，允许添加非键列到非聚集索引的叶节点上。
创建涵盖索引：
create index ix_tb_col1_col2 on tb
(
col1,
col2
)
创建include索引：
create index ix_tb_col1 on tb
(
col1
)include(col2, col3,col4)
涵盖索引和include索引的区别在于，涵盖索引的所有列都是键列，索引行的物理存储顺序就是col1、col2的顺序，这也是误区6之所以称为误区的原因。涵盖索引可以是聚集索引，也可以是非聚集索引。
include索引include的列并不影响索引行的物理存储顺序，它们作为一个挂件“挂在”索引行上。挂着这些挂件的作用在于，诸如select col2, col3, col4 from tb where col1 = XXX只需要seek一把非聚集索引ix_tb1_col1就OK了，拿到索引行就拿到了需要的所有数据。挂件们是要占用索引行空间的，我们知道，索引字段宽度要尽可能窄是选择索引的一项基本原则（这项原则背后的原理是尽可能让索引树深度小），所以并不是include的字段越多越好，这得跟你的系统行为有一个平衡。
从上面叙述可以看到，涵盖索引实际上是include索引的加强版。也就是说，你的where条件里除了涵盖索引的第一个索引列之外还有其他索引列的比较，创建涵盖索引要比include索引高效一点点。同样，维护涵盖索引的消耗也会多少高于Include索引。
聚集索引的索引行直接包含了数据行指针，也就是说，通过聚集索引行，可以直接拿到其他所有列的数据，从某种意义上说，聚集索引就是最大的include索引，这也是include索引只能是非聚集索引的原因所在。
OK，给你一条SQL语句：
select col1, col2, col3, col4 from tb where col5 > XXX and col6 > yyy
你既可以在上面创建涵盖索引col5、col6，又可以创建include索引（col5/col6）include(col1、col2、col3、col4)。选择如何创建，就要看你的表各字段宽度、系统行为了。在此不再赘述。
最后讲讲如何拿到在文中频频提到的系统行为统计信息。这东西说白了就是各种SQL的执行次数、逻辑IO、物理IO、执行消耗CPU时间等等等等。想想看，假如你拿了一份系统中所有SQL的文本、执行总次数、逻辑IO占用总IO比例、物理IO占用总IO比例、平均逻辑IO、平均物理IO等等等等，你八成能够指出系统瓶颈所在，老板和伙计们的眼光也会会极大的满足你小小的虚荣心，哈。这些东西就在动态视图sys.dm_exec_query_stats里面，自个翻翻联机文档吧：）
拿到系统行为统计信息之后，你终于调整了索引，于是系统明显nb了。如果你要看看它变得有多nb，可以关注动态视图sys.dm_db_index_usage_stats，这个也就不多说了。
最后，多读联机文档，多做尝试，尽力不用工具而手写SQL才是硬道理。
=====================
关于表上是不是都需要一个聚集索引,各位安达展开了剧烈讨论.摘录部分到这里:
from RicCC:
描述的确不足，是否选择聚集索引不是这么简单
1. heap表的查询，除了table scan和covering index之外，都需要bookmark lookup，covering index的使用是有限的，剩下的都是成本很高的操作。除非对这个表的查询很少。
2. heap的数据页之间没有link，顺序读取数据性能低，I/O开销大。除非每次都用unique index seek。
3. heap每个insert数据都是在末尾，并发的insert阻塞问题比较大。因为insert位置一次只能有一个任务加排它锁。可以用clustered改善。
4. delete多时，heap比clustered更浪费磁盘空间，碎片更严重，并且没有正常的方式消除heap数据页的碎片，只能建clustered或者drop table重建。
目前为止我基本没有发现充足的证据使用heap.
============
index seek跟unique index seek不一样，例如你要找8.1-8.9号的log，执行计划里面只会看到一个index seek，它seek的是第一条数据，从第一条数据到最后一条用的是scan，并且heap肯定要用到rid/index lookup，假如要取的是1.1-8.9，rid/index lookup的成本很可能导致sql server放弃index而使用table scan
综合考虑，使用heap的范围实在是太狭窄，clustered index怎样建倒很有文章，需要极为认真的对待.
============
index是unique的，index条件都给出来了并且全部是=，每次seek操作输出都只有一条记录，就是unique index seek，oracle是有这个操作的
如 果不是unique index seek，就一定会有range index scan。sql server heap表的range index scan需要在IAM跟数据页间切换，效率不好，clustered index就是用于改善这种状况，并且充分利用磁盘设备读取连续数据的优化措施
========================================================================
Me:到目前为止,我找到的最有理由使用堆的地方是一张每天产生kw级记录的日志表,这张表上的查询主要以查询指定Id的用户在某段时间内的记录.
写有效率的SQL查询（IV）
本文主要介绍写SQL的另外两个误区：
1、 存储过程中使用局部变量而不使用参数变量（就是存储过程输入参数）做where条件
2、 查询条件中类型不匹配
这两种错误都是非常非常容易犯且非常发指的错误，特别是2，太多次见过了。
一、关于存储过程使用局部变量，我们举