create view V1 as
select NID,VName,VNum from Tb1
union all
Select NID+'A' as NID,,VName,VNum from Tb2
go
其中Tb1和Tb2都是用NID(VARCHAR50)作为聚焦唯一索引的表,
执行
select * from V1 where NID='14010101'
后,执行时间需要5秒。
将Tb2的NID+'A"改为NID,则0秒执行。
这个视图要如何写才能让索引正常起作用?
...全文
992打赏收藏
字符串连接后索引被破坏
create view V1 as select NID,VName,VNum from Tb1 union all Select NID+'A' as NID,,VName,VNum from Tb2 go 其中Tb1和Tb2都是用NID(VARCHAR50)作为聚焦唯一索引的表, 执行 select * from V1 where NID='14010101' 后,执行时间需要5秒。 将Tb2的NID+'A"改为NID,则0秒执行。 这个视图要如何写才能让索引正常起作用?
declare @s varchar(50)='14010101';
select NID,VName,VNum from Tb1 where NID =@s
union all
Select NID+'A' as NID,,VName,VNum from Tb2 where NID =stuff(@s,len(@s),1,'') and right(@s,1)='A'