SQL - INDEX & VIEWS

INDEXES:

·         Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

·         Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

·         An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.

The CREATE INDEX Command:

The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name ON table_name;

 TYPES OF INDEXES:

1. Single-Column Indexes:

A single-column index is one that is created based on only one table column.

CREATE INDEX index_name

ON table_name (column_name);

2. Unique Indexes:

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table.

CREATE UNIQUE INDEX index_name

on table_name (column_name);

3. Composite Indexes:

A composite index is an index on two or more columns of a table.

CREATE INDEX index_name

on table_name (column1, column2);

4. Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

The DROP INDEX Command:

An index can be dropped using SQL DROP command.

DROP INDEX index_name;

When should indexes be avoided?

·      Indexes should not be used on small tables.

·      Tables that have frequent, large batch update or insert operations.

·      Indexes should not be used on columns that contain a high number of NULL values.

·      Columns that are frequently manipulated should not be indexed.

Views:

A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

Views, which are kind of virtual tables, allow users to do the following:

·      Structure data in a way that users or classes of users find natural or intuitive.

·      Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.

·      Summarize data from various tables which can be used to generate reports.

Creating Views:

Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.

To create a view, a user must have the appropriate system privilege according to the specific implementation.

CREATE VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

You can include multiple tables in your SELECT statement in very similar way as you use them in normal SQL SELECT query.

Example:

Consider the CUSTOMERS table having the following records

CREATE VIEW customer_view AS

SELECT lastname, city

FROM customer;

select * from customer_view;