More Group Sites
Education Books
School Rankings
Jobless Net
Better Home
Enviro++
更好教育论坛
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Share
Options
View
Go to last post Go to first unread
hong  
#1 Posted : Sunday, September 17, 2017 12:45:49 PM(UTC)
hong

Rank: Administration

Reputation:

Groups: AcademicCoachingSchool, admin, Administration, BookSeller, CatholicSchool, CoachingAdult, CoachingProfessional, CoachingSports, ExtraCurriculumCoaching, IndependentSchool, Moderator, MusicTeacher, PrivateSchool, PublicSchool, SelectiveSchool, tutor
Joined: 11/23/2008(UTC)
Posts: 522

SQL relational database view vs table


A table contains schema and data / records (rows, columns) while a view is a stored / saved query (SELECT statement). A view doesn't actually hold data.

When to use view? See its advantages:

1. Join multiple tables or views into a single view. This is useful for repeated complex queries.
2. Aggregate data in a view (SELECT count, SUM and AVERAGET, etc.). This is useful for repeated complex queries.
3. After saving a complex query as a view, you can then just SELECT from the view without sending the big query to database all the time. You can think of a view as a "saved select statement" that you can repeat. Views can help mitigate the complexities that arise from an efficient, normalized model by allowing you to abstract that complexity.
4. Views can be used as security mechanisms by letting users access data without granting the users permissions to directly access the underlying base tables of the views, ie. GRANTS directly on views, rather than the actual tables. Show only a subset of the data. It can be a decomposition data access, eg. sales2020 and sales2019.
5. Tables are the concrete model and views provide abstraction over tables. You can add or remove columns in a view without modifying your underlying schema. The output remains the same but the underlying SELECT could change significantly. With the abstraction layer to clients or downstream systems, any change in schema is not exposed and hence the clients don't get affected. So views can help you with backwards compatibility.
6. Increase performance (Sql Server Indexed Views)

Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution.


Drawbacks
1. Information about relations (primary keys, foreign keys) is lost.
2. Because the view hides its underlying joins, it's not obvious whether you will be able to insert/update a view.

ORDER BY clause in the view definition / CREATE VIEW command will be refused (like no defined ordering in CREATE TABLE statement) because the sets of rows are not ordered by default. However, you can sort the data via a query on the view, in the same way as any other tables.

Edited by user Sunday, September 17, 2017 1:29:41 PM(UTC)  | Reason: Not specified

Sponsor
Rss Feed  Atom Feed
Users browsing this topic
Guest
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.