We have a small app that currently uses the xml field for data collection. For reporting purposes the xml has been flattened out into a view, so that the report writer can query the view as normal instead of writing the xml explosion in the data source.
Here is the question. Say for instance you have 10000 records, does the view explode all of the xml then apply the wear clause from the select statement, or does it just explode the xml for the rows matching the where statement?
Note the field used in the where statement is not in the xml but a normal field in the storage table.
ex table:
Id, firstname, lastname, xmlstoragefield
Query's where clause hits the "ID" field"
Thanks,
(note I have tested this for 10000 records (small dataset I now) and it seems to have the same performance as when it had 20 records). Any insight into the arcane of the optimizer would be greatly appreciated.