A view is a virtual table that represents data from one or more than one database table. You can select data from single or multiple tables based on the sort and filter criteria (using WHERE and GROUP BY clauses) and save data as a view. You can also set permissions on views to control access to sensitive data. For example, a manager, an accountant, and a clerk of a company can all use the same database. The manager can access all data from different tables of the database, the accountant can access only some of the data from multiple tables, and the clerk can access only some of a single table's data. The easiest way to do this is with SQL views; you create three different views based on the user rights and let the user access these views based on their rights.
Access databases don't natively support SQL views. You'll need to use a "true" relational database to explore views, such as SQL Server and its copy of Northwind.
The CREATE VIEW SQL statement creates a view. The simple format of the CREATE VIEW statement is as follows:
CREATE VIEW viewname AS selectstatement
where viewname is the name of the view and selectstatement is the SELECT statement used to select data from one or more table.
The following CREATE VIEW statement creates myView with records as a result of SELECT myName FROM myTable:
CREATE VIEW myView AS SELECT myName FROM myTable
The following two CREATE VIEW statements create the View1 view and the View2 view from the Orders table based on different criteria:
CREATE VIEW "View1" AS SELECT OrderID, OrderDate, ShippedDate FROM Orders WHERE (Freight < 10) CREATE VIEW "View2" AS SELECT OrderID, OrderDate, ShippedDate FROM Orders WHERE (Freight > 1000)
You can also create views by selecting data from multiple tables. For example, the following view selects data from the Products and Categories tables. By using views this way, you can essentially "save" frequently used queries:
CREATE VIEW "Products by Category" AS SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE Products.Discontinued <> 1 ORDER BY Categories.CategoryName, Products.ProductName
You may have heard the term materialized view and wondered what it was. Basically, a materialized view is a view that's been stored (in many ways defeating the original goal of views as being virtual tables, but that's another issue). SQL Server calls its materialized views indexed views, and although you can create indexed views with any version of SQL Server, only the enterprise, developer, and evaluation editions use them. A SQL Server indexed view is essentially a view that has had a set of unique values "materialized" into the form of a clustered index, thereby providing a quick lookup in terms of pulling the information behind a view together.