MSSQL View - Change In Underlying Table And Impact In .NET Application

Introduction

Our .NET application was working fine, and we got the same change request and feature addition in the application. We changed our code and added new features according to the new requirement. We went through all of the software development life cycles and deployed them in the staging server of the client. However, our test team missed some areas, as we needed to urgently deliver that project. Because of that, while loading data on the user side, there was an error. Oops, something went wrong!

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

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

Objective

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

What is View in SQL?

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

Why do we need View in SQL?

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

Data Security
Ease of Use
Query Simplicity

The syntax to create a 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];

Syntax 3

CREATE VIEW view_name AS    
SELECT * FROM table_name

Method to get data from view or query to select View.

Select * from ViewName

Let's suppose we have a table Customer with the 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:

Example 1

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

We can Join multiple tables and create Views as per our requirements. Below, I have provided a sample example query to create View with joining. Here, I have joined the Customer Table with UserInformation Table.

Example 2

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 an original table and Demo_View is created by selecting all columns from the Customer table for this scenario.

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

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

Query to Alter View

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

What was the issue?

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

However, If we haven't used select * from a statement in View and used a query like just below to select columns, then it couldn't create any issue with View even though we added a new column in the 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 this?

There are two methods, as we discussed above:

Run Alter query
Use column name in Select statement

Though method one also resolves the issue for the moment in the future, if the same kind of changes or feature addition comes, the same issue may arise. Hence, for a long-term solution, I opted for method two and change View by writing all column names in the select statement.

After going through this issue, what I can recommend is not to use the select * statement in your View to be on the safe side. The best practice is to use column names in select queries. Shortcut ways always create problems in the future, write the column name in your View to replace *.

Conclusion

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