Using Views in SQL Server
Views are nothing but saved SQL Statements, and are sometimes referred as “Virtual Tables”. Views cannot store data; rather they only refer to data present in tables. As the name implies, they are just used to view the contents of the tables by means of joins etc. They can be used to provide row- or column-level access to data, to wrap up complex joins, to perform complex aggregate queries, and to otherwise customize the display of data.
Creating Simple View
Let’s checkout the basic syntax for creating a view:
CREATE VIEW View_Name;
AS
SELECT Statement
GO
Remember a View can be created only in the current database and can contain at max 1024 columns. Now lets a look creating simple view on “Pubs” database. This is the example database installed when you install SQL Server (Except when you install client tools on Windows XP and then install MSDE)
Create View SimpleView
As
Select emp_id , fname , lname From employee
Now Open your Enterprise Manager, double click the database and then Views, You will see a view named “Simple View”. This is the view which we have created above.
You need to have a goal in mind when creating a view. There are a number of occasions where views are suitable.
- To hide the complexity of the underlying database schema or customize the data and schema for a set of users.
- As a Security Tool
Let’s take a look at each of these scenarios.
Hiding Complexity and Customizing Data:
Consider the join below
Create View JoinView
As
Select emp_id , fname , lname , job_desc From employee Inner Join jobs
On employee.job_id = jobs.job_id
Where employee.job_id = 5
This View selects data from two tables by means of an Inner Join. See the result by opening the view in Enterprise Manager.
Now writing such queries are almost impossible for all users. May be some of your users can manage it but majority have to learn SQL in order to view data. Here View comes in action. We just created Views using complex queries and allow our users to view that data. They even don’t think to know the complex structure of the database behind the scene or handling typical queries.
Now writing such queries are almost impossible for all users. May be some of your users can manage it but majority have to learn SQL in order to view data. Here View comes in action. We just created Views using complex queries and allow our users to view that data. They even don’t think to know the complex structure of the database behind the scene or handling typical queries.
View as a Security Tool:
In many cases you don’t want your users to view complete database data for some security reasons and also to restrict a group of users to view data specifically to their use. In this case you just publish Views where you display data only for user’s perspective. All the users have their own view of data in the forms of VIEWS. For e.g. Accounts Users can view data related to Accounts and Finance where as Managements have their own.
Modifying Data through Views:
Insertion in Views is same as Insertion in actual tables. Values Inserting in Views also reflects in the underlying table. But always consider constraints and relationships during insertions in Views specially when they contains more than one table.
Insert into simpleview (emp_id , fname , lname)
values (‘DBT39435M’,’exforsys’,’Member’)
Creating Views on Views:
Yes you can create Views on other Views. The syntax is almost same. Just you need to replace table names with Views names in you SELECT statement.
Dropping Views:
Dropping View is same as dropping any other object from the database.
Here I am first confirming whether the particular view (ViewOnView) is already present in the database. If then I will remove it using DROP keyword.
