What is the difference b/w derived tables and views? which will give better performance in reporting.
Printable View
What is the difference b/w derived tables and views? which will give better performance in reporting.
Indeed, the fact that a view is a relvar is precisely one of the strengths of relational systems; it is important in just the same way as the fact that a subset is a set is important in mathematics. Note: SQL Products and the SQL Standard ... often seem to miss this point, however, inasmuch as they refer repeatedly to 'tables and views' with the tacit implication that a view is not a table."
In other words, from the perpective of an application or from the user, there is no difference between a view and a table. Now, we know that's not completely true, because it's easy to create a view that can't be updated because it joins multiple tables, and the query processor can't parse out what's being updated amongst the various tables in the view. That's a relatively easy problem to solve, however, with the INSTEAD OF trigger.
In my company we have an application that manages web sites. It uses a master database for all sites, and then a database for each site. In each database is a table that the application uses to display exhibitors at our trade shows. Some of our trade shows have multiple shows around the country, and for these the site database is actually a set of databases with a parent database for the main site and child databases for the individual shows in the group. In the child databases the table is replaced by a view, presenting data in the parent database. The application had a problem with an update to this object, so I wrote the INSTEAD OF trigger, and the application works as it was supposed to. I didn't have to create views in every database used by the application for this exception situation.
So, my point is that tables and views should follow the same naming standard - the difference between the two should be transparent to the
in other words views are logical table.