SQL Server Views

What is View:

• A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database.
• It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.
• Once you have defined a view, you can reference it like any other table in a database.

Scenarios and Behaviour:

• 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. A view also serves as a mechanism to simplify query execution.
• Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.
• 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.

Creating View:

CREATE VIEW MyView AS

SELECT CustomerId, Company Name FROM Customers

Accessing View Data:

select * from MyView

Scenarios for Using Views:

• Views are generally used to focus, simplify, and customize the perception each user has of the database. Views can be used as security mechanisms by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables of the view. Views can also be used, when copying data to and from Microsoft® SQL Server™ 2000, to improve performance and to partition data.
  To Focus on Specific Data
• Views allow users to focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary data can be left out of the view. This also increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table. For more information about using views for security purposes
• To Simplify Data Manipulation
  Views can simplify how users manipulate data. You can define frequently used joins, projections, UNION queries, and SELECT queries as views so that users do not have to specify all the conditions and qualifications each time an additional operation is performed on that data. For example, a complex query that is used for reporting purposes and performs subqueries, outer joins, and aggregation to retrieve data from a group of tables can be created as a view. The view simplifies access to the data because the underlying query does not have to be written or submitted each time the report is generated; the view is queried instead. For more information about manipulating data.
  You can also create inline user-defined functions that logically operate as parameterized views, or views that have parameters in WHERE-clause search conditions.
  To Customize Data
• Views allow different users to see data in different ways, even when they are using the same data concurrently. This is particularly advantageous when users with many different interests and skill levels share the same database. For example, a view can be created that retrieves only the data for the customers with whom an account manager deals. The view can determine which data to retrieve based on the login ID of the account manager who uses the view.
  To Export and Import Data:
• Views can be used to export data to other applications. For example, you may want to use the stores and sales tables in the pubs database to analyze sales data using Microsoft® Excel. To do this, you can create a view based on the stores and sales tables. You can then use the bcp utility to export the data defined by the view. Data can also be imported into certain views from data files using the bcp utility or BULK INSERT statement providing that rows can be inserted into the view using the INSERT statement. For more information about the restrictions for copying data into views
• The Transact-SQL UNION set operator can be used within a view to combine the results of two or more queries from separate tables into a single result set. This appears to the user as a single table called a partitioned view. For example, if one table contains sales data for Washington, and another table contains sales data for California, a view could be created from the UNION of those tables. The view represents the sales data for both regions.
• To use partitioned views, you create several identical tables, specifying a constraint to determine the range of data that can be added to each table. The view is then created using these base tables. When the view is queried, SQL Server automatically determines which tables are affected by the query and references only those tables.
• For example, if a query specifies that only sales data for the state of Washington is required, SQL Server reads only the table containing the Washington sales data; no other tables are accessed.
• Partitioned views can be based on data from multiple heterogeneous sources, such as remote servers, not just tables in the same database. For example, to combine data from different remote servers each of which stores data for a different region of your organization, you can create distributed queries that retrieve data from each data source, and then create a view based on those distributed queries.
• Any queries read only data from the tables on the remote servers that contains the data requested by the query; the other servers referenced by the distributed queries in the view are not accessed.
• When you partition data across multiple tables or multiple servers, queries accessing only a fraction of the data can run faster because there is less data to scan. If the tables are located on different servers, or on a computer with multiple processors, each table involved in the query can also be scanned in parallel, thereby improving query performance. Additionally, maintenance tasks, such as rebuilding indexes or backing up a table, can execute more quickly.
• By using a partitioned view, the data still appears as a single table and can be queried as such without having to reference the correct underlying table manually.
  Partitioned views are updatable if either of these conditions is met:
• An INSTEAD OF trigger is defined on the view with logic to support INSERT, UPDATE, and DELETE statements.Both the view and the INSERT, UPDATE, and DELETE statements follow the rules defined for updatable partitioned views.

Dis Advantages of views:
So, if you have a view joining 10 tables and one of those tables has a million rows, your view will return at least 1 million rows. Joining such a view to a few other tables within a stored procedure could be disastrous.
What should you do? A better alternative in such cases would be a user-defined function (UDF), which accepts parameters and allows you to limit the number of returned rows. Another option is to join all tables within a single unit stored procedure and limit the output by using parameters.