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)

Name
Job Mathew
Rock Feller
Harri Robins
Joe Philip
Nadia Alex

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)

Name
Harri Robins
Joe Philip

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).

Primary links

T-SQL » Views » Creating a View

Examples

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server