Clustered And Non Clustered Index In SQL

This blog will clear your understanding on how the Clustered and Non-Clustered index works when we are searching data from SQL.

When you execute a command like ‘select * from tblAccounts’ (without specifying any specific columns), it will fetch data using Clustered Index seek. When you execute a command like ‘select intAccountID from tblAccounts’ (with the specific columns), it will fetch data using the very first non-clustered index available for that table.

 

Example

 

I have one table named ‘tblAccounts’ which is having ‘intAccountID’ column as a PK-Clustered Index and few non-clustered indexes among which the very first non-clustered index(FIFO) is ‘bmain’ column which is having values like 0 & 1.

 

Let's see the data fetching scenario,

select TOP 10 *from tblAccounts;

It will give me TOP 10 (intAccountID = 1 to 10) records.

 

It will fetch data using a clustered index.

 

That's why,

 

 

select TOP 10 intAccountID from tblAccounts;

It will give me records of intAccountID 2 to 11. Why???

 

Because It is going to fetch records using the very first available non-clustered index. Which is for bmain column and for the record of int AccountID = 1, the value of bmain is 1. And for the int AccountID 2-15 the value of bmain is 0. It will seek the data in the ASC order of the non-clustered index.

 

 

Now, suppose you need to have min, max, TOP int AccountID from the TOP 10 accounts, then how can you fetch it?

select MIN(intAccountID), max(intAccountID) from (select TOP 10 *from tblAccounts) C;

As seen in 2^nd option, it will display MIN accountID = 2 and Max accountID = 11.

 

So how can we be assured that we will get the correct result in our query?

 

The answer is that we have to execute the query with order by int AccountID ASC.

select MIN(intAccountID), max(intAccountID) from (select TOP 10 *from tblAccounts orderby 1 asc) C;

Using order by we will get the MIN accountID = 1 and Max accountID = 10. Because it will seek data using a clustered index. And clustered index itself is explanatory in that it arranges data in an identical, well-sequenced format.

 

 

 

So, while you are writing any SP or function in which you have to fetch TOP, MIN, MAX kind of data and you want to be assured that correct data should be fetched, then you can consider such scenarios.