Index in Database

An index is best described as a pointer to data within a table in a database.
is analogous to an index found at the back of a book or within a telephone directory.

An index is created either on a table or within a view.

Purpose of Index in database

the primary purpose of an index is to retrieve the large amount of data from a table efficiently and fast,

Heap

Any table or view  that does not have any form of indexes  is termed a heap, Heap is basically a database or a table that is not sorted in a particular way.

Lets create  Table as a head (Without any Indexes)

We can see our records in PhoneBook Table without any sorting order. Now if we insert any record it added at a last row in table

Clustered Index in Sql Server

Clustered indexes organize the data rows in a table or view according to their key values, allowing for only one clustered index per table.  When you create a clustered index, it does not require additional space.

Check data in Table ‘Phonebook’ , we can see all records are sorted by column lname. now with the clustered index in place, if we insert a record, it will automatically be sorted by column lname.

• Can cross check this index in left panel
  • 
• Just double click this index to open index properties in design mode

• Advantage of a Clustered Index
  • It primarily sorts the data based on the column you index to, and it does not take up space. All it does is rearrange it.

Can create a clustered index on multiple columns 

Non- Clustered Index in Sql Server

Non clustered index actually takes a space, for Example a Book Index, A book typically has a few more pages at the very end, which contain more reading material  or more space on the disc in relation to a SQL. After the creation of a non-clustered index, the data is not sorted.

Use Case Book Indexing

If we try to find a particular word say ‘coding’, you will first access the index, which is its own separate entity then the entire book. So it is using more disc space in the SQL because there are more pages.

Now as you'll head over to the index, check out the term ‘coding’, and take note of the pointer, which represents the page number.We can concluded that pages 11 and 13 include the word ‘coding’.

So, instead of going page by page, you would jump to page 11 and keep scrolling till The word ‘coding’ is located by you.

• Conclusion- Discovering the word  ‘coding’, by the utilization of an index, namely a non-clustered index  is much faster than rather of reading the book page by page , as a distinct entity from the data itself web page. Specially fi we have a book with one hundred of pages.

Advantage of Non clustered Index -

• Non Clustered index allows you to retrieve data extremely fast. Obviously it take extra space to store these index. 
• Also a single non customer index can include multiple columns at a time
• Can create multiple Non-Clustered Index on a table

Retrieve without creating any index on People table having 50 million records

In Above Row, you're going through/scan the each row by row in a table trying to find that word ‘Frankline600’ . It's going to have to go through the field 50 million rows one by one, trying to find all the data.

any time you update the table people with an insert update or delete the index on that column has to be updated

Lets try to do a search before creating any index 

It took around 5 seconds to get this record

Lets create a Non Cluster Index on People Table having 50 millions of records

it gook around 1.23 min to create this index. because it has to recreate that entire column and pointers.

Try to retrieve / search again

This time it took hardly a second of time to get this record