What is a View?
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.View can be created using tables of same database or different database. It is used to implements the security mechanism in the Sql Server.
Create table Emp_Details(EmpId int, EmpName nvarchar(200),
EmpLogin nvarchar(20), EmpPassword nvarchar(20) , EmploymentDate datetime )
And for example table has following data of employees
EmpId EmpName EmpLogin Emppassword EmploymentDate
1 EmployeeA EmpA EmpAPwd 29/01/2006
2 EmployeeB EmpB EmpBPwd 06/02/2007
3 EmployeeC EmpC EmpCPwd 14/05/2007
4 EmployeeD EmpD Empd 30/03/2008
5 EmployeeE EmpE EmpEPwd 30/06/2007
6 EmployeeF EmpF EmpFPwd 12/09/2012
Now suppose that the Administrator do not want that the users access the whole data of Emp_Details table which contains 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 give the access permission for the table to the user.
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.
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)
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 following query
Select * from View_EmployeeInfo where empid=3
It would gives the following result
EmpId EmpName EmploymentDate
3 EmployeeC 14/05/2007
We can also use Sql Joins in the Select statement in deriving the data for the view.
Create table EmpProjInfo (EmpId int, Projectname nvarchar(200))
and it contains the following data
Now we can create a view Vw_EmployeeProj which gives the information about the Employees and their projects
Create view Vw_EmployeeProj As
Select Emp_Details.EmpId, Emp_Details.EmpName,
EmpProjInfo.Projectname from EmployeeInfo inner join
EmpProjInfo on Emp_Details.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 Emp_Details.EmpId, Emp_Details.EmpName,
EmpProjInfo.Projectname from Emp_Details inner join
EmpProjInfo on Emp_Details.EmpId=EmpProjInfo.EmpId where Emp_Details.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
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
Thanks for approving my first article..
I appreciate that your willingness to write some sought of nice article or resource to bring in front of 'viewers'.But at the same time i am sorry to say that that your article should n't be like textbook text(all known things). when you are writing an article you have to remember that you are bringing some thing innovative or rediscover or highlighting a point some thing which most of the majority people did n't know about that.In other words your article is more in practicality as a reader i want to see that and i need that .Its my advice on my side please take it as a sportive one.I hope that your next articles meets the required criteria.
Thanks and Regards
SRI RAMA PHANI BHUSHAN.KAMBAMPATI
This Article is helpful for us who are working with Microsoft SQL Server
nice article.... Keep it up...
The definition of View is mentioned below:-
A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.
What is the SQL produce view Statement?
A SQL view could be a virtual table and therefore the SQL CRERATE view statement is that the SQL command that adds a brand new view to a SQL information.
A view are often accessed exploitation the SQL choose statement sort of a table. A view is made by choosing information from one or a lot of tables.
Some views also can support the SQL INSERT, SQL UPDATE and SQL DELETE statements. therein case, the view should check with one table and embody all NOT NULL columns of that table.
Why Use the SQL produce view Statement?
SQL views area unit used as a result of they'll offer the subsequent edges / functions:
information queries area unit simplified
information quality is hidden
Flexibility is inflated - queries of views might not modification once underlying tables chagne
Security is inflated - sensitive data are often excluded from a view
How To Use the SQL produce view Statement
The SQL produce view command is employed as follows.
SQL produce view Statement Syntax
Creates a virtual table whose contents (columns and rows) area unit outlined by a question. Use this statement to make a view of the information in one or a lot of tables within the information. as an example, a view are often used for the subsequent purposes:
To focus, simplify, and customise the perception every user has of the information.
As a security mechanism by permitting users to access information through the view, while not granting the users permissions to directly access the underlying base tables.
to supply a backward compatible interface to emulate a table whose schema has modified.
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH view_attribute [ ,...n ] ]
[ WITH CHECK OPTION ] [ ; ]
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }
A view is created solely within the current information. The produce view should be the primary statement in a very question batch. A view will have a most of one,024 columns.
When querying through a view, the information Engine checks to create positive that each one the information objects documented anyplace within the statement exist which they're valid within the context of the statement, which information modification statements don't violate any information integrity rules. A make certain fails returns a blunder message. A winning check interprets the action into Associate in Nursing action against the underlying table or tables.
If a view depends on a table or view that was born, the information Engine produces a blunder message once anyone tries to use the view. If a replacement table or view is made and therefore the table structure doesn't modification from the previous base table to exchange the one born, the view once more becomes usable. If the new table or view structure changes, the view should be born and create.