SQL Server CREATE VIEW

Summary: in this tutorial, you will learn how to use the SQL Server CREATE VIEW statement to create new views.

To create a new view in SQL Server, you use the CREATE VIEW statement as shown below:

CREATE VIEW [OR ALTER] schema_name.view_name [(column_list)]
AS
    select_statement;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the view after the CREATE VIEW keywords. The schema_name is the name of the schema to which the view belongs.
• Second, specify a SELECT statement (select_statement) that defines the view after the AS keyword. The SELECT statement can refer to one or more tables.

If you don’t explicitly specify a list of columns for the view, SQL Server will use the column list derived from the SELECT statement.

In case you want to redefine the view e.g., adding more columns to it or removing some columns from it, you can use the OR ALTER keywords after the CREATE VIEW keywords.

SQL Server CREATE VIEW examples

We will use the orders, order_items, and products tables from the sample database for the demonstration.

Creating a simple view example

The following statement creates a view named daily_sales based on the orders, order_items, and products tables:

CREATE VIEW sales.daily_sales
AS
SELECT
    year(order_date) AS y,
    month(order_date) AS m,
    day(order_date) AS d,
    p.product_id,
    product_name,
    quantity * i.list_price AS sales
FROM
    sales.orders AS o
INNER JOIN sales.order_items AS i
    ON o.order_id = i.order_id
INNER JOIN production.products AS p
    ON p.product_id = i.product_id;
Code language: SQL (Structured Query Language) (sql)

Once the daily_sales view is created, you can query data against the underlying tables using a simple SELECT statement:

SELECT 
    * 
FROM 
    sales.daily_sales
ORDER BY
    y, m, d, product_name;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

Redefining the view example

To add the customer name column to the sales.daily_sales view, you use the CREATE VIEW OR ALTER as follows:

CREATE OR ALTER sales.daily_sales (
    year,
    month,
    day,
    customer_name,
    product_id,
    product_name
    sales
)
AS
SELECT
    year(order_date),
    month(order_date),
    day(order_date),
    concat(
        first_name,
        ' ',
        last_name
    ),
    p.product_id,
    product_name,
    quantity * i.list_price
FROM
    sales.orders AS o
    INNER JOIN
        sales.order_items AS i
    ON o.order_id = i.order_id
    INNER JOIN
        production.products AS p
    ON p.product_id = i.product_id
    INNER JOIN sales.customers AS c
    ON c.customer_id = o.customer_id;
Code language: SQL (Structured Query Language) (sql)

In this example, we specified the column list for the view explicitly.

The following statement queries data against the sales.daily_sales view:

SELECT 
    * 
FROM 
    sales.daily_sales
ORDER BY 
    y, 
    m, 
    d, 
    customer_name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Creating a view using aggregate functions example

The following statement creates a view named staff_salesthose summaries the sales by staffs and years using the SUM() aggregate function:

CREATE VIEW sales.staff_sales (
        first_name, 
        last_name,
        year, 
        amount
)
AS 
    SELECT 
        first_name,
        last_name,
        YEAR(order_date),
        SUM(list_price * quantity) amount
    FROM
        sales.order_items i
    INNER JOIN sales.orders o
        ON i.order_id = o.order_id
    INNER JOIN sales.staffs s
        ON s.staff_id = o.staff_id
    GROUP BY 
        first_name, 
        last_name, 
        YEAR(order_date);
Code language: SQL (Structured Query Language) (sql)

The following statement returns the contents of the view:

SELECT  
    * 
FROM 
    sales.staff_sales
ORDER BY 
	first_name,
	last_name,
	year;Code language: SQL (Structured Query Language) (sql)

The output is:

In this tutorial, you have learned how to create a new view by using the SQL Server CREATE VIEW statement.