A view is an alternative solution for observing data from one or more of the tables comprising a database. You can regard it as a virtual table or a stored query.
The recommended procedure is to implement SELECT instruction in the view to retrieve data for visualization, storing it both in a virtual table and in cache. Therefore, in some cases it is faster to have your query as a view, since an instruction would demand more work.
With views, it is also possible to implement users x data restrictions.
Using views presents certain advantages, such as improving your information’s access performance, saving time in development, simplifying user permissions’ management, etc.
Syntax for creating views:
Example:
Since the goal of a view is to simplify the developer’s job, in the example below we created a view that exhibits the fields of a certain table executing the selection command:
SELECT * FROM Sales V
JOIN Clients C ON V.Client_id = C.id_Client
JOIN Products P ON V.Product_id = P.id_Product
GO
The view previously created can be executed as follows:
SELECT * FROM vwSales
To view the configured view in E3, you must set up a query that executes the code above.
NOTE: views are used to (as the name says) view one or more tables, or one or more columns in a query. To update or insert tables and/or columns, you must create Procedures, which will be seen in the next article.
Related Articles
- Structure Query Language (SQL): Chapter 1 – Manipulating information in the DB.
- Structure Query Language (SQL): Chapter 2 – SQL Server Database’s Files and Logs.
- Structure Query Language (SQL): Chapter 3 – Data Discard and DB Limits.
- Structure Query Language (SQL): Chapter 4 – Backup.
- Structure Query Language (SQL): Chapter 5 – Best practices for setting up Historics and Queries.
- Structure Query Language (SQL): Chapter 6 – SQL Commands.
- Structure Query Language (SQL): Chapter 7 – Views.
- Structure Query Language (SQL): Chapter 8 – Triggers.
- Structure Query Language (SQL): Chapter 9 – Stored Procedures.