Difference Between View and Table in SQL

Difference between View and table in sql

A table contains data, a view is just a SELECT statement which has been saved in the database (more or less, depending on your database).

The advantage of a view is that it can join data from several tables thus creating a new view of it. Say you have a database with salaries and you need to do some complex statistical queries on it.

Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view

Key Differences Between Views and Created Tables Using SELECT INTO?

Let's start with aknowledging that by what you say about views, you are not talking about indexed views. Because these are actually implemented with existing tables in the background. So, we are talking about non-indexed views.

The two methods are very different - in fact, they have nothing in common. It is strange, because you both mention:

• "view = only saved sql query"
• "into = creating new table"

Isn't this a contradiction? The result of select into is actually a table, a view is not.

Not sure why are you asking about this or what are you trying to accomplish. In my experience, I use select into to fastly create logically temporary tables that have the same columns with the original without having to type all columns. This method of creating tables is generally inferior to the create table command and a subsequent insert, because no indexes and other stuff can be made - thus its use in adhoc queries or as a temporary entity.

What is difference between INDEX and VIEW in MySQL

VIEW

• View is a logical table. It is a physical object which stores data logically. View just refers to data that is tored in base tables.
• A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.

INDEX

• Indexes are pointres that maps to the physical address of data. So by using indexes data manipulation becomes faster.
• An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

ANALOGY:

Suppose in a shop, assume you have multiple racks. Categorizing each rack based on the items saved is like creating an index. So, you would know where exactly to look for to find a particular item. This is indexing.

In the same shop, you want to know multiple data, say, the Products, inventory, Sales data and stuff as a consolidated report, then it can be compared to a view.

Hope this analogy explains when you have to use a view and when you have to use an index!

When to use a View instead of a Table?

Oh there are many differences you will need to consider

Views for selection:

1. Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
2. Views can model complex joins easily.
3. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT function or many other things
4. You can easily manage your GRANTS directly on views, rather than the actual tables. It's easier to manage if you know a certain user may only access a view.
5. Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.

Views for insertion/updates:

1. You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view

Drawbacks

1. You lose information about relations (primary keys, foreign keys)
2. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you

What is the difference between Views and With ... as clause in Oracle?

A view and a with clause are certainly not the same thing.

A with clause generates an inline table (aka derived table) that exists only in the query within which it executes.

On the other hand, a view is a legitimate database object, that, in a sense, emulates a table. A view is defined by a sql query, so it is a virtual table, that can be queried just like any other database table. Under certain conditions, you can even run DML operations or views (update, delete, insert), that are applied to the underlying tables.

If you will repeativly need the same with clause, then a view is helpful to shorten your queries.

Difference between select from table directly and view

According to Microsoft there is a performance benifit if you use indexed views in sql server 2000/2005/2008.

Indexed views can increase query performance in the following ways

1. Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.

2. Tables can be prejoined and the resulting data set stored.

3. Combinations of joins or aggregations can be stored

But just like indexes on tables, indexes on views experience modification overhead. So only add an index to a view if the benefit of its speed increase when running exceeds the time it takes to update the view's index.

The below links give more information on this (on when to use what).

1. SQL Server 2000/2005 Indexed View Performance Tuning and Optimization Tips.
   
2. Improving Performance with SQL Server 2000 Indexed View.
   
3. See performance gains by using indexed views in SQL.

Difference Between Views and Tables in Performance

Performance is a lot more dependent on having the appropriate indexes than if you are using a view or direct table access, which (except for materialized views) behave exactly the same way.

---

Related Topics