Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would create a VIEW for several reasons</p> <p>A) A well constructed view does tend to perform faster than a query, though with query optimization you may not notice much of a difference.</p> <p>B) It keeps knowledge of the database structure within the database itself - adding a good layer of abstraction (as a side note, consider using a stored procedure rather than an inline query - this also keeps database knowledge within the database itself)</p> <p>C) If you do need to make a structural change to the database, you can keep the view consistent without needing to rebuild your code.</p> <p><strong>AMENDMENT</strong> I'm going to amend this answer in light of some of the comments so as to clarify some points ...</p> <p>It is absolutely true that a standard view does not provide any real performance gain over a query. A standard view is materialized at run time which essentially makes it no different than a convenient way to execute a query of the same structure. An index view, however, is materialized immediately and the results are persisted in physical storage. As with any design decision, the use of an indexed view should be carefully considered. There is no free lunch; the penalty you pay for use of indexed views comes in the form of additional storage requirements and overhead associated with maintaining the view when there are any changes to the underlying database. These are best used in instances of commonly used complex joining and aggregation of data from multiple tables and in cases in which data is accessed far more frequently than it is changed.</p> <p>I also concur with comments regarding structural changes - addition of new columns will not affect the view. If, however, data is moved, normalized, archived, etc it can be a good way to insulate such changes from the application. These situations are RARE and the same results can be attained through the use of stored procedures rather than a view.</p>
    singulars
    1. This table or related slice is empty.
    1. POUse SQL View or SQL Query?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USharriyott
    1. USJames Conigliaro
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POUse SQL View or SQL Query?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe down side is you have to deploy and version your view in sync with the client code.
      singulars
      1. PO
      1. USBrian Reiter
    2. COI'd hesitate to make this generic advice. A view is great for common queries, but it should never be the first answer. I've seen plenty of DB's with 300 views, and (needless) views on top of views because, "Views are quicker!" It's only true if you use views *well*. Just use good prudence when building views.
      singulars
      1. PO
      1. USEric
    3. CO*sigh* Views do NOT perform faster than queries. Views and queries resolve to the same thing.
      singulars
      1. PO
      1. USRBarryYoung
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload