What are SQL views? What are the advantages and disadvantages of using?

Question

From my research I learned that views in SQL, are like virtual tables results of stored searches of frequent access.

To W3S defines essentially as: virtual table.

It has to be said that views are stored searches.

Doubts:

1. What are views?

2. What advantages and disadvantages to use?

3. Assuming they are virtual tables, I would like to know if they are always loaded at the time the database server starts? And whenever any of the tables involved/related to this view undergo an update to view suffers equally?

4. If it’s just a query, what difference does it make with Storedprocedure?

Answer 1

View is a result originated from a predefined query. Essentially it is a metadata that maps a query to another, so it can be considered as a virtual table. As its name says, it represents a vision data and does not contain data. With it you have the illusion that you are seeing a table that does not exist. Of course what you see in this table exists otherwise in the bank. See more details on Wikipedia.

It may help to understand that they are also called named queries or stored queries. Not to confuse query (query) with result. She is the query same. It is basically a text of a SELECT. Think of her as a query that you store in a variable and instead of writing the whole query again whenever necessary use the variable. Of course this is a great simplification to facilitate understanding, the actual functioning is little more complex than this but it is not important here.

They are not loaded, they are generated whenever necessary.

Unless the view be it materialized, then you will have real tables representing these views. But their load will occur according to the need and optimizations of the database. Only in this type of view is that data is updated when actual tables are updated (answer here).

But it is important to note that even in a view not materialized there is the illusion that an update of the actual table involved reflects in the view, after all the view is generated at the time it is needed. These virtual tables are dynamic, they only exist at the time you need them. Only the query for your generation is predefined. If a data is updated soon after a view is accessed, this update will not be considered at this time, only the next time that the view is used.

It is good to understand that this "virtual table" that is created, is not quite a physical table created in memory with all the data you need. A view is only one way to translate a query to another query more complex. But an optimization can end up making a view common in physical table. Of course this depends on the implementation of DB.

One view is widely used to help give understanding of logical database design.

Example:

Here we see a view created to facilitate access to officials who are from Seattle who. This way it is possible to access the most important information of these employees in a consolidated form in a "virtual table" called SeattleOnly. So that they have all the advantages mentioned below.

CREATE VIEW dbo.SeattleOnly
AS
SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress bea 
    ON bea.BusinessEntityID = e.BusinessEntityID 
    INNER JOIN Person.Address a 
    ON a.AddressID = bea.AddressID
    INNER JOIN Person.StateProvince sp 
    ON sp.StateProvinceID = a.StateProvinceID
WHERE a.City = 'Seattle'

Taken from example using Microsoft’s Adventureworks database.

Example of use:

SELECT * FROM dbo.SeattleOnly WHERE LastName = 'Willians';

Note that it will return 4 columns. At the bottom this is the same as writing:

SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
    INNER JOIN Person.BusinessEntityAddress bea 
    ON bea.BusinessEntityID = e.BusinessEntityID 
    INNER JOIN Person.Address a 
    ON a.AddressID = bea.AddressID
    INNER JOIN Person.StateProvince sp 
    ON sp.StateProvinceID = a.StateProvinceID
WHERE a.City = 'Seattle' AND p.LastName = 'Willians'

I put in the Github for future reference.

Perks

• You can use this result in other queries decreasing the complexity, after all you will reference a virtual table mounted outside this query. In a certain way we can consider how syntax sugar for a query. You have a more limited view of the data without major worries.
• These consultations predefined are stored and you do not need to remember how to create them. Do not confuse with results.
• As the name itself helps identify them allow creating a more logical view for a human to understand modeling.
• Facilitates the change of the physical model without the danger of breaking darlings existing.
• Possibly the database can make some optimizations by already knowing the darlings used in views. But usually this depends even more on the statistics collected.
• You can put permissions on view, that is, you can prohibit access to the tables in their raw state, but in a certain condition the user can have access to the information as you defined in view. You better control what and how the user can access the information. It works as a firewall.
• Business rules may be adopted in views. The way you’re going to access the data is predefined. This is useful for formatting data, helping external tools and facilitating access via Apis. Think about computed columns. You can use them as a proxy and perform operations whenever data is accessed.
• Can facilitate access on legacy basis, making migrations and transitions painless.
• If the view is materialized may have gained performance for access to data already "consolidated".

Disadvantages

• Hides a complexity of query may mislead the developer as to the performance needed to access certain information. And it may be worse when views use other views. In some cases you may be making unnecessary queries without knowing very intensively.
• Create an extra layer. More objects to manage. Some people consider this an increase in complexity. Another way of looking at it is that a view can be misused.
• It can overstate what the user can access by preventing certain tasks.
• If the view is materialized will cause changes in the actual tables involved to be slower, after all they are more tables to update. This type of view works in a similar way to a trigger.

There are some technical details that can bring problems but I think it is not relevant to this question.

Difference to Stored Procedure

I think you’re beginning to get into another question. But summarizing one Stored Procedure allows more flexibility to create more complex algorithms but it does not create a virtual table. How will you handle this to expose the data and call a Sproc is a problem of her or her programmer query who will use it.

Good thing you didn’t ask the difference to use a temporary table, then it would be ample once and for all :)

Answer 2

I will give an example and make the considerations:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;

We have a table with price and amount and I want to know this "total value" I have seen many people telling me that putting the total value on the table is justifiable through the "denormalization", but see that it is not necessary and not recommended to put data in a table that you can get them through calculations, then to view can enter in this case so that the calculation is obtained without it existing in any table, because it is in a view that we can define as a predefined query to B.D. or a virtual table.

In this example already answered the item 1 and 2. Ref. to 3 is only a disadvantage if it is misused, and the advantage is already in the example.

And whenever any of the tables involved/related to this view undergo an update the view suffers equally?

• Yes because the view is a query to tables, if data changes the view result also changes, ref. to be loaded when the bank starts, I believe this may depend on the DBMS.

I see that the view is commonly used by DBA’s to delimit access, the DBA makes the rule of what particular program can view from your bank using the view and giving access to the program only in that part where it is allowed through the privileges, and the Stored Procedure are used more for when data needs to be handled more complex.