View is a virtual table. It
doesn’t store the data and does not physically exist in SQL Server. When we
calling the view it will fetch the data from the table and display it. View
can be made over one or more database tables. Generally we put those columns in
view that we need to retrieve/query again and again. Once you have created the
view, you can query view like as table. We can make index, trigger on view.
You are thinking that why we are
using the view. It does not have the data and it does not exist physical. When
we call the view it will fetch the data from table and display then why we need
it. The answer is for security purpose.
Let’s see the example. Suppose I have a table which is store the
information of the employee. This table is having the information of the
employee like name, address, salary, joining date, date of birth, skill set and
so on. This table is used by several department. Each department use their
relevant information (data) like HR deals with salary ,BMS team required about
the Access his does not interest to know the salary of the Employee but if he
want he can know because there is no way to protect the column. We can’t give the access on the column. We can give the
access on the table label. In this case we are creating the view and giving
the access of the view to the respective person.
Use of a View
Views are used for security
purposes because they provide encapsulation of the name of the table. Data
is in the virtual table, not stored permanently. Views display
only selected data.
Syntax of creating view
See the example: I am using AdventureWorksDW2008R2
database.
create view
vHr_Emp
AS
SELECT [FirstName],[LastName],[MiddleName],[Title],[HireDate]
,[BirthDate],[EmailAddress],[Phone],[MaritalStatus]
,[EmergencyContactName],[EmergencyContactPhone],[Gender]
,[BaseRate],[DepartmentName],[StartDate],[EndDate],[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
|
View created successfully.
For seeing the data we use like table
See for HR I am proving the limited
access. Similar we can do for others department.
There are two type of View
Ø System
view
Ø User
define view
User Define View
A view which is created by developer known as User Define
View.
There are two type of User define view
Ø
Simple view (Stander view)
Ø
Partitioned view(complex view)
o
Local partitioned view
o
Distributed partitioned view
Simple view
A
Simple View is a user defined view. A Simple View takes the data from a
single table and has no function. The user defined view is created by the
user as needed. See the below example
create view
vHr_Emp
AS
SELECT [FirstName],[LastName],[MiddleName],[Title],[HireDate]
,[BirthDate],[EmailAddress],[Phone],[MaritalStatus]
,[EmergencyContactName],[EmergencyContactPhone],[Gender]
,[BaseRate],[DepartmentName],[StartDate],[EndDate],[Status]
FROM [AdventureWorksDW2008R2].[dbo].[DimEmployee]
|
In simple view we can insert,
update, and delete data. We can only
insert data in simple view if we have primary key and all not null fields in
the view.
Virtually it will update the table
See the view result
Now we see the value of the table
Complex View
A Complex View is created for
multiple tables and contains functions and group data.
Local
partitioned view
We are using multiple tables in
the same database. It is called the local portioned view. Example
Create view
v_local_exp
as
SELECT F.[ProductKey],P.EnglishProductName,P.EnglishDescription,F.[CustomerKey],F.[CurrencyKey]
,F.[OrderQuantity],F.[UnitPrice],F.[ExtendedAmount],F.[ProductStandardCost],
F.[TotalProductCost],F.[SalesAmount],F.[TaxAmt],F.[Freight]
FROM
[AdventureWorksDW2008R2].[dbo].[FactInternetSales] F
Inner join [AdventureWorksDW2008R2].[dbo].[DimProduct]
p
ON P.ProductKey=F.ProductKey
|
There are two tables are used in
this view
Distributed partitioned view
If we are using the difference
server to creating the view that view known as distributed view.
We can only update data in
complex view. We can't insert
data in complex view.
In view we can’t use order
by clause
We can use Order by clause with help of TOP clause.
See the example
It is completed successfully.
System view
System views are use to retraining the information about the
system and database
Alter the view
If we want to change the view we need to alter the view.
Syntax
ALTER VIEW
View_name
AS
SELECT Statement
Where condition
|
Drop view
If we want to delete the view or drop the view from the
database we need to used DROP
DROP View View_name
|
