更新时间:2023-12-01 10:09:52
要在简单的语句中执行此操作而无需特殊过程,可以将每一行转换为XML,然后对XML使用XQuery在其中搜索任何值.匹配的行.例如:
To do it without a special procedure in a simple statement, you could convert each row to XML and then use an XQuery on the XML to search for any value in the row that matches. So for example:
declare @SearchValue as varchar(20)
set @SearchValue = 'MyValue'
select *
--,(select MyTable.* for XML PATH (''),TYPE) AllColumns
--,convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item')) FoundColumns
from MyTable
where convert(nvarchar(255),(select MyTable.* for XML PATH (''),TYPE).query('for $item in * where $item=sql:variable("@SearchValue") return $item'))<>''
专门为此任务设计的过程可能会更有效地执行此过程,并可以利用索引等优势.老实说,我不会在没有过多考虑的情况下将其放入生产数据库解决方案中,但是要综合考虑搜索工具,还不错.我在40秒内对700,000条记录表进行了搜索.但是,如果我按每列分别进行过滤,它将几乎立即运行.还有一些警告:
A procedure specifically designed for this task could probably do this more efficiently and could take advantage of indexes... etc. Honestly I would not put this into a production database solution without quite a bit of consideration, but as a throw together search tool it's not bad. I ran a search on a 700,000 record table in 40 seconds. However if I filter by each column individually it runs nearly instantly. Also a few more caveats: