Contents
Can we update values in view?
You can’t directly modify data in views based on union queries. You can’t modify data in views that use GROUP BY or DISTINCT statements. All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
When I update a view in SQL does my original table gets affected?
Yes, when you update a view it affects original table, because SELECT opens an implicit cursor which points to the records from the base table. When ever you update the records it updates the original data since they are pointers to the original.
When does updating a view affect the base table?
In some databases it’s possible to update the source table (s) for a view if there is a one-to-one relationship between the rows in the view and the rows in the underlying table, that is, you cant have derived columns, aggregate functions or a distinct clause in your view for example.
Why does my view change when my table changes?
Generally the argument is that if I put “SELECT *” rather than an explicit field list, then when my table changes so will my view. Unfortunately it doesn’t work that way. Let’s try an example. Create a test table and populate it with some data.
Is the data in view updated in the database?
Yes , View gets updated. Please note that View is a logical statement and the data in view is not stored anywhere, the data is returned from database every time we select data from view. Eg. Table 1 stores the Details of Clients i.e. Name, Age , Phone Number, IsYourCustomer. And your view,View-1 shows just Name and Clients who are your customer.
How are table changes reflected in SQL Server?
When a view is created in SQL Server, metadata for the referenced table columns (column name and ordinal position) is persisted in the database. Any change to the referenced base table(s) (column re-ordering, new column addition, etc) will not be reflected in the view until the view is either: Altered with an ALTER VIEW statement.