一句T-SQL语句引发的思考

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

**********************************************************

Author:黄山光明顶

mail:[e-mail]

version:1.0.0

date:2004-1-30

(如需转载,请注明出处!,如果有问题请发MAIL给我:-))

***********************************************************有一网友问:关于MSSQLSERVER索引优化问题:
 有表Stress_test(id int,keychar(2))
       id上有普通索引;
       key上有簇索引;
       id有有限量的重复;
       key有无限量的重复;

现在我需要按逻辑与查询表中key='Az'ANDkey='Bw'ANDkey='Cv'的id

求教高手最有效的查询语句

测试环境：
    Hardware:P42.6+512M+80G
    Software:windowsserver2003(EnterpriseEdition)+SQLserver2000+sp3a

 首先我们建立一个测试的数据，为使数据尽量的分布和随即，我们通过RAND()来随机产生2个随机数再组合成一个字符串，首先插入的数据是1,000,000条记录，然后在循环插入到58,000,000条记录。
  因为是随机产生的数据，所以如果你自己测试的数据集和我测试的会不一样，但对索引的优化和运行的效率是一样的。
  下面的“--//测试脚本”是产生测试数据的脚本，你可以根据需要修改@maxgroup，@maxLoop的值，比如测试1百万的记录可以:

    Select@maxgroup=1000
    Select@maxLoop=1000

如果要测试5千万：

    Select@maxgroup=5000
    Select@maxLoop=10000

所以如果你的SERVER或PC比较慢，请耐心等待.....，
 （在我的PC上运行的速度是插入1百万条的时间是1.14m，插入5千八百万条的时间是19.41m，重新建立INDEX的时间是34.36m)

 

作为一般的开发人员很容易就想到的语句：

  --语句1

   selecta.[id]from 
   (selectdistinct[id]fromstress_testwhere[key]='Az')a,
   (selectdistinct[id]fromstress_testwhere[key]='Bw')b,
   (selectdistinct[id]fromstress_testwhere[key]='Cv')c
   wherea.id=b.idanda.id=c.id

  --语句2

    select[id] 
     fromstress_test 
    where[key]='Az'or[key]='Bw'or[key]='Cv'
    groupbyidhaving(count(distinct[key])=3)  

  --语句5

   SELECTdistincta.[id]FROMstress_testASa,stress_testASb,stress_testASc
   WHEREa.[key]='Az'ANDb.[key]='Bw'ANDc.[key]='Cv'
     ANDa.[id]=b.[id]ANDa.[id]=c.[id]

但作为T-SQL的所谓“高手”可能会认为这种写法很“土”，也显得没有水平，所以会选择一些子查询和外连接的写法，按常理子查询的效率是比较高的：

  --语句3

   selectdistinct[id]fromstress_testAwhere
   notexists(
   select1from
    (select'Az'askunionallselect'Bw'unionallselect'Cv')B
   leftjoinstress_testCon C.id=A.idandB.[k]=C.[key]
   whereC.idisnull)

  --语句41
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>