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 in Database by Neha
• 6,260 points
17 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 by Vaani
• 5,040 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,360 points
8,193 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,360 points
182 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
621 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
589 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,360 points
834 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
304 views
0 votes
1 answer

Different Types Of Index

There are three types of index namely: Unique ...READ MORE

answered Oct 11, 2018 in Database by DataKing99
• 8,240 points
552 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
280 views
0 votes
1 answer

What is an index in SQL?

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

answered Feb 3 in Database by Vaani
• 5,040 points
29 views
0 votes
1 answer

What are DDL and DML?

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

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