SQL Server Views

Summary: in this tutorial, you will learn about views and how to manage views such as creating a new view, removing a view, and updating data of the underlying tables through a view.

When you use the SELECT statement to query data from one or more tables, you get a result set.

For example, the following statement returns the product name, brand, and list price of all products from the products and brands tables:

SELECT
    product_name, 
    brand_name, 
    list_price
FROM
    production.products p
INNER JOIN production.brands b 
        ON b.brand_id = p.brand_id;
Code language: SQL (Structured Query Language) (sql)

Next time, if you want to get the same result set, you can save this query into a text file, open it, and execute it again.

SQL Server provides a better way to save this query in the database catalog through a view.

A view is a named query stored in the database catalog that allows you to refer to it later.

So the query above can be stored as a view using the CREATE VIEW statement as follows:

CREATE VIEW sales.product_info
AS
SELECT
    product_name, 
    brand_name, 
    list_price
FROM
    production.products p
INNER JOIN production.brands b 
        ON b.brand_id = p.brand_id;
Code language: SQL (Structured Query Language) (sql)

Later, you can reference to the view in the SELECT statement like a table as follows:

SELECT * FROM sales.product_info;
Code language: SQL (Structured Query Language) (sql)

When receiving this query, SQL Server executes the following query:

SELECT 
    *
FROM (
    SELECT
        product_name, 
        brand_name, 
        list_price
    FROM
        production.products p
    INNER JOIN production.brands b 
        ON b.brand_id = p.brand_id;
);
Code language: SQL (Structured Query Language) (sql)

By definition, views do not store data except for indexed views.

A view may consist of columns from multiple tables using joins or just a subset of columns of a single table. This makes views useful for abstracting or hiding complex queries.

The following picture illustrates a view that includes columns from multiple tables: