What is a View?
A view is a subset of a database that is generated from a query and stored as a permanent object. Although the definition of a view is permanent, the data contained therein is dynamic depending on the point in time at which the view is accessed.
A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement.
Syntax
Why is it used?
A view provides the following features:
1. To structure the data – Data from different tables can be taken together with joins so that they look like a single table hence simplifying the structure of data.
2. To restrict the access of data – Only the relevant data can be pulled from the tables based on the where clause of the view definition. Thus, the users can be restricted to have access only certain columns or certain rows of the base tables.
3. Summarize data – Views can be used to aggregate the information from the detailed level base tables.
What is an Index?
An index is a data structure that the database uses, to search records within a table more quickly.
An index is created on a column of a table. So, the key points to remember are that an index consists of column values from one table, and that those values are stored in a data structure.
Syntax:
What are the types of indexes?
Indexes are broadly of two types: Clustered and Non-Clustered
1. Clustered Index: A clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index.
Therefore, only one clustered index can be created on a given database table.
For example:
Suppose in a table ‘Employee’ we create clustered index on EmployeeID what will happen to the table? All the records inserted in the table will be stored physically in the order of the index. This makes the query on EmployeeID very fast for this table.
When to use clustered index?
Let’s take an example:
There are two tables Owner(OwnerID, Name, Age, Address, Contact No) and Vehicle(OwnerID, VehicleNo, Model No, Color, Chassis No).
Let’s say we want the list of all the vehicles against a person ‘X’.Desired result is Something like (OwnerID, Owner Name, Address, Vehicle No)
For this we need to join the two tables and search for all the vehicles in the Vehicle table for that particular person.
In this case it makes sense to have a clustered index on OwnerID in the Vehicle table since it will make sure that all the records pertaining to a particular OwnerID are stored together in an order thus hitting one record means finding the other relevant records also in the vicinity.
Any disadvantage of using clustered index?
Yes!!
In case the value of a column is to be updated for a row, and the clustered index is defined on this column. Then the entire row has to be moved from its current physical location and shifted appropriately to fit it in a proper sequence as per the new value of the column to preserve the sequence defined by the clustered index.
Thus a simple UPDATE operation becomes DELETE+INSERT operation.
2. Non-Clustered Index: A non-clustered index will store both the value of the EmployeeID AND a pointer to the row in the Employee table where that value is actually stored.
This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values, as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself. So, reading from a clustered index is generally faster than reading from a non-clustered index.
Important: A table can have multiple non-clustered indexes but only one clustered index.
CareerShapers 