When to Use a View Instead of a Table

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

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

What is a good reason to use SQL views?

Another use that none of the previous answers seem to have mentioned is easier deployment of table structure changes.

Say, you wish to retire a table (T_OLD) containing data for active users, and instead use a new table with similar data (named T_NEW) but one that has data for both active and inactive users, with one extra column active.

If your system(s) have gazillion queries that do SELECT whatever FROM T_OLD WHERE whatever, you have two choices for the roll-out:

1) Cold Turkey - Change the DB, and at the same time, change, test and release numerous pieces of code which contained said query. VERY hard to do (or even coordinate), very risky. Bad.

2) Gradual - change the DB by creating the T_NEW table, dropping the T_OLD table and instead creating a VIEW called T_OLD that mimics the T_OLD table 100% (e.g the view query is SELECT all_fields_except_active FROM T_NEW WHERE active=1).

That would allow you to avoid releasing ANY code that currently selects from T_OLD, and do the changes to migrate code from T_OLD to T_NEW at leisure.

This is a simple example, there are others a lot more involved.

P.S. On the other hand, you probably should have had a stored procedure API instead of direct queries from T_OLD, but that's not always the case.

View vs Table in SQL Server

To some extent, this is a matter of opinion. But, you have defined the following situation:

• Dashboard is only updated once per day, so it should be constant during the day.
• Underlying tables are being used for transactional purposes.
• Required query is at least somewhat complex.

Combined, these strongly argue for creating a dashboard table in the middle of the night, to simplify the querying.

In many environments, the underlying transactional tables are not used directly for reporting. Instead the tables are copied to a reporting environment (either periodically or in realish-time) and the reporting environment is used for reporting.

MySQL Views - When to use & when not to

This mysql-forum-thread about indexing views gives a lot of insight into what mysql views actually are.

Some key points:

• A view is really nothing more than a stored select statement
• The data of a view is the data of tables referenced by the View.
• creating an index on a view will not work as of the current version
• If merge algorithm is used, then indexes of underlying tables will be used.
• The underlying indices are not visible, however. DESCRIBE on a view will show no indexed columns.

Why do you create a View in a database?

A view provides several benefits.

1. Views can hide complexity

If you have a query that requires joining several tables, or has complex logic or calculations, you can code all that logic into a view, then select from the view just like you would a table.

2. Views can be used as a security mechanism

A view can select certain columns and/or rows from a table (or tables), and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

3. Views can simplify supporting legacy code

If you need to refactor a table that would break a lot of code, you can replace the table with a view of the same name. The view provides the exact same schema as the original table, while the actual schema has changed. This keeps the legacy code that references the table from breaking, allowing you to change the legacy code at your leisure.

These are just some of the many examples of how views can be useful.

In SQL Server, when should I use an indexed view instead of a real table?

You may want to use a view to simplify on queries. In our projects, the consensus is on using views for interfaces, and especially "report interfaces".

Imagine you've got a client table, and the manager wants a report every morning with the client's name, and their account balance (or whatever). If you code your report against the table, you're creating a strong link between your report and your table, making later changes difficult.

On the other hand if your report hits a view, you can twist the database freely; as long as the view is the same the report works, the manager is happy and you're free to experiment with the database. You want to separate client metadata from the main client table? go for it, and join the two tables in the view. You want to denormalize the cart info for the client? no problem, the view can adapt...

To be honest, it's my view as a programmer but other uses will certainly be found by db gurus :)

Using views instead of tables in stored procedures?

A view is just a macro that expands into an outer query.

If your view contains several joins, then when you join if to other views you suddenly have a 20 or 30 way JOIN when you actually see 3 JOINs in the SQL of the stored procedure. You'll also find that each query is different: why keep joining the same 20 or 30 tables for every query?

Generally, there is no benefit unless the view is indexed/materialised and the optimiser can use it.

Ideas such as having calculations on a single table masked by a view should be in a computed column: why keep calculating it? For a calculation on multiple tables in a view, it should be indexed.

Using a stored procedure already means no base table access (ownership chaining).

There are good uses of views to avoid direct table access by users, or to mask schema changes, or provide some basic security (eg based on SUSER_SNAME), but not for performance or idealogy

Is a view faster than a simple query?

Yes, views can have a clustered index assigned and, when they do, they'll store temporary results that can speed up resulting queries.

Microsoft's own documentation makes it very clear that Views can improve performance.

First, most views that people create are simple views and do not use this feature, and are therefore no different to querying the base tables directly. Simple views are expanded in place and so do not directly contribute to performance improvements - that much is true. However, indexed views can dramatically improve performance.

Let me go directly to the documentation:

After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

Second, these indexed views can work even when they are not directly referenced by another query as the optimizer will use them in place of a table reference when appropriate.

Again, the documentation:

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

This documentation, as well as charts demonstrating performance improvements, can be found here.

Update 2: the answer has been criticized on the basis that it is the "index" that provides the performance advantage, not the "View." However, this is easily refuted.

Let us say that we are a software company in a small country; I'll use Lithuania as an example. We sell software worldwide and keep our records in a SQL Server database. We're very successful and so, in a few years, we have 1,000,000+ records. However, we often need to report sales for tax purposes and we find that we've only sold 100 copies of our software in our home country. By creating an indexed view of just the Lithuanian records, we get to keep the records we need in an indexed cache as described in the MS documentation. When we run our reports for Lithuanian sales in 2008, our query will search through an index with a depth of just 7 (Log2(100) with some unused leaves). If we were to do the same without the VIEW and just relying on an index into the table, we'd have to traverse an index tree with a search depth of 21!

Clearly, the View itself would provide us with a performance advantage (3x) over the simple use of the index alone. I've tried to use a real-world example but you'll note that a simple list of Lithuanian sales would give us an even greater advantage.

Note that I'm just using a straight b-tree for my example. While I'm fairly certain that SQL Server uses some variant of a b-tree, I don't know the details. Nonetheless, the point holds.

Update 3: The question has come up about whether an Indexed View just uses an index placed on the underlying table. That is, to paraphrase: "an indexed view is just the equivalent of a standard index and it offers nothing new or unique to a view." If this was true, of course, then the above analysis would be incorrect! Let me provide a quote from the Microsoft documentation that demonstrate why I think this criticism is not valid or true:

Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes.

Together with the above quote regarding the persistence of data in physical storage and other information in the documentation about how indices are created on Views, I think it is safe to say that an Indexed View is not just a cached SQL Select that happens to use an index defined on the main table. Thus, I continue to stand by this answer.

What are the advantages of using a view over a temporary table in SQL Server

To discern, ask yourself if you need to reuse the information:

• a view is a glorified SELECT and it's used mainly for convenience
• you can materizalize a view, i.e. store it as a table and even index it. See this question
• use a temp table if you will not reuse the structure many times, like in a script that runs every now and then
• views will take space (especially if materialized) and having many views is difficult to maintain

Also note how temp tables are destroyed:

• if you create a temp table #tbl, it will be destroyed when it goes out of scope (e.g. at the end of the script).
• you can however create a temp table like ##tbl (with two #) and it will be destroyed when the connection ends.

---

Related Topics