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 delivery of 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 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?

 

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 statement 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 query.

 

Why do we need View?

 

The main benefit and use case for 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 View as per our requirements. Below, I have provided a sample example query to create View with joining. Here, I have joined 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 original table and Demo_View is created by selecting all columns from 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 column added into 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 use 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?

 

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 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 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 productive SQL View so that your application can run without any impact even anything need to change in the table structure in the future.