Views in SQL Server

 Views:
SQL VIEWS are data objects, and like SQL Tables, they can be queried, updated, and dropped.
• Views are virtual tables containing columns and rows but the data is generated dynamically from SQL tables and hence physically it doesn’t exist inside the view itself.
• It always gives updated data.
• A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables.
• Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
• Database contains only the definition of the view table, without its definition.
• Database creates ABSTRACTION using views.
• Nested views reduce the complexity of normalization.
• Views are dynamic SQL query .Changes in the base table will be reflected in the subsequent invocation of views.
• Views can also be removed by using the DROP VIEW command.

Syntax:
CREATE VIEW view_name
AS
SELECT * FROM TABLE_NAME

(Or)

CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

Sample code:

                      CREATE VIEW virtualInventory
                       AS
                       SELECT * FROM Inventory;

Views ensure the security of data by restricting access to the following data:
• Specific rows of the tables.
• Specific columns of the tables.
• Specific rows and columns of the tables.
• Rows fetched by using joins.
• Statistical summary of data in a given tables.
• Subsets of another view or a subset of views and tables.
Some common examples of views are:
• A subset of rows or columns of a base table.
• A union of two or more tables.
• A join of two or more tables.
• A statistical summary of base tables.
• A subset of another view, or some combination of views and base table.
The restrictions imposed on views are as follows:
• A view can be created only in the current database.
• The name of a view must follow the rules for identifiers and must not be the same as that of the base table.
• A view can be created only if there is a SELECT permission on its base table.
• A SELECT INTO statement cannot be used in view declaration statement.
• A trigger or an index cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with other SQL statements in single batch.
• Order By clause cannot be implemented in CREATE VIEW query.

Types of views:
• Read-only
We cannot update the read-only views.
• Updatable-view
We can make changes in the views like update , delete, insert etc;

Posted in: SQL Server Tutorial