How To Create View in SQL Server

SQL views are results of stored queries in a database. They are very useful to create ad hoc tables for reporting and querying, without modifying any of the existing tables. In this article, we will look at how to create view in SQL server.

How To Create View in SQL Server

We will create view in SQL server using CREATE VIEW statement. Here is the syntax for CREATE VIEW statement.

CREATE VIEW [OR ALTER] schema_name.view_name [(column_list)]
 AS
     select_statement;

In the above statement you need to specify schema name which contains the view, view name, and also a select statement whose result you want to store as the view.

If you don’t specify schema name, the view will be created in present active schema.

Also read : How to Add Default Value in SQL Server

If you don’t specify the column names, then all columns present in the select statement will be used as-is.

If the view already exists, you will see an error. So you can optionally use OR ALTER clause in CREATE VIEW statement to alter the existing view.

If you want to modify an existing view such as add/remove columns, you need to use OR ALTER after CREATE keyword.

Once the view is created, you can query it like a normal table.

Also read : How to Drop View in SQL Server

Here is an example to create view in SQL Server. Let us say you have the following table sales(id int, order_date date, amount int).

# create table sales(id int, order_date date, amount int);

# insert into sales(id, order_date, amount)
       values(1,'2020-12-01',200),
       (2,'2020-12-02',100),
       (3,'2020-12-03',300);

# select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-12-01 |    200 |
|    2 | 2020-12-02 |    100 |
|    3 | 2020-12-03 |    300 |
+------+------------+--------+

Here is the SQL query to create sales_view from sales table.

# create view sales_view
       as select * from sales;

# select * from sales_view;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-12-01 |    200 |
|    2 | 2020-12-02 |    100 |
|    3 | 2020-12-03 |    300 |
+------+------------+--------+

Also read : How to Drop Index in SQL Server

Let us say you want to modify your view to drop the id column. Here is the SQL query to alter view in SQL server.

# alter view sales_view
         as select order_date,amount from sales;

# select * from sales_view;
+------------+--------+
| order_date | amount |
+------------+--------+
| 2020-12-01 |    200 |
| 2020-12-02 |    100 |
| 2020-12-03 |    300 |
+------------+--------+

As you can see it is very easy to create view in SQL server.