How to update view when new column added in table in sql server

Introduction

When we are work with sql server view and tables, there is strange issue you will face. You have created table and a view for that table. Now you are going to alter your table with one more column, in that case, sql server does not update view automatically based  according your new column.

Case

Create a new table using following script

GO
CREATE TABLE [dbo].[TestHeader](
	[TestID] [int] NULL,
	[Descriotion] [nvarchar](50) NULL
) ON [PRIMARY]

Now create a simple view for this table

create view vTestHeaderAll
as
SELECT [TestID]
      ,[Descriotion]
  FROM [TestHeader]

Then I’m going to use this view to select records

select * from vTestHeaderAll

Output 

Now I want to add another column into the table. Let’s say I want to add SortID column into the table

alter table TestHeader add SortID int NULL

Output

When open table, i could see the new column inside the table. 

Now lets run select qurey to view again.

select * from vtestheaderAll

But output is same like earlier. So what we can do to update new column in the view. There is three ways

1. Remove and recreate view
2. Alter the view

alter view vTestHeaderAll  
as  
SELECT *
FROM [TestHeader]

3.refresh the view using inbuilt stored procedure.

EXEC sp_refreshview vTestHeaderAll

Once you are done one of above action, let's run select query again for view.

select * from vtestheaderAll

Output 

Now see you able to see the new column inside the view. I have used here third way to do add new column in view. I hope this is help to all.