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:
- 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 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.