Clustered vs non clustered index

(0) Share

Report

Posted on by Dev

435

Hi All,

Clustered index are created for performance reason and data is stored in physical order in table but I am not sure how is physical order will make the search quicker, can you please also help me know and also in what scenarios are clustered index useful and in what non clustered.

Thanks.

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

ergun sahin 8,816 Moderator on at

Like (0)

Report

RE: Clustered vs non clustered index

Clustered index stores data pages in the leaf nodes of the index. Therefore, your records must be uniq (not null). If you pay attention, usually the primary key is used as the cluster index.

www.guru99.com/clustered-vs-non-clustered-index.html

In general, you can think of indexes as:

You can directly know the location of the file in the cluster index.

In non cluster, you have the index file, it tells you where the file of the relevant student is. You go one by one and bring them from there (not quite right, but there's no harm in thinking about it that way).

If you don't have any indexes, you should extract all the files and sort them according to your needs.

I focused more on the physical benefit of the index in the examples. Technically, it's a little different, of course.

Was this reply helpful? Yes No
Dev 435 on at

Like (0)

Report

RE: Clustered vs non clustered index

Thanks Ergün. Sorry for the trouble but I still have some doubts.

When a query is issued against a column on which the non clustered index is created, the database will first go to the index and look for the address of the corresponding row in the table and then go to that row address and fetch other column values.

For clustered index how is sorting going to help in retrieval. If suppose we have put the non clustered index on the best possible column which is Group Id and in each group we can have max of 4 students with unique roll numbers, if we query by groupId how is clustered index going to be helpful. Is there any concept of paging involved here.

Was this reply helpful? Yes No
Suggested answer

ergun sahin 8,816 Moderator on at

Like (0)

Report

RE: Clustered vs non clustered index

If we had put the files in the letter-ordered lockers from the very beginning, we would know that the files of the students starting with the letter A were in the 1st lockers in the 1st room. It would be much easier to find. (maybe 2th or 3th locker but we now room)

But if it is class-based, we will have to remove all the files from the lockers, sort them again and use them that way.

You have the right to create one cluster and it is useful to create it according to the query you use most frequently to get maximum efficiency.

But as I mentioned in the first example, there are extra situations that you should pay attention to.

If you're sorting by name and half the school is Tom, it means you haven't created a cluster index for half the records. In order to get efficiency from the cluster, it should be distributed more evenly.

Or, if the fields you select in the index change very often, the update process will be slow as it has to be physically relocated each time.

Was this reply helpful? Yes No
Dev 435 on at

Like (0)

Report

RE: Clustered vs non clustered index

Thanks Ergün. In your example where we sort by name for vaccine is what we will achieve with clustered index then how is it going to help is what I am not understanding.

If you can please provide an example of use of clustered and non clustered index like the above it will be really helpful. Thanks.

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 298,286 Super User 2025 Season 2 on at

Like (0)

Report

RE: Clustered vs non clustered index

HI AX Dev,

There are various blog posts available on the internet with some explanation. Like this one: www.sqlshack.com/.../

You have to think of how many times a certain order is required. If you look at the Global address book, the DirPartyTable has a Party-ID. The form usually shows the records in alphabetical order on the name. If reads from this table are commonly on the Name, then it would make sense to have the clustered index on this table. You can use SQL monitoring tools to check the queries on a table.

Was this reply helpful? Yes No
Suggested answer

ergun sahin 8,816 Moderator on at

Like (0)

Report

RE: Clustered vs non clustered index

Think of a school. There are students. Physically, each student should have a file. Imagine grouping it by class and putting it in a locker . When the exam results of the class are announced, it will be very easy for you to find the relevant locker and remove the records.

But suppose the school has a duty to check students' vaccinations. You need to find the files in alphabetical order and check the records. This time, class-based grouping didn't help at all, it even made it difficult.

Or you sorted by name, but half of the kids at the school are named Tom.

Or you grouped them according to the age of the children, but every day someone's age changes.

In these examples, it means that you did not use the cluster correctly. You have only one cluster right in each table and you need to sort with the index that will speed you up the most.

Was this reply helpful? Yes No

Community site session details

Clustered vs non clustered index

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Clustered vs non clustered index

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Andrés Arias – Community Spotlight

Congratulations to the August Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates