Indexes in SQL Server

Retrieving of rows from a table or a view using indexes is faster. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-Tree) that enables SQL server to find the row or rows associated with key values efficiently and quickly.

Disadvantages of having indexes:

Occupies memory in the Hard Disk. Insertions, Updations and Deletion will suffer in indexes (due to the page splitting).

Types of Indexes:

There are two types of indexes:

  1. Clustered Indexes.
  2. Non-Clustered Indexes.

 Differences between Clustered and Non Clustered Indexes:

Below are the differences between Clustered and Non Clustered Indexes

Clustered Index Non – Clustered Index
Only one clustered index is possible for a table. 249 Non – Clustered Indexes are possible for a table.
All Clustered Indexes are not dependent on non – clustered indexes. All non – clustered indexes are dependent on clustered indexes.
When we drop a clustered index all existing non-clustered indexes will be rebuild. When we drop a non-clustered index all existing clustered index will not be rebuild.
The data order and the index order is same. The data order and the index order is not same.

Non Clustered Index:

Below is an image for Non Clustered Index without a clustered Index:

Basic unit storage space in database is called as Page. 8 continuous pages is called as extents. The data is stored in the pages. In the above figure., we see that the extent contains records(X,M,K,P,etc).

Assume that we have non – clustered index on the ename column. For non clustered indexes, data is stored in an unsorted manner in the data file.

The data at the leaf level index page is stored in a sorted manner. Each page in the leaf-level index page contains data that is sorted and a row locator.

Row locator contains 3 parts:

  1. File.
  2. Page.
  3.  Record.

 For instance, for first row, the leaf level index page contains A 1 2 1., this implies that record A is at 1st extent->2nd page->1st record.

The address of each leaf level index page is stored at non-leaf level index page. Root contains the address of each non-leaf level index page.

The root is stored in sysindexes table of corresponding database.

Clustered Index:

Below is an image for Clustered Index:

In Clustered Index, the data is sorted at the base level. The address are stored at leaf level. Root contains the addresses that are stored at leaf level.

In the above figure, we can see that a clustered index is created on Empno column of Emp Table. While insertions only the data is stored in a sorted manner. The address are stored at the Leaf leavel.

Non Clustered Index with the Clustered Index:

The above fig., illustrates the non-clustered indexes with the clustered index. Ename has the non – clustered index and eid has the clustered index. The data for the clustered index will be in a sorted way.

Thus, we can see that eid column have the data that is in sorted order. In clustered index, the address of the data files is stored in the clustered index.

The clustered index page contains the data(1st record) of each page and address of each page in the extent. Leaf level index pages also contains the clustered index data. The clustered index page is stored in the sysindexes table.

In order to drop non – clustered and clustered index, first drop the non-clustered index, this is because if we drop clustered index first then the non clustered index again rebuilds the row locator.

Covered Index Query

If a query is answered by the index page itself then it is called as covered indexed query or covered query. (possible only when clustered and non-clustered indexes are there and in WHERE condition non-clustered index column is given.

Example:

SELECT 
  * 
FROM emp 
WHERE ename=’D’

Here, we can see that in where condition non-clustered index column is given, hence this query is called as covered query.

Fill Factor:

How much percent of data should be filled at leaf level is called as Fill Factor.

For example, if we write fill factor=60%, then for each index page 60% stores the data and the remaining 40% is empty space.

Pad Index:

How much percent of space should be filled at non-leaf level is called as pad-index.

Pad index should be specified with the fill factor, the value for pad index should not be given separately.

Example:

pad_index, fillfactor=60 -- correct declaration
Pad_index=20,fillfactor=80 --Wrong declaration

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80, PAD_INDEX = ON)

Difference between OLTP and OLAP:

Below are the differences between OLTP and OLAP Processes:

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: