2

I am looking at a legacy system that stores XML blobs

A view is created over this so the data is more accessible (for reporting) 

CREATE view [dbo].[Data]
as
select top 100 percent
data.value('(/Input/@FirstName)[1]','varchar(100)') as FirstName,
(select data.query('for $i in /Input/Control[@Name="Postcode"]/* order by $i/@UpdateDateTime descending return $i')).value('(/Value/@ValueCode)[1]','varchar(50)') as 'Postcode'
...
...
from data

Will SQL server generate statistics for queries run against this view?

Improve this question
3
  • I don't know but I do know that that top 100 percent is absolutely useless - why is it there?
    – Aaron Bertrand
    Commented Nov 10, 2015 at 23:00
  • Very good question. I am not sure. This is not the way I would have written it. (And I cannot re-write ~ read-only access)
    – Hayden Wiltshire
    Commented Nov 11, 2015 at 0:41
  • TOP 100 Percent ORDER BY Considered Harmful.
    – Tom V
    Commented Nov 11, 2015 at 7:35

1 Answer 1

Reset to default
1

No, the query optimizer will not create statistics for a view unless it's an indexed view, you can create an XML index on the xml column itself to speed up the view,

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.