August 2, 2011 at 3:12 pm
How does SQL filter a view?
For example I have a VIEW which is made up from multiple tables with about 100000 rows of information for numerous different groups.
If I wanted to select a specific set of group information from the view i.e SELECT * FROM myView WHERE Group='group to get' would the VIEW create the "table" of 100000 rows and then select the rows requested from this OR would it use the WHERE clause as part of the VIEW construction?
My need would be for the same set of information to be selected for one group at any specific time - would it be better to do as above and filter the view OR create a stored procedure with the group as a parameter and call that when required?
Cheers in advance...
August 2, 2011 at 3:20 pm
A view is just a saved sql query. The results are not meterialized untill you call the view. If you use an indexed view then the results are materialized and kept up to date for every insert / update / delete statement.
The only problem with using views is that when you use views that use views that use views, then the optimizer has a much harder time figuring a good plan to retrieve the data.
If you can avoid that views can be a good tool.
In that case I would use a view and then put the filter only when I call it.
August 3, 2011 at 9:10 pm
A couple of comments:
1. Think of a view as a template of a query, with a lot of stuff pre-written for you (joins, columns, calculated columns). The optimizer takes that as a starting point and then adds your additional code so yes, the WHERE clause would apply to view. Queries against a view are simply queries run against the tables underlying the view, unless you specifically create an indexed view.
2. Inline table valued functions are basically parameterized views. They might just be what you're looking for.
3. Procedures let you do some things like splitting a complex query into segments, loading temporary tables, using dynamic sql, etc that you can't do in a single query. Views and inline table valued functions must be single queries.
Finally, is your view an old school "partition" that looks like this? If so, we need to talk about indexes to support your one-group-at-a-time query. Multi-table scans aren't pretty.
CREATE View etc etc
SELECT * from TableA
UNION ALL
SELECT * from TableB
UNION ALL
SELECT * from TableC
etc
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply