Tag Archives: Sql Update View

Views in Sql Server

Sql Server, Views, , , , , , , , , , , , , , , , , , , ,

Views are nothing but saved SQL statements, and are sometimes referred as Virtual Tables. Keep in mind that Views cannot store data rather they only refer to data present in tables.

Benefits of Views:

A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all the data). Views can do the following:

  • Restrict access to specific rows in a table
  • Restrict access to specific columns in a table
  • Join columns from multiple tables and present them as though they are part of a single table
  • Present aggregate information (such as the results of the COUNT function)

Syntax: Creating a view

CREATE VIEW <View_Name>
AS
<SELECT Statement>

Let us create a Sample DataBase with Tables to understand Views Concepts:

By using the below script we are creating a Sample DataBase Named:ViewDemo. Then in this database we are creating two tables Customers and Orders  and in these tables populating the sample data.

Create DataBase SqlHintsViewDemo
GO
USE SqlHintsViewDemo
GO
Create Table dbo.Customers
( CustomerID int Identity(1,1),
  FirstName Varchar(50), LastName VarChar(50),
 Phone varchar(50), City Varchar(50) )
GO
Create Table Orders
(
OrderId int Identity(1,1),
CustomerId int
)
GO
Insert INTO dbo.Customers
Values ('Kalpana', 'Biradar', '2727272727', 'Bangalore'),
	('Basavaraj','Biradar','1616161616','Mysore')
GO
INSERT INTO Orders
Values(1), (2), (2), (2), (2)

Create Simple View:

The below script creates a view named vwSample:

CREATE VIEW dbo.vwSample
As
 SELECT CustomerID, FirstName, LastName
 FROM dbo.Customers
GO

We can use a statement like below to return all the customer records with three columns: CustomerID, FirstName, LastName.

SELECT * from dbo.vwSample

We can use the statement like below to see the content of view:

Sp_helptext vwGetCustomers

Different Uses of Views:

Views can also be used to insert, update and delete data from a table.

Example 1: Insert View Example

INSERT INTO dbo.vwSample
VALUES ('Test1','Test1')

SELECT * from dbo.vwSample
SELECT * from dbo.Customers

Example 2: Update View Example

UPDATE dbo.vwSample
SET LastName = 'B'
WHERE CustomerID = 1

SELECT * from dbo.vwSample
SELECT * from dbo.Customers

Example 3: Delete View Example

DELETE FROM dbo.vwSample 
Where CustomerID > 2

SELECT * from dbo.vwSample
SELECT * from dbo.Customers

Difference Between Views and User Defined Functions:

Views and User-Defined Functions almost serve the same purpose. But the major difference is that User-Defined Function can accept parameters, where as Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.

Addition of New Column’s in the Underlying Table will not automatically reflect in the existing views:

Let us prove this behaviour by creating a view vwGetCustomers  which returns all customer details with all the columns in the customer table:

 

Create View dbo.vwGetCustomers
AS
SELECT *
FROM Customers
GO
Select * FROM vwGetCustomers

Now add one more column Country to the Customers table:

ALTER Table Customers
ADD Country Varchar(30)

Execute the below statement and observe that the new column country added in the Customers table is not present in the result.

SELECT * From dbo.vwGetCustomers

The only way to reflect this new column in the view is to drop and create back the view as below:

Drop View dbo.vwGetCustomers
GO
Create View dbo.vwGetCustomers
AS
 SELECT *
 FROM dbo.Customers
GO
SELECT * From dbo.vwGetCustomers
GO

Below is an example view where it returns the data from multiple tables by joining:

Create View dbo.vwGetCustomerOrders
AS
 SELECT C.FirstName,O.OrderId
 FROM dbo.Customers C 
   INNER JOIN dbo.Orders O
    ON C.CustomerId = O.CustomerId
GO
Select * from dbo.vwGetCustomerOrders

[ALSO READ] How to check if a VIEW exists in Sql Server