A View is a saved SQL query which act as a Virtual table. View is generally used for security mechanism by allowing user to access the data through View rather than granting the other user to directly access the db.

Syntax to create a View

CREATE VIEW vwViewName
as
Select Query
----
---

We have two table tblperson and tblGender, i want to create a view which shows all the person records along with it corresponding Gender as shown below:

 CREATE VIEW vwPerson
  as
  select [TBLPERSON].[Name],[TBLPERSON].[Email], tblgender.Gender FROM [TBLPERSON]  inner join tblgender on [TBLPERSON].[GenderId]=tblgender.id

Retrieving data from the View

select * from vwPerson

Updateable View

One of my friend asked my today that can we update records uusing a View and th3e answer is Yes. View can be used to update,insert or delete the records in underline table used in View. Internally all the statements are fired against the base table present in View. In order to demonstrate that View can be used to insert, update and delete records from the base table, lets take above mentioned view and try to update the records on the same.

I want to update email address of Saillesh. So in order to do the same i will trigger the update query as mentioned below:

  update [dbo].[vwPerson] set Email='shaillesh.pawar@microsoft.com' where Name='Saillesh'

Once we run the query, we will se output 1 row affected. Now in order to check whether the corresponding base table has been updated or not? lets query the person table.

When we try to update the column which is basically a join of two columns the update command is not executed properly resulting in incorrect behavior. If i want to update gender of Nishant to Male. I will execute the below query.

  update [dbo].[vwPerson] set Gender='Male' where Name='Nishant'

But once the query is executed we will see the below output, which seems perfect but when we dig deep we will find that the update command has been run over child table which will result in inappropriate results when we have others records with Gender as “Female”

Below we will see rather than updating the corresponding ID for the gender in parent table it updated the Gender of child table to “Male” which will result in appropriate data.

In order to handle these kind of update issues we make use of Triggers.

Indexed View

Indexed View is stored sql query similar to stored procedure which is stored in cache. By default View doesn’t store any data on it’s own. But if we create a index on View it gets Materialized, which enables view to store data.

In order to deomonstrate the same lets take an example of two tables Order and Product table as mentioned below:

Records present in both the tables are shown below:

Lets create a view which returns the total sale and number of each product which has been sold.

Steps for creating Indexed Views or Materialized View

• View should be created with SchemaBinding option.
• If there is a expression in select list which value can be null than alternate replacement value should be defined.
• If Group by is specified use Count_big(*) returns bigInt
• Base table to be referred as two part name schemaName and tableName as shown below in figure:
• First Index to be create on Index View has to be unique clustered Index

Creating an Index on the View

Now if i try use count(*) instead of count_big(*) sql server will throw me error to use count_big(*) instead of count(*), when we try create Index on View as shown below:

Changing count(1) to count_big(*))

alter view VwTotalOrderByProduct
with SchemaBinding
as
select [Description],
SUM(isnull((Quantity * price),0)) as Sales,
count_big(*) totalCount
  from
  [dbo].[mstOrderDetails] orders inner join
  [dbo].[Product] prod on orders.productid=prod.[ID]
  group by prod.[Description] 

Creating Index on View

Now whenever we execute the View it will retrieve the records from the Index rather than going to the actual base table. If any row is inserted the View is also again computed again. Indexed Views are basically used where records are rarely changed.

Limitation of Views

• • Order by clause is not allowed in View

Order by clause should be used outside the View not inside the view. If we try to use Order by clause inside View it will threw an error as shown below:

But the error message say we can use Order by clause if we use Top, Offset, For xml. Lets try the same.

But when we tried executing the View the output doesn’t seems to follow the order by clause.

select * from [dbo].[vw_ViewPersons]

When we try to execute above mention query, sql server will perform percent on 100% on 3005 records which after round of returns 3005 records. Now lets execute the above query.

We can see the that returned records are not in Order by clause. Lets see the SQL plan for the same.

We can easily conclude that the it doesn’t perform the sorting for the same. While if we only show rows less than the number of rows present in table than Order by clause. We will change our View as per below logic.
USE [master]
GO
/****** Object: View [dbo].[vw_ViewPersons] Script Date: 12-Jul-17 11:58:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter view [dbo].[vw_ViewPersons]
as
SELECT TOP 10 percent [PERSONID]
,[FIRSTNAME]
,[MOTHERID]
FROM [SampleDb].[dbo].[Person]
order by MOTHERID desc

GO

Now this view will return 301 records with Order by Clause.

• We cannot create parameterized view

We cannot create parameterized view instead we can use where clause on View. In order to create a parameterized view we can make use inline table valued function which takes in a parameter and returns a table.

• View cannot be created on Temporary table
• Column issue in View

Once the view is created and after sometime we add a new column to the table. It’s not reflected in View till is refreshed.

USE [SampleDb]
GO

/****** Object:  View [dbo].[vw_ViewPersons]    Script Date: 13-Jul-17 10:10:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER view [dbo].[vw_ViewPersons]

as
SELECT TOP 100 percent *
  FROM [dbo].[Person]
  order by MOTHERID desc

GO

Now we will add one more column to the table Person.

alter table [dbo].[Person]
add lastName nvarchar(40)

Now let’s try to run the View and see whether the new column is reflected or not?

Hence we can conclude that if we add new Column it will not be reflected to View. Let try to refresh the View and than try.

SP_REFRESHVIEW [vw_ViewPersons]

Same drawbacks remains when we delete any column from the table

alter table [Person]
drop column lastname

Now if we try to run our View we will see the following output

After refresh