Sunday, September 13, 2015

SQL – Views

Views are the most amazing feature in the SQL because they are just pre-established query commands known as Virtual Tables which are kept in the database dictionary. In some NoSQL databases, views are the only way to query data. Views hide the database complexity and they are good for providing security, de-normalization and  data model abstraction. Views are commonly used to implement business logic. 

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. 

Advantages of Views
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. 

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:
  1. Catalog ViewsCatalog 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.
  2. 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.
  3. 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.
  4. 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. 
  5. 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.
  6. Dynamic Management Views and FunctionsDynamic 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:
  1. To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
  2. To control the access on rows and columns of data in the tables.
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:
  1. SQL - Stored Procedure
  2. SQL - Create Stored Procedure
  3. SQL - Execute Stored Procedure
  4. SQL - Alter Stored Procedure
  5. SQL - Views
  6. SQL – Stored Procedure Vs Ad-Hoc (In-Line)
  7. SQL - Merge Statement
  8. SQL - Functions
  9. SQL - Cursors

No comments:

Post a Comment