Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Indexed Views

SQL views and indexes

View :

View is a virtual table created by defining SQL query on one or more table.

Index:

An index is used to speed up the performance of queries.

Indexed view:

Creating a unique clustered index on a view is an indexed view.

If the view is indexed then any queries that can be answered using the index only will never need to refer to the underlying tables. This can lead to an enormous improvement in performance.

How to crate:

A view that is to be indexed has to be created with schema binding. This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. It also means that all the tables referenced in the view must be referenced by their two-part name (schemaname.tablename).

Example:

CREATE VIEW MyCartView WITH SCHEMABINDING AS
SELECT CartId, Quantity
  FROM [dbo].[CartItem]
 WHERE Quantity > 10
GO
CREATE UNIQUE CLUSTERED INDEX idx_MyCartView ON MyCartView(Quantity

sql views advantages

In this article I will explain benefits of using view in detail, following are some main advantages I listed with detail.

SQL views benefits:

Performance:

Views take little space to store; Database store definition of SQL query not actual data reason view is faster.

Security

 Views can be made easy accessible to users while the underlying tables are not directly accessible.

Easy and Reusable:

Views can join and simplify multiple tables into a single virtual table.
View Hide complex queries provide easy view, With you can reuse complex queries logic.

Aggregated data:

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.

Consistency:

Schema changes not required to add/remove fields with view, view provide consistent, unchanged image of the structure of the database, Even if the underlying source tables are split, restructured, or renamed.

Data Integrity

If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

Data independence:

View is abstraction on table structure and view is independent with actual table data.
View logic is independent with actual database table structure and actual data.

 

 

 

 

SQL Views VS Tables

SQL Views:

View is a virtual table based on the result-set of an SQL statement and that is Stored in the database with some name.

SQL Table:

SQL table is database instance consists of fields (columns), and rows. In relational databases and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows; the cell is being the unit where a row and column intersect.

SQL Views VS Tables:

Sr. No

View

Table

1

Provides summery on table, contain multiple table data.

Table data in columns and rows.

2

You can add/remove fields easily in a view without modifying your underlying schema.

You require schema changes if want to add/remove fields.

3.

You lose information about relations (primary keys, foreign keys)

Relation are maintained in table and saved in table.

4.

View contain complex multiple table joins and its data.

In table you can maintain table relationship using primary and foreign key.

5.

Grant access easily.

To provide access require security permissions.

6.

Increase performance.

Actual data in table.

7.

You can aggregate data in views.

With table this is not possible.