- Create a table
CREATE TABLE [dbo].[tb_tes_test](
[tes_id] [int] IDENTITY(1,1) NOT NULL,
[tes_name] [varchar](50) NULL,
[tes_date] [datetime] NULL,
CONSTRAINT [PK_tb_tit_titti] PRIMARY KEY CLUSTERED
(
[tes_id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
- Add records to table
tes_id tes_name tes_date
1 tiz 2019-11-28 00:00:00.000
2 cla 2019-10-28 00:00:00.000
3 mar 2019-09-28 00:00:00.000
- Create a view for the table
CREATE VIEW [dbo].[vw_test]
AS
SELECT dbo.tb_tes_test.*
FROM dbo.tb_tes_test
- Add new field to the table between two existent fields - Insert values for the field
tes_id tes_name tes_candy tes_date 1
1 tiz com1 2019-11-28 00:00:00.000
2 cla com2 2019-10-28 00:00:00.000
3 mar com3 2019-09-28 00:00:00.000
- Run the view with a select * from vw_test
The result is:
tes_id tes_name tes_date
1 tiz com1
2 cla com2
3 mar com3
**I know that I have to run the alter view to update it, but what if I forget to do it?
Is there an option that update the view automatically?**
You can't update the view automatically, when a new column is added to a table. A view is a stored query, that accesses one or more tables or views.
As views are often used for security purposes, when you don't want the user to have access to all columns on a table (for example hiding employee salaries, etc). Having views that automatically add new columns would be a bad idea. I'm afraid that if you want to add the column in to the view, then you would need to make the conscious decision to do this.
If you add the SCHEMABINDING construct in your view definition then, when you try to add the new column to the table, it will prevent you due to the binding to the table from the view. This will act as a reminder for you to update the view, at the same time.