SQL Server How to create a view

To create a view, we should keep the following points in mind.

1. We can use SELECT query to create a view.
2. We can create a view using JOIN operation in SELECT query.
3. We cannot use any action query to create a view.
4. We cannot use ORDER BY clause with SELECT query unless TOP argument is used with the query.

Example to create a view:

A view is always based on table. So, to create a view, we require table. For the example, we first of all create a table and will then create a view out of that table.

CREATE TABLE Employees
(
	Id int ,
	first_name varchar(10),
	last_name varchar(10),
	salary int,
	gender varchar(1),
	grade varchar(10),
	dob date
) 
GO

We create a view out of Employee table.

CREATE VIEW vwEmployees
AS
SELECT Id, first_name, last_name, gender
FROM Employees
GO

Note that salary, grade and date of birth are sensitive data and are not included in view.

Error during creating view

The ORDER BY clause is invalid in views, inline functions, derived tables, sub queries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. You get error when you attempt to create view using ORDER BY clause as given below.

CREATE VIEW vwEmployees
AS
SELECT Id, first_name, last_name, gender
FROM Employees
ORDER BY gender
GO

The following script will run successfully as it includes TOP argument with SELECT clause.

CREATE VIEW vwEmployees
AS
SELECT Top 5 Id, first_name, last_name, gender
FROM Employees
ORDER BY gender
GO