September 21, 2009 at 8:34 am
Say I have two tables, both are the same.
Table1 and Table2.
I UNION both and add a column called "Origin" that says "Table1" for table 1 and "Table2" for table 2.
Now I make that into a view.
I then SELECT * FROM vw_Table1and2 WHERE Origin = "Table2"
That view is still going to have to select all the records and union them ALL, just to throw away all of Table1's results and return Table2, right...?
Or would SQL Server ever be "smart enough" to look inside the view and avoid doing that?
September 21, 2009 at 12:38 pm
Maxer (9/21/2009)
Say I have two tables, both are the same.Table1 and Table2.
I UNION both and add a column called "Origin" that says "Table1" for table 1 and "Table2" for table 2.
Now I make that into a view.
I then SELECT * FROM vw_Table1and2 WHERE Origin = "Table2"
That view is still going to have to select all the records and union them ALL, just to throw away all of Table1's results and return Table2, right...?
Or would SQL Server ever be "smart enough" to look inside the view and avoid doing that?
You'd have to test it to be sure, but I think SQL Server will have to run everything and then throw away the stuff that doesn't match. The optimizer isn't that smart, especially since those are derived values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply