Index in SQL with examples

Data Storage:

The data in SQL Server is stored in the form of data pages.

The size of each data page is 8kb.

The data page contains the page header which is 96 bytes.

The header will have previous pointer (PP), object id, next pointer (NP).

The previous pointer pointing to the previous page, this value is NULL for first page.

The next pointer pointing to the next page, it’s value is NULL for the last page.

Data Access:

In SQL Server Data will be accessed in two ways.

Table Scan : It starts from beginning of the table and scan each and every page for required data, it extracts the rows to meet the query criteria.

Using Index: When index is created for a table, it travels through index tree structure to determine the required rows and extracts the rows which will meets the query criteria.

What is Index

Database Index is a schema object created to speed up the retrieval process by using a pointer. It can reduce disk I/O by using rapid path access to locate data quickly. If you don’t have index full table scan will happen. Logically and physically independent of the table. For example if you take a library in engineering college, you will be having so many books of various departments in engineering college. How will you know your department books are there mean those are indexed or arranged according to the department level and subject level, so it is for you to pick the required book right. Same thing will happen on databases also.

When to create an index:

A column contain wide range of values.

On primary key column.

Columns that are used to retrieve data in the sorting order.

A column contains large number of values

One or more columns are frequently used together in a WHERE or JOIN conditions

The table is large and most queries are expected to retrieve less than 2 to 4% of rows

When not to create an index:

The table is small.

The columns contains duplicate or null values.

The columns infrequently used in WHERE clause.

The columns contains binary and text information.

The columns are not often used as condition in the query

The table is updated frequently.

Most queries are expected to retrieve more than 2 to 4% of rows

Types of indexes or indices in SQL:

Clustered Index

Non Clustered Index

Unique Index

Composite Index

Create Index Syntax:

CREATE INDEX index_name
ON table_name (column1,[column2])

Clustered Index:

As we discussed, the performance will increase by creating the indexes, It also depends upon the way the data is getting stored in a database table. By creating this clustered index the data will be sorted as the way the clustered index is created. For example if we take a employee table and we created a clustered index on employee number, while querying on this table the data will be sorted based on the clustered index column here in the case based on employee number and retrieve the data quickly.

Clustered Index Syntax:

CREATE CLUSTERED INDEX Index_name
ON table_name (column_list)

Example:

CREATE CLUSTERED INDEX emp_empno_clus_index
ON EMP (employee_number)

Non Clustered Index:

Non clustered index actually stores a value and a pointer to that value. Whenever any query is issued to retrieve the data nonclustered will take more time to retrieve the whole set of row using pointer, while with clustered index it will be very fast. We can create number of non clustered indexes where as clustered index should be only one to avoid the ambiguity of sorting order. It is the default index created on a table. 249 non clustered indexes are allowed for a table.

Syntax:

CREATE NON CLUSTERED INDEX Index_name
ON table_name (column_list)

Example:

CREATE NON CLUSTERED INDEX emp_empno_clus_index
ON EMP (employee_number)

Unique Index:

An index with unique constraint is called unique index, it will not allow the duplicate values.
Syntax:

CREATE UNIQUE INDEX Index_name
ON table_name (column_name)

Composite Index:

Unique index is created more than one column that is called composite index.

Syntax:

CREATE UNIQUE INDEX Index_name
ON table_name (column_list)

Function Based Indexes:

It is based on expressions. The index expression is built from table columns, constants, SQL functions, and user defined functions.

Example:

CREATE INDEX upper_dept_name_idx ON dept (UPPER(dept_name)='SALES')

When the below query rans then the above function based index will comes into picture for retrieval process.

SELECT *  FROM dept  WHERE (UPPER(dept_name)= ‘SALES’

Data Dictionary table for Indexes on oracle:

USER_INDEXES: This view contains the name of the index and its uniqueness

USER_IND_COLUMNS: This view contains the Index name, table name and column name.

Previous :User Defined Functions in SQL