Wednesday, 29 June 2016

View in sql server

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


2 comments:

If you have any doubt, please let me know.

Popular Posts