January 30, 2007 at 10:41 pm
Hi
A question regarding Views. If a views is created which is made up of several unions from several tables; and when a select is placed on the view with a filter on a particular field, will the view first run the entire underlying query and then perform a filter, or otherwise. Please explain. Thanks a lot
Gogula
January 31, 2007 at 2:41 am
It depends.
There's no black or white in general, and the question is very general.
You can read up on views in BOL (Books on Line) and also on the subject of 'partitioned views'.
There's a lot of info in there to get you started.
/Kenneth
January 31, 2007 at 2:52 am
hello
You would have to define more precise how the views could look like.
Let's assume:
CREATE VIEW V1
AS
BEGIN
SELECT Col1, Col2, Col3 FROM Table A
ORDER BY {...}
UNION ALL
SELECT Col4, Col5, Col6 FROM Table B
WHERE {filtering here}
ORDER BY {...}
END
How it works:
1)selecting all information from table A
2) selecting filtered information from table B
3) construct a dataset (union all)
Well - query optimizer maybe first run the query from table B than A, I do not know, but result will be displayed correctly
I assume that UNION ALL statement will be parsed here, no syntaxt error occurs.
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
January 31, 2007 at 2:59 am
Hi,
Thanks for the replies and pardon me for not being more specific, I was looking at something like this:
CREATE VIEW V1
AS
BEGIN
SELECT Col1, Col2, ColX FROM Table A
UNION ALL
SELECT Col4, Col5, ColX FROM Table B
END
then we run a select as follows:
SELECT * FROM V1 WHERE ColX = 13
I need to know if the view would first select all the records based on the view defn. and then perform a filter on it (on ColX) or otherwise..
Thanks again..
Gogula
January 31, 2007 at 3:18 am
Hello
I think filtering will be performed first on each SELECT ... and then result is concatenated
Kindest Regards,
Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET
January 31, 2007 at 7:24 pm
To take advantage of partitioning there are several requisites. Look at partitioned Views in BOL. In particular the primary key must define a check constraint so that the engine can do exactly what you are asking for.
Good Luck,
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply