大量数据中多列中如何快速搜索相应数据

[7761098]

现在有一个table，格式大概是
create table #TEMP
(
ID varchar(max),
UserDefineField1 varchar(max),
UserDefineField2 varchar(max),
UserDefineField3 varchar(max),
UserDefineField4 varchar(max),
UserDefineField5 varchar(max),
UserDefineField6 varchar(max),
UserDefineField7 varchar(max),
UserDefineField8 varchar(max),
UserDefineField9 varchar(max),
UserDefineField0 varchar(max)
)
中间大概千万条数据
如何最快速搜索出含有“A"的record呢

select * from #temp
where UserDefineField1 = 'A'
OR UserDefineField2 = 'A'
OR UserDefineField3 = 'A'
OR UserDefineField4 = 'A'
OR UserDefineField5 = 'A'
OR UserDefineField6 = 'A'
OR UserDefineField7 = 'A'
OR UserDefineField8 = 'A'
OR UserDefineField9 = 'A'
OR UserDefineField10 = 'A'

这样运行的话效率太低，有没有更好的方法呢

对了，其实UserDefineField* 这些列大多数都是空值，但是又不知道怎么样才可以更好的filter一下

[sdhdy]

--try:
select * from #temp 
where   charindex('A',isnull(UserDefineField1,'')+  
        isnull(UserDefineField2,'')+  
        isnull(UserDefineField3,'')+  
        isnull(UserDefineField4,'')+ 
        isnull(UserDefineField5,'')+  
        isnull(UserDefineField6,'')+  
        isnull(UserDefineField7,'')+  
        isnull(UserDefineField8,'')+  
        isnull(UserDefineField9,'')+  
        isnull(UserDefineField10,''))>0

[olddown]

[Quote=引用 3 楼 sdhdy 的回复:]
SQL code--try:select*from#tempwherecharindex('A',isnull(UserDefineField1,'')+isnull(UserDefineField2,'')+isnull(UserDefineField3,'')+isnull(UserDefineField4,'')+isnull(UserDefineField5,'')+isnull(UserDefineField6,'')+isnull(UserDefineField7,'')+isnull(UserDefineField8,'')+isnull(UserDefineField9,'')+isnull(UserDefineField10,''))>0
[/Quote]
学习了，这种做法没见过，貌似遍历一遍就够了。

[olddown]

[Quote=引用楼主 7761098 的帖子:]
现在有一个table，格式大概是
create table #TEMP
(
ID varchar(max),
UserDefineField1 varchar(max),
UserDefineField2 varchar(max),
UserDefineField3 varchar(max),
UserDefineField4 varchar(max),
UserDefineField5 varchar(max),
UserDefineField6 varchar(max),
UserDefineField7 varchar(max),
UserDefineField8 varchar(max),
UserDefineField9 varchar(max),
UserDefineField0 varchar(max)
)
中间大…
[/Quote]
为什么用那么大的size？

[7761098]

其实 or 与 in的效率有差别吗

[jia_guijun]

select * from #temp where UserDefineField1  = 'A'
union
select * from #temp where UserDefineField2  = 'A'
union
select * from #temp where UserDefineField3  = 'A'
union
select * from #temp where UserDefineField4  = 'A'
union
select * from #temp where UserDefineField5  = 'A'
union
select * from #temp where UserDefineField6  = 'A'
union
select * from #temp where UserDefineField7  = 'A'
union
select * from #temp where UserDefineField8  = 'A'
union
select * from #temp where UserDefineField9  = 'A'
union
select * from #temp where UserDefineField10 = 'A' 

[7761098]

其实主要是因为有text，remarks数据，当年遗留问题，多谢sdhdy。