Views can centralise or consolidate data and they save a lot of repeated complex JOIN statements in your SQL scripts. This would sometimes be handy, straight forward and easier than writing out the join statements in every query. Views also provide an abstracting layer preventing direct access to the tables.
In another parallel with functions, database users can
manipulate nested views, thus one view can aggregate data from other views.
Without the use of views, the normalization of databases above second normal
form would become much more difficult. Views can make it easier to create
loss less join decomposition.
Views can provide advantages over tables:
- Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
- Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.
- Views can join and simplify multiple tables into a single virtual table.
- Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.
- Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
- Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
- Depending on the SQL engine used, views can provide extra security.
- Views can also be used when you copy data to and from SQL Server to improve performance and to partition data.
- Views can be used to provide a backward compatible interface to emulate a table that used to exist but whose schema has changed.
- Views are a nice way of providing something simple to report writers.
- In a sense views deformalize, Denormalization is sometimes necessary to provide data in a more meaningful manner.
Due to having the virtual nature, a view has no physical
existence in the database until it is invoked and this is the biggest reason
that you cannot put constraints on a view. Just like a base table name, the name of the view
must be unique within the entire database schema. The view definition cannot
reference itself, since it does not exist yet and they are generally used to focus, simplify, and customise
the perception each user has of the database.
https://msdn.microsoft.com/en-us/library/ms190174(v=sql.110).aspx
Types of Views
Besides the standard role of basic user-defined views, SQL
Server provides the following types of views that serve special purposes in a
database.
Indexed Views
An indexed view is a view that has been materialized. This
means the view definition has been computed and the resulting data stored just
like a table. You index a view by creating a unique clustered index on it.
Indexed views can dramatically improve the performance of some types of
queries. Indexed views work best for queries that aggregate many rows. They are
not well-suited for underlying data sets that are frequently updated.
Partitioned Views
A partitioned view joins horizontally partitioned data from
a set of member tables across one or more servers. This makes the data appear
as if from one table. A view that joins member tables on the same instance of
SQL Server is a local partitioned view.
System Views
System views expose catalog metadata. You can use system
views to return information about the instance of SQL Server or the objects
defined in the instance. For example, you can query the sys.databases catalog
view to return information about the user-defined databases available in the
instance. Microsoft SQL Server provides the following collections of
system views that expose metadata:- Catalog Views: Catalog views return information that is used by the SQL Server Database Engine and they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.
- Information Schema Views: An information schema view is one of several methods SQL
Server provides for obtaining metadata. Information schema views provide an
internal, system table-independent view of the SQL Server metadata. Information
schema views enable applications to work correctly although significant changes
have been made to the underlying system tables. The information schema views
included in SQL Server comply with the ISO standard definition for the
INFORMATION_SCHEMA.
- Compatibility Views: Many of the system tables from earlier releases of SQL
Server are now implemented as a set of views. These views are known as
compatibility views, and they are meant for backward compatibility only. The
compatibility views expose the same metadata that was available in SQL Server
2000. However, the compatibility views do not expose any of the metadata
related to features that are introduced in SQL Server 2005 and later.
Therefore, when you use new features, such as Service Broker or partitioning,
you must switch to using the catalog views.
- Replication Views: These views contain information that is used by replication in Microsoft SQL Server. The views enable easier access to data in replication system tables. Views are created in a user database when that database is enabled as a publication or subscription database. All replication objects are removed from user databases when the database is removed from a replication topology. The preferred method for accessing replication metadata is by using Replication Stored Procedures.
- Data-tier Application Views (Transact-SQL): The following views (dbo.sysdac_instances (Transact-SQL), sysdac_instances_internal (Transact-SQL)) display information about data-tier application (DAC) instances. These views are in the msdb database and are owned by the dbo schema.
- Dynamic Management Views and Functions: Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of dynamic management views and functions:
- Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
- Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
When to Use a View
In our daily needs, there are a number of scenarios where we
will like to create our own View:
- To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
- To control the access on rows and columns of data in the tables.
Conclusion
A view is a virtual table, through which a selective portion
of the data from one or more tables can be seen and they do not contain data of
their own. They are used to restrict access to the database or to hide data
complexity. A view is stored as a SELECT statement in the database. DML
operations on a view like INSERT, UPDATE, DELETE affects the data in the
original table upon which the view is based. Views are a nice way of providing
something simple to report writers.
Learn more on another features of SQL as:
- SQL - Stored Procedure
- SQL - Create Stored Procedure
- SQL - Execute Stored Procedure
- SQL - Alter Stored Procedure
- SQL - Views
- SQL – Stored Procedure Vs Ad-Hoc (In-Line)
- SQL - Merge Statement
- SQL - Functions
- SQL - Cursors
https://msdn.microsoft.com/en-us/library/ms190174(v=sql.110).aspx
No comments:
Post a Comment