What do you mean by Clustered and Non-Clustered index

0 votes
I have limited knowledge in DB, can someone explain in simpler words how clustered and non-clustered index works?
Feb 10, 2022 in Database by Neha
• 9,060 points
265 views

1 answer to this question.

0 votes

The rows in a clustered index are physically stored on the disc in the same order as the index. As a result, only one clustered index is possible.

There is a second list with references to the physical rows in a non clustered index. You can have a lot of non-clustered indices, but each one increases the amount of time it takes to create new entries.

If you wish to get all of the columns, it's usually faster to read from a clustered index. It is not necessary to go to the index first and then to the table.

When you create a clustered index, you're directing the database to store values that are physically close to one another on the disc. This has the advantage of allowing for a quick search and retrieval of data that fall inside a certain range of clustered index values.

For example, let us take two tables: Customer and Order:

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

Create a clustered index on the "CustomerID" column of the Order table if you want to easily obtain all orders for a certain customer. This method, entries with the same CustomerID are physically stored near one other on storage (clustered), making retrieval faster.

P.S. Because the index on CustomerID is obviously not unique, you'll need to either add a second column to "uniquify" the index or let the database handle it for you, but that's an other issue.

Concerning the use of multiple indices, because this dictates how the data is physically structured, each table can only have one clustered index. Imagine a large room with several tables if you want an analogy.

answered Feb 10, 2022 by Vaani
• 7,020 points

Related Questions In Database

0 votes
1 answer

What do ‘Record’, ‘Field’ and ‘Table’ mean in terms of a database?

You can refer to the following definitions ...READ MORE

answered Nov 12, 2018 in Database by Sahiti
• 6,370 points
20,455 views
0 votes
1 answer

What do you understand by Database Triggers?

A set of commands that automatically get ...READ MORE

answered Nov 14, 2018 in Database by Sahiti
• 6,370 points
453 views
0 votes
1 answer

Can you explain what is the difference between UNION and Join?

Hi Sahana,  I got your doubt, Checkout these ...READ MORE

answered May 30, 2019 in Database by sampriti
• 1,120 points
1,094 views
0 votes
1 answer

What is the rule to use group by, having and where clause?

Hi samar, this is a very common mistake ...READ MORE

answered Jul 2, 2019 in Database by anonymous
1,050 views
0 votes
1 answer

Difference between clustered and non clustered index in SQL

The differences between the clustered and non ...READ MORE

answered Sep 28, 2018 in Database by Sahiti
• 6,370 points
1,478 views
0 votes
0 answers

What are Covering Indexes and Covered Queries in SQL Server?

Can you describe the ideas behind Covering ...READ MORE

Aug 21, 2022 in Database by Kithuzzz
• 38,010 points
271 views
0 votes
1 answer

How do I obtain a Query Execution Plan in SQL Server?

There are several ways to get an ...READ MORE

answered Sep 20, 2022 in Database by narikkadan
• 63,420 points
813 views
0 votes
1 answer

What is a covered Index?

A covering index is an index that contains all ...READ MORE

answered Oct 10, 2018 in Database by Frankie
• 9,830 points
570 views
0 votes
1 answer

What is an index in SQL?

An index is used to speed up ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,020 points
393 views
0 votes
1 answer

What are DDL and DML?

SQL Commands have subcategories like DDL, DML, DCL ...READ MORE

answered Feb 8, 2022 in Database by Vaani
• 7,020 points
642 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP