日期:2014-05-18  浏览次数:20310 次

下面两个语句那个效率高,一个用charindex,一个用in
用charindex:
select   top   100   a.*,     b.telno,   b.mobileno,   b.addr,   b.postid,   ltrim(str(a.serverid))+ '| '+ltrim(a.orgid)+ '| '+ltrim(str(a.fundid,   19))   as   posstr   from     run..fundinfo     a,   run..custbaseinfo   b     where   0=0   and   a.custid   =   b.custid       and   charindex(a.orgid,   '0000,1201,1202,1203,1205,1235,1251,1252,1253,1255,1256,1257,1258,1259,1307,1308,1309 ')> 0     order   by   a.serverid,a.orgid,   a.fundid  

用in:
select   top   100   a.*,     b.telno,   b.mobileno,   b.addr,   b.postid,   ltrim(str(a.serverid))+ '| '+ltrim(a.orgid)+ '| '+ltrim(str(a.fundid,   19))   as   posstr   from     run..fundinfo     a,   run..custbaseinfo   b     where   0=0   and   a.custid   =   b.custid       and   a.orgid   in   ( '0000 ', '1201 ', '1202 ', '1203 ', '1205 ', '1235 ', '1251 ', '1252 ', '1253 ', '1255 ', '1256 ', '1257 ', '1258 ', '1259 ', '1307 ', '1308 ', '1309 ')     order   by   a.serverid,a.orgid,   a.fundid  

我测试了以后发现是charindex而同事在另外一个客户那里测试发现是in,到底哪个效率高啊

------解决方案--------------------
charindex不能使用索引