T-SQL » Views » Creating a View

Syntax:
CREATE VIEW view_name [ ( column1, ... ) ]
[ WITH ENCRYPTION ]
AS
    select_statement
[ WITH CHECK OPTION ]
view_name
The name of the view to be created. It should follow the rules for identifiers.
column1, ...
The name to be used for a column in a view. This is necessary only when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns have the same name (caused by a join), or when a column in a view is given a name different from that of the column from which it is derived.
WITH ENCRYPTION
The optional keyword that encrypts the syscomments entries that contain the text of the CREATE VIEW statement.
AS
The keyword that preceds the select_statement parameter.
select_statement
The SELECT statement used to define the view.
WITH CHECK OPTION
Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data remains visible through the view after the modification has been committed.

To create a view, the user must have the appropriate permissions on any tables or views referenced within its definition.

Examples

Code:
CREATE VIEW vwStudentDetails
AS
SELECT * FROM Students;


SELECT Name FROM vwStudentDetails;
Output:
The command(s) completed successfully.


Name
Job Mathew
Rock Feller
Harri Robins
Joe Philip
Nadia Alex

(5 row(s) affected)
Explanation:

The first segment of code creates the view, vwStudentDetails, which contains the Name field. The second code segment executes the view and displays the result set.

Language(s): MS SQL Server
Code:
CREATE VIEW vwStudentDetails_Filter
AS
SELECT Id, Name FROM Students
WHERE Std_Course=3;


SELECT Name FROM vwStudentDetails_Filter;
Output:
The command(s) completed successfully.


Name
Harri Robins
Joe Philip

(2 row(s) affected)
Explanation:

Views can also be used as filters to filter out only the data desired.

The following things are to be considered, while creating a view:

  • Views can be created in the current database only.
  • View names must adhere to the rules for naming identifiers.
  • A view can reference other views.
  • DEFAULT definitions, triggers, etc. cannot be associated with views, as it is used in the case of a table.
  • Keywords such as ORDER BY, COMPUTE, COMPUTE BY, or INTO cannot be used in the query that defines the view.
  • Indexes or full-text index definitions cannot be built on views.
  • Views are not created on temporary tables and vice-versa.
  • The name of every column in the view must be explicitly specified if:
    1. Any of the columns in the view is derived from an arithmetic expression, built-in function, or constant.
    2. Two or more of the columns in the view would otherwise have the same name (usually because the view definition includes a join and the columns from two or more different tables have the same name).

Language(s): MS SQL Server