cluster Index

Hi @Vineet S,

Welcome to Microsoft Q&A forum.

As I understand it, when handling large amounts of data, indexing is key for quick data retrieval. Let me explain how clustered and non-clustered indexes work and how they can help you.

Clustered Index: A clustered index sorts and stores the data rows in the table based on the key values. In your case, if customerid is the unique key and you create a clustered index on it, the data will be physically sorted by customerid. This can be very efficient for range queries and retrieving data in a sorted order. However, since the data is physically sorted, there can only be one clustered index per table.

Advantages:

• Faster retrieval of data when querying by the indexed column.
• Efficient for range queries.

Disadvantages:

• Slower insert, update, and delete operations due to the need to maintain the physical order.
• Only one clustered index per table.

Non-Clustered Index: A non-clustered index, on the other hand, creates a separate structure that points to the data rows. This index contains the key values and a pointer to the actual data row. You can create multiple non-clustered indexes on a table, which can be very useful for queries that filter or sort by columns other than the primary key.

Advantages:

• Faster retrieval for specific queries that do not involve the clustered index column.
• Multiple non-clustered indexes can be created on a table.

Disadvantages:

• Additional storage space required for the index.
• Can slow down data modification operations due to the need to update the index.

Example Scenario

Given your sample data:

customerid - 1

product - grains

productcategory - rice

sales - 1000

If you frequently query by customerid, a clustered index on customerid would be beneficial. For queries that filter by product or productcategory, non-clustered indexes on these columns would improve performance.

In-Memory Operations:

In-memory operations benefit from indexes by reducing the amount of data that needs to be scanned. Clustered indexes help by keeping the data sorted, which is efficient for range scans. Non-clustered indexes help by providing quick lookups for specific queries, reducing the need to scan the entire table.

For more information, please refer the document: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver16&source=recommendations

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.