Database administrator might want to restrict access of data to different users. They might want some users to be able to access all the columns of a table whereas other users to be able to access only selected columns. The SQL Server allows you to create views to restrict user access to the data. Views also help in simplifying query execution when the query involves retrieving data from multiple tables by applying joins.
A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data. A view can derive its data from one or more tables, called the base tables or underlying tables. Depending on the volume of data, you can create a view with or without an index. As a database developer, it is important for you to learn to create and manage views.
Views ensure security of data by restricting access to:
Apart from restricting access, views can also be used to create and save queries based on multiple tables. To view data from multiple tables, you can create a query that includes various joins. If you need to frequently execute this query, you can create a view that executes this query. You can access data from this view every time you need to execute the query.
You can create a view by using the CREATE VIEW statement. The syntax of the CREATE VIEW statement is:
CREATE VIEW view_name
[ (column_name [, column_name]…)]
[WITH ENCRYPTION [, SCHEMABINDING] ]
AS select_statement [WITH CHECK OPTION]
Where,
For example, to provide access only to the employee ID, marital status, and department ID for all the employees you can create the following view:
CREATE VIEW HumanResources.vwEmployeeDepData
AS
SELECT e.EmployeeID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID
The preceding code crates the vwEmployeeDepData view containing selected columns from the Employee and EmployeeDepartmentHistory tables.
A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data. A view can derive its data from one or more tables, called the base tables or underlying tables. Depending on the volume of data, you can create a view with or without an index. As a database developer, it is important for you to learn to create and manage views.
Creating Views
A view is a database object that is used to view data from the tables in the database. A view has a structure similar to a table. It does not contain any data, but derives its data from the underlying tables.Views ensure security of data by restricting access to:
- Specific rows of a table
- Specific columns of a table
- Specific rows and columns of a table
- Rows fetched by using joins
- Statistical summary of data in a given table
- Subsets of another view or a subset of views and tables
Apart from restricting access, views can also be used to create and save queries based on multiple tables. To view data from multiple tables, you can create a query that includes various joins. If you need to frequently execute this query, you can create a view that executes this query. You can access data from this view every time you need to execute the query.
You can create a view by using the CREATE VIEW statement. The syntax of the CREATE VIEW statement is:
CREATE VIEW view_name
[ (column_name [, column_name]…)]
[WITH ENCRYPTION [, SCHEMABINDING] ]
AS select_statement [WITH CHECK OPTION]
Where,
- View_name specifies the name of the view.
- Column_name specifies the name of the column(s) to be used in a view.
- WITH ENCRYPTION specifies that the text of the view will be encrypted in the syscomments view.
- SCHEMABINDING binds the view to the schema of the underlying table or tables.
- AS specifies the action to be performed by the view.
- Select_statement specifies the SELECT statement that defines a view. The view may use the data contained in other views and tables.
- WITH CHECK OPTION forces the data modification statements to meet the criteria given in the SELECT statement defining the view. The data is visible through the view after the modifications have been made permanent.
Guidelines for creating views
While creating views, you should consider the following guidelines:- The name of a view must follow the rules for identifiers and must not be the same as that of the table on which it is based.
- A view can be created only if there is a SELECT permission on its base table.
- A view cannot derive its data from temporary tables.
- In a view, ORDER BY cannot be used in the SELECT statement.
For example, to provide access only to the employee ID, marital status, and department ID for all the employees you can create the following view:
CREATE VIEW HumanResources.vwEmployeeDepData
AS
SELECT e.EmployeeID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID
The preceding code crates the vwEmployeeDepData view containing selected columns from the Employee and EmployeeDepartmentHistory tables.