Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow.



In SQL Server 2008 I have a view V over tables A and B that looks roughly like

create view V as
    select * from A
    union all
    select * from B


Reading from V causes a query to take intent shared locks on the base tables, but also takes an intent shared lock on the view object itself.

It is clear why we need the IS locks on the tables, and we can see that the IS lock on the view prevents concurrent modification to the tables underlying the view. That's fine.

The query plan contains no mention of the view. It's completely compiled out, and the resulting plan in this case is a simple concatenation of rows from the two base tables. Indeed the only mention of the view in the query plan XML is in the statement text.

If you add a second view U over the tables, reading from V does not cause any lock to be taken on U. This rules out that the engine just takes an IS lock on all views over A and B.

How does the database engine know to take a lock on the view?


Is the statement text parsed again?
Is there some other channel of information between the query planner and underlying execution to pass this information? If so, what?


If the latter, the details of the mechanism by which the storage engine knows to lock the view can fairly be considered internal. However the fact that it does this is user-visible, and I would expect it to be documented somewhere.

Or, to retrieve all rows, just press the blue data grid.



The tooltip "View data in the selected object" is almost making it too easy.
Try the button with the funnel next to it to retrieve only a selection (for big tables).
