Sql Server View

Views:- View can be described as virtual table which derived its data from one or more than one table columns.It is stored in the database. It is used to implements the security mechanism in the Sql Server. For example, suppose there is table called Employeeinfo whose structure is given below:-
Create table EmployeeInfo(EmpId int, EmpName nvarchar(200),EmpLogin nvarchar(20), Emppassword nvarchar(20) , EmploymentDate datetime )
And it contains the following data
EmpId        EmpName         EmpLogin       Emppassword     EmploymentDate 
1                 Vivek Johari         Vivek                  VikJoh           29/01/2006
2                Virender Singh      Virender              Virender         06/02/2007
3                Raman Thakur       Raman                Raman          14/05/2007 
4                Uma Dutt Sharma   Uma                   Uma             30/03/2008
5                Ravi Kumar Thakur  Ravi                    Ravi            30/06/2007
Now suppose that the Administrator do not want that the users have excess to the table EmployeeInfo which contains the some critical information (Emplogin, EmpPassword etc) of the Employees. So he can create a view which gives the empid, empname, employmentdate as the output and give the permission for the view to the user. In this way the administrator do not need to bother about giving the access permission for the table to the user.
The syntax for creating a View is given below:-
Create View Viewname As
 Select Column1, Column2  From Tablename
 Where (Condition)
Group by (Grouping Condition) having (having Condition)
For Example,
  Create View View_Employeeinfo As 
        Select EmpId, EmpName, employmentdate 
          From EmployeeInfo
Now user can use the view View_EmployeeInfo as a table to get the empid , empname and employmentdate information of the employees by using the giving query
Select  *   from  View_EmployeeInfo  where empid=2
It would gives the following result
    EmpId        EmpName             EmploymentDate
       2              Virender Singh         06/02/2007
We can also use Sql Joins in the Select statement in deriving the data for the view
For Example, Suppose there is a table named EmpProjInfo which contains the information about the employee project. the structure of the EmpProjInfois given below:-
Create table EmpProjInfo (EmpId int, Projectname nvarchar(200))
and it contains the following data
EmpId          Projectname
1                   Abcbank
2                   AtoZfinancialsol
3                   learningsystem
4                   ebooksystem
5                  AtoZfinancialsol
Now we can create a view Vw_EmployeeProj which gives the information about the Employees and its projects
Create view Vw_EmployeeProj As
  Select EmployeeInfo.EmpId, EmployeeInfo.EmpName, EmpProjInfo.Projectname from EmployeeInfo 
   inner join EmpProjInfo on EmployeeInfo.EmpId=EmpProjInfo.EmpId
Altering an View
  If we want to alter the view, then we can use the Alter View command to alter the view
For Example,
Alter view Vw_EmployeeProj As
  Select EmployeeInfo.EmpId, EmployeeInfo.EmpName, EmpProjInfo.Projectname from EmployeeInfo inner join EmpProjInfo on EmployeeInfo.EmpId=EmpProjInfo.EmpId where EmployeeInfo.EmpId in (2,3,4)
Getting Information about the Views:-
   We can use the System Procedure Sp_Helptext to get the definition about the views
For example,  we can use the sp_helptext command to get the information about the view Vw_EmployeeProj
          sp_helptext Vw_EmployeeProj
Renaming the view:-
    We can use the sp_rename system procedure to rename a view. The syntax of the sp_rename command is given below:-
  SP_Rename ‘Old Name’, ‘New name’
 For example if we want to rename our view View_Employeeinfo to Vw_EmployeeInfo, we can write the sp_rename command as follows:-
sp_rename ‘View_Employeeinfo’, ‘Vw_EmployeeInfo’
Dropping  a View
We can use the Drop command to drop a view 
  For example, to drop the view  Vw_EmployeeInfo, we can use the following statement
Drop view Vw_EmployeeInfo
 
Summary :-
  This article tells us that view can be described as a virtual table which can derived its data from one or more than one table.We can create a view with the help of the Create View command,can alter its definition with the help of Alter view command, get its definition with the help of Sp_helptext command, rename a view with the Sp_rename command and drop a view the Drop view command.
            

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Most Imp SQL Concepts, SQL Server, SQL View and tagged , , . Bookmark the permalink.

31 Responses to Sql Server View

  1. mahesh says:

    Thanks for giving information . I have a small doubt
    If i want to create a view based on two tables how can i create views please tell me.

  2. Pingback: Mupen64 Plus - How to Get Free Games for Android

  3. Pingback: http://ballarini.com.pl/dziennik-gazeta-prawna-15-2013-1118.php

  4. Pingback: https://www.olx.pl/

  5. Pingback: social impact marketing

  6. Pingback: Homepage

  7. Pingback: افضل شركة نقل اثاث بالرياض

  8. Pingback: buy cbd oil

  9. Pingback: Fed Coin is Here Central Bank Cryptocurrencies

  10. Pingback: Empire Market

  11. Pingback: Crystal Meth

  12. Pingback: Dream Market

  13. Pingback: m88vina

  14. Pingback: w88asia

  15. Pingback: Apollon Market

  16. Pingback: Automobile Brakes Market Report by Growth Enablers, Geography, Restraints and Trends – Global Forecast To 2025

  17. Pingback: idr poker

  18. Pingback: indoqqpoker

  19. Pingback: The World in 2020 Economist Cover

  20. Pingback: www.parisqq1.site

  21. Pingback: santali gana 2013

  22. Pingback: lapakqq

  23. Pingback: Visit site

  24. Pingback: daftar warnetqq

  25. Pingback: Klik hier

  26. Pingback: https://www.outdoorpowerllc.net/collections/repair-parts-accessories/products/toro-60-timecutter-hd-deck-belt-133-4372

  27. Pingback: Southwood Tallahassee homes for sale

  28. Pingback: www.rajacapsaq.site

  29. Pingback: Togel OnLine

  30. Pingback: ชุดไทย ชาย

  31. Pingback: girlsfinishingthejob

Leave a Reply