Views in SQL

We can define a VIEW as a virtual table with its set of rows and columns, whose content is defined by a SELECT query on one or more tables. This means that the data they contain is not actually stored in the view, but is dynamically generated according to the query they execute.

VIEWS are often used as filters of the tables from which they collect data and also as security mechanisms, since they allow the user to have access to data visibility but not to the underlying real tables. They are also often used to simplify and personalize the display of the data for the user.

The SELECT defining the VIEW can also come from other views of the system.

There are different types of views in SQL Server:

1. User Views: user-defined virtual tables whose content is defined by a query.

2. Indexed Views: in indexed views, data has been stored as a real table since it has been indexed through a single clustered index. These types of views greatly improve performance on some types of queries that return many records.

3. Partitioned Views: they combine horizontal data with partitions from a set of member tables across one or more servers.

4. System Views: they expose catalog metadata.

In the next section we will focus on the exemplification of DDL statements for creating, updating and deleting user views:

CREATE VIEW

CREATE VIEW, is the DDL statement for creating views. Its syntax is as follows:

CREATE VIEW viewName AS SELECT …

Now let’s move on to creating our example view:

CREATE VIEW view_example
    SELECT [name],
           lastName],
           product,
           productDescription,
           price
    FROM Customers
    INNER JOIN Orders
    ON Customers.customerId = Orders.customerId
    WHERE price >= 450;

** Once created, we can display our database components and view them in the subdirectory: **Views**

To execute the new VIEW, execute a SELECT on it:

SELECT * FROM view_example;

As we can see, VIEW shows in a virtual table, the set of results returned by the SELECT on the Customers and Orders tables.

ALTER VIEW

ALTER VIEW, is the DDL statement for updating views. The syntax is as follows:

ALTER VIEW viewName AS SELECT …

Now let’s move on to updating our VIEW: view_example

ALTER VIEW view_example AS
    SELECT [name],
           [lastName],
           product,
           productDescription,
           price
    FROM Customers INNER JOIN Orders
    ON Customers.customerId = Orders.customerId
    WHERE price >= 450
    AND productDescription LIKE '%Console%';

If we execute the query, we would see that it only returns the first two previous records when adding the condition with the LIKE clause.

DROP VIEW

DROP VIEW, is the DDL statement for removing views. The syntax is as follows:

DROP VIEW viewName;

Now let’s move on to removing our VIEW: view_example.

DROP VIEW view_example;