Using View in SQL Server
Using View in SQL Server
Views are virtual tables. They are really queries, but you can refer to them as though they were tables. For example, the following code creates a view :
CREATE VIEW CA_Authors AS
SELECT * FROM Authors
WHERE state = ‘CA’
You can then select directly from CA_Authors:
SELECT * FROM CA_Authors
And see only the California authors. Views can be used to limit the number of rows or columns that users can see. A DBA can grant rights to a view and not to be the original table, thereby hiding sensitive information from the user.
You can change a view in Transact-SQL using ALTER VIEW command, as in the following code :
ALTER VIEW CA_Authors AS
SELECT au_lname, au_fname FROM Authors
WHERE state = ‘CA’
This has the advantage that permissions do not need to be reset for the view.
You should know that if you drop and re-create a view, it gets a new ID in the sysobjects table and requires you to reset its permissions. If you use ALTER VIEW, the ID stays the same and you don’t need to reset permissions.
Table can be updated through view, provided view is against one table and does not contain any computed columns. Once those assumptions are in place, you can execute the Transact-SQL INSERT, UPDATE, and DELETE commands against the view. If you try to update a view that does not allow updates, the error messages may be cryptic, especially on the client. Be extra careful when trying to update views. So if you have a view that shows only a subset of a table’s data and you update the column on which the view depends, you may get surprising results. For example, if you update a view that contains only authors with state=’CA’ and you change a state value to ‘NY’, the resulting row will disappear. That is because it no longer fits the view.
To prevent updating view definition columns, compile the view using the WITH CHECK OPTION switch:
CREATE VIEW CA_Authors AS
SELECT au_lname, au_fname FROM Authors
WHERE state = ‘CA’
WITH CHECK OPTION
Written by,
Kasim Wirama, MCSD.NET, MCDBA
Database Administrator