SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files<You are Here>

Continuing from my previous post on this series, If you have missed any link please visit link below

We are going to Cover the Following Points in this article

• Introduction
• Primary Index
• Clustering Index
• Secondary Index

Introduction

© Indexes are used to speed up the retrieval of records.

© Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

© The disk space required to store the index is typically less than the storage of the table (since indexes usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table).

© Index file consists of two fields, the first field contains the value and second field contains the list of pointers to address values in the disk block

© Searching an index is much faster than searching the table because the index is sorted and its rows are very small.

© Index access structure is usually defined on a single field of a file, called an indexing field.

There are several types of indexes:

1. Primary index

2. Clustering index

3. Secondary index

4. Multilevel index

5. B – Trees and b + trees

Primary Index

· A primary index is an index specified by ordering key field.

· It consists of two fields

1. First Field: This is of the same Data type as the primary key field of the data file

2. Second Field: Pointer to a disk block.

· For each block in the data file there is a corresponding index entry in the index file

· Each index entry has the value of the primary key field for the first record in the block and pointer to that block.

· Performing the search operation on the index can be done more efficiently as the Index file is much smaller than the data file.

Block Anchor: Block anchor is also referred as anchor record. The number of entries in the index is equivalent to the number of disk blocks in the sorted data file. The data file is called the block anchor.

The indexes are differentiated into two types

· A dense index

· A sparse Index

A dense index has an index entry for every record since they have an entry for every search key value in the data file.

A sparse index has index entries for only some of the search values.

Figure Primary Index on the ordering key field of the file

Clustering Index

· A Clustering Index is an index specified by ordering non-key field of a data-.

· That field is called the Clustering Field.

· Unlike a primary index, this may have duplicate values.

Figure Clustering index on the DEPTNUMBER ordering non key field of an EMPLOYEE file

Secondary Index

· Secondary index is an ordered file with two fields.

· The first field is of the same data type as some non-ordering field of the data file. i.e., indexing field. The second field is either a block pointer or a record pointer. In this case there is one index entry for each record in the data file, which contains the value of the secondary key or record; pointer points to the block in which the record is stored or to the record itself. Hence it is an example for ‘dense index’. The records of the data file are not physically ordered by values so we can’t use block anchors; that’s why an index usually needs more storage space or longer search time as compared to primary index.

Figure  A dense secondary index on a non-ordering key field of a file.

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |