SQL Server Performance Forum – Threads Archive

View Behaviour ?

Does anyone have a good link on how views actuall get executed and their behaviour? I have come across some strange behaviour.
Firstly from a sql profiler trace i traced the followign sql. Select * from View2 where A = ? and B = ?.
If i do the same select it takes 1 second, this sql was taking very long. I then found out that if i opened the view source t-sql and appended the where clause in it it took very long. How does this behaviour work , why does a direct select on the view with the where clause take 1 second but executing the view source with the where clause so long ? WHen you place a where clause on a view what actually happens ?
Why does the t-sql captured not seem to behave as captured but as though it literally executed the view source code with an appended where cluase?? Also i found that if your view includes a select top 1, where clauses dont work on the view ? It is almost as thought the same top 1 row is returned and that the where cluase does not matter. Any idea how this works , i know that the where cluase is valid , if i simply remove the top 1 from the view it works but with top 1 (even though the where cluse only returns 1 row) it does not seem to work, it seems as though the same first row is always returned and then the where clause is tested??? ANyway i am trying to understand how a where cluase on a view really works ? Thanx

Has anything changed recently to the underlying table(s)? If so, have a look at sp_refreshview —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hi Frank No nothing has changed.
The main issue is it seems as though it is not selecting from the view with a where clause even thought that is the sql captured by profiler, it is as though it is executing the t-sql source for the view wit an appended where clause somewhere ? Thanx

Hm, anyway can you get your hands on Inside SQL Server 2000? It’s full of information about view’s internals. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>