MSSQL View, Change in Underlying Table and Impact in .NET Application

Problem Background

Our .NET application was working fine, we got same change request and feature addition in the application. We change our code and added new features according to new requirement. We went through all Software development life cycles and deployed in staging server of client. However, our test team miss some area to check properly as we were in urgent delivery of that project. Because of that, while loading data in User side there was error: Oops something went wrong.

We investigated the solution and found nothing wrong in our code area. We even did not change any code for this page. Later on, we went through Requirement document and checked all the changes and came to know that we have changed Data base and added few columns in one table of database and the page, which was throwing error, was using the View with Select * statement of that modified table.

So, in this article I will describe how to handle such situation and explain about View with some examples.

Introduction

This article will describe the impact on view containing select * from statement, if some column added in original table after the creation of view. Besides this, the write up contains simple query to create and alter view to give some ideas how to tackle such situation with an example.

What is View?

A View is a virtual table which is designed based on the SQL statement using the other Tables. As just as real Table, View contains columns and rows. We can use SQL functions, WHERE and JOIN statement in View and it gives data as like coming from real table. In another way, we can say it is subset of table. In short, a View contains query to pull data from table. Views are virtual and those views run the query definition each time whenever we access. The contents of View are defined by query.

Why we need View?

The main benefit and use of view is: we can write complex query to select data from various tables. So, instead of writing complex query to get data from database each time, we can just create View with complex query using Join, SQL function and WHERE statement and simply can use it by just selecting as like table select. Some of other benefits are:

Syntax to create View is given below.

Syntax 1

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Syntax 2

CREATE VIEW view_name AS
SELECT * FROM table_name
WHERE [condition];

View Select query

Select * from ViewName

Let’s suppose we have table Customer with following structure.

CREATE TABLE [dbo].[Customer](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] nvarchar NOT NULL,
[Address] nvarchar NOT NULL,
[PhoneNo] nvarchar NOT NULL,
[Email] nvarchar NOT NULL,
[Nationality] nvarchar NULL,
[Active] [bit] NOT NULL,
[Author] [bigint] NOT NULL,
[Created] [datetime] NOT NULL,
[Editor] [bigint] NOT NULL,
[Modified] [datetime] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Simple SQL Query to create View using Customer table.

Create view [dbo].[Demo_View] as select * from [dbo].[Customer]

We can Join multiple tables and create view as per our requirements. Below I have given sample example to create View with Joining.

CREATE VIEW [dbo].[Demo_CustomerView]
AS
SELECT dbo.Customer.*, dbo.UserInformation.UserName AS CreatedBy
FROM dbo.Customer INNER JOIN
dbo.UserInformation ON dbo.Customer.Author = dbo.UserInformation.Id AND dbo.Customer.Editor = dbo.UserInformation.Id

For simplicity, we will go with example 1 of view creation for the problem statement. Here, Customer is original table and Demo_View is created by selecting all columns from Customer table for this scenario.

If we change some columns in Customer Table then we need to run Alter View Query for Demo_View. Because, view is created with original table information including the previous columns. If we add some new columns in Customer, previously created view does not get newly updated or added changes in view automatically. Thus, view can throw error in your application side.

So, to prevent this if we add something in original Table and our View contains  Select * from statement in View using that Table then we need to run Alter the query for view after changing or adding any columns in original table.

Query to Alter View

Alter view [dbo].[Demo_View] as select * from [dbo].[Customer]

What was issue?

The issue in my case was same as described above, we had select * from statement in our view and some column added into the main table by another developer who was working on change request . Moreover he had no idea or information that, this Table is used by View.

However, If we haven’t used select * from statement in View and use query like just below to select columns, then it couldn’t create any issue with View even though we add new column in original table.

Query to create View without selecting all ( *)

Create view [dbo].[View_Report1] as Select Name, Address, PhoneNo, Email, Nationality from Customer

In this case, if we add any column in Customer Entity then it doesn’t  affect view [View_Report1]

How we resolved?

There are two methods as we discussed above.

1. Run Alter query
2. Use column name in Select statement

Though, method one also resolves the issue for the moment but in future if same kind of changes or feature adding come, same issue may arise. Hence, for long-term solution I opted for method two and change View with writing all column name in the select statement.

After going through this, what I can recommend is do not use select * statement in your View to be in safe side. Best practices is to use column name in select query. Shortcut ways always create problem in the future, write column name in your View to replace *.

Conclusion

Hence, this article has described impact of column add in the Table which was already used by View using select * statement. Additionally, it provided simple example queries to create, alter view and brief introduction about View . I hope, this will help you to write standard, qualitative and error free SQL View so that your application can run without any impact even anything need to change in the table structure.