SQL Server helps you to store data so that it can be retrieved easily. SQL Server is form of Relational Database Management System (RDBMS) i.e. data is stored as combination of columns and rows (tables) that are related to each other in a database. It provides various services such as integrating different database, reporting services, security of data. One of the important features is security that provides access to user based on their roles. One form that security can be enabled is by using views.
There are three uses of views in SQL Server:
- When we want to restrict access of columns to some users
- It helps simplify query execution when the users need to access data from multiple tables frequently that can be performed by using joins
- When there is a large data in tables, multiple views can be created, each view having manageable data and the database can be managed by multiple users who will handle that amount of data which makes the process of handling database efficient
A view is basically a virtual table that provides access to subset of columns from one or more tables. It is a query stored as an object in database that does not have its own data. It derives data from one or more tables called as underlying table. There is view known as syscomments view which is system defined view that stores entries for each view and other objects. This view contains a text column that stores the original SQL definition statement.
There are some restrictions at the time of modifying data through views:
- You cannot modify table if the modification affects more than one table at a time
- You cannot change a column that is result of a calculation such as a computed column or an aggregate function
Let’s take an example of a view:
To create a view:
Create View vwJoin
As
Select e.Employeeid,e.managerid,eph.Rate
From HumanResources.employee e Join HumanResources.EmployeePayHistory eph
On e.Employeeid=eph.Employeeid
To alter a view:
Alter View view_name
As
[Sql query]
To rename a view:
sp_rename old_viewname,new_viewname
To drop a view:
Drop View viewname
Generally, all the database administrators provide restrictions on data so that all the columns that contain sensitive data are not exposed to the users that are not authorized to view them. In short view forms an important part of SQL Server and is widely accepted form of providing security of the tables.
For more on Sql or Outsourcing software visit NevonProjects.com
