I have two queries as shown below which do the same thing. Here
xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column.
I checked the execution plan of both these queries and find that the first query has a lower I/O cost and subtree cost than second one. I was expecting that the first one would have a higher cost as it uses
charindex, but that is not the case.
Why does it have a lower cost?
SELECT * FROM mytable WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )
SELECT * FROM mytable t1 WHERE t1.[xmlcolumn].exist('//*/text()[contains(.,"abc")]')=1