SQL Server 索引的疑问
最近在SQL Server 2008 R2上调优从SQL Server 2000升级上来的系统
所以经常接触索引,对索引优化也有一点认识和疑问,提几个问题大家讨论一下

1.网上到处都是说索引字段中不能有null值或者说不能在索引字段中使用is null 或者 is not null
在CSDN上还找到一个帖子说is null 可以is not null不可以,还给出了测试代码和测试数据

这个讨论应该不是空穴来风的,因为在我的系统上也曾经出现过一些索引中使用null的怪现象
我的一个表建立了一个非聚集索引(多字段的),我的一个查询使用到 is null 查询该索引的某个字段
(当然条件是复杂的了)结果非常慢,效率很低,后来没办法什么办法都试了,最后试到一个办法就是使用
SET ANSI_NULLS OFF选项,吧null的不可比较关闭,然后用=号查找效率就上来了,但最近升级到2008
后在联机文档中看到SET ANSI_NULLS OFF标志将在以后版本不在支持了,我现在想知道的确切答案是
到底什么情况下索引中字段与null的之间的比较不会降低效率,怎么提高这种索引字段中含有null的
查询的查询效率吗(难道非得使用默认值不能使用null吗),请大家给出样板数据和执行计划说明,实操一下
不然人云亦云理所当然,我在SQL Server 2008 R2 上测试是不管我怎么写查询条件
 is null ,is not null, not in (),<> 等等都可以使用索引

2.还有一个就是全表扫描的问题,我今天特意想试一下怎么写查询条件能够避免全表扫描,结果试了一些
网上说会出现全表扫描的写法,结果我在执行计划中还是不能看到全表扫描,很奇怪啊,一个很明显会全表
扫描的例子 select * from table where a like '%1%' (a没有索引,table记录数在1万条左右)
结果在执行计划中也没看到全表扫描的计划,而是使用了聚集索引查找,我的table有聚集索引,但不是
在a字段上.后来不管我怎么写,还是没办法遇上全表扫描的计划,神啊,呵呵,想他出现的时候却不出现
不想他出现的时候却到来.有谁能给出一个样板例子(最好代码创建样板数据),在 SQL Server 2008 R2
上使用索引和不能使用索引的例子呢?


PS:网上很所优化的文章今天我试都是没效果的,或者说根本和文章说讲得不一样,很可能是因为现在
查询优化已经很智能了,以前很多不能优化的查询现在都可以优化了,举一个这次我优化中遇到的一个
问题说大家讨论一下,一个where如果有多个条件用and连接,那么这几个条件的先后顺序是有讲究的
例如把计算复杂度很高的写在前面(前还是后这个就要看SQL Server是怎么分析SQL产生执行计划了,
有人说是从后面往前分析,所以应该把简单的条件写在后面),那么就会大大增加运算时间,不知道SQL
的and 连接是不是断路的(例如.NET里面的andalso),因为第一个条件不成立我就没必要去计算第二个
条件了.我就曾经优化过一个if ... and ...这样的条件语句,把简单的判断写在前面,结果执行时间
大大降低,效率提高几倍.但奇怪的是我试了一下这样的写法:select * from tableA where a=1 
and exists(select * from tableB where tableB.b=tableA.a)
这个查询不管我怎么调换两个条件的顺序,查询时间都是差不多的,优化器是怎么做到的啊!!!
难道优化器能够优化到分析每一个and的复杂度然后先执行复杂度低的???
------解决方案--------------------
这个是实验代码，从4中，我们能够看到v(key)列中有null，说明了索引中也包含了null值。

最后的查询，通过 is null，发现也是走的索引查找。

--1.建表

if OBJECT_ID('t1') is not null

   drop table t1

go



create table t1

(

id int primary key,

v varchar(20)

)





insert into t1

select 1 ,'aa' union all

select 2 ,'bb' union all

select 3 ,'cc' union all

select 4 ,'dd' union all

select 5 ,'ee' union all

select 6 ,'ff' union all

select 7 ,null union all

select 8 ,'gg' union all

select 9 ,null

go





--2.创建一个非聚集索引

create index idx_t1_v on t1(v)

go





--3.index_id = 2 为idx_t1_v索引

select *

from sys.indexes

where object_id = object_id('t1')





--hobt_id = 72057594041466880

select *

from sys.partitions

where index_id = 2

      and object_id = object_id('t1')





--