SQL: Story of Indexes

sql-index
sql-index

Image courtesy of Grant Cochrane / FreeDigitalPhotos.net

SQL: Story of Indexes

Welcome to CodeSpread!

I know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge.

My first thought about Indexes is that ‘It is a solution’. I assume that you will ask me ‘What was the problem behind it?’

The problem lies in the way our regular data is stored. We know that data is stored on the heap.

What is the Heap?

A heap file is an unordered set of records where Records can be inserted and deleted. Also, Records are uniquely identified by a record-id. We can use this record-id to retrieve the specific data.

Heap file supports sequential scan of records. We can run our SQL query to retrieve the data but efficiency will always be on the lower side.

If we make some explicit correction and introduce some kind of order to this unordered set of data, we can reach a certain level of efficiency.

What do we need to do?

We need to locate a column based on some specific requirements and used it as a reference to retrieve the entire row of data. When we fire a SQL query to search for a row of data by providing a conditional value, SQL Server first finds that value in this special column, and retrieve the corresponding entire row of data.This column is normally called as Index.

What are the specific requirements?

Indexes can be created on most columns in a table except if the data-type is large object (LOB) data types, such as image, text, and varchar(max). It may also uniquely identify the row. if not, the output result might contain more that one row.

How Index-Seek happens?

We need some knowledge of B-tree architecture to understand Index-seek.

btree

Indexed column values are arranged as per leaf nodes shown above. Each of the group is referenced as pages and also pointed as index level pages.  When a SQL query is run having some conditional value on index. The query engine starts from the root level and follows down till the leaf node, narrowing down the result set. Once query engine reaches the index level, it identifies and retrieves the exact result set expected. For example, let’s search for a value 60 in indexed column, the query engine starts from the root level and traverse down towards the right side and finally reaches the leaf node level. Here, the query engine identifies the value 60.

This operation is called index-seek where instead of going for sequential scan of the whole table, a B-tree traversal is done to seek the value on an indexed column.

Next in line:

1) Clustered Index and Non-Clustered Index

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • SQL:Target a TriggerSQL:Target a TriggerI remember those days when I used to simply fall into the trap set by client requirements which could have been easily resolved by using a trigger. So I can definitely say, trigger is a life saver,...
  • SQL : Transactions and ACID properties.SQL : Transactions and ACID properties.There are always two point of views of transactions. First, the user point of view, for example, customer went to an ATM to retrieve some amount. For him, the transaction might look like, Fo...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • SQL:Target a SubQuerySQL:Target a SubQueryPeople ask me a lot about subquery so I thought let’s answer with a article touching a bit of everything about subquery. Before discussing SubQuery, there are few things which we should know lik...
  • VB.Net: Download file from FTP and UnZipVB.Net: Download file from FTP and UnZipExperience: Many application requires data migration tasks to execute on daily basis. To achieve this we generally seek help of SSIS package, if at all we are making use of SQL sever. ...