--- description: What is an Index? An Index is a key built from one or more columns in the database that speeds up fetching rows from the table or view. This key helps a Database like Oracle, SQL Server, MySQL, etc. t title: Difference between Clustered and Non-clustered index image: https://www.guru99.com/images/1/051019_0547_Clusteredvs4.png --- [Skip to content](#main) ## Key Difference between Clustered and Non-clustered Index * A cluster index is a type of index that sorts the data rows in the table on their key values, whereas the Non-clustered index stores the data at one location and indices at another location. * Clustered index stores data pages in the leaf nodes of the index, while the Non-clustered index method never stores data pages in the leaf nodes of the index. * The cluster index doesn’t require additional disk space, whereas the Non-clustered index requires additional disk space. * Cluster index offers faster data access, on the other hand, the Non-clustered index is slower. [![Difference between Clustered and Non-clustered Index](https://www.guru99.com/images/1/051019_0547_Clusteredvs4.png)](https://www.guru99.com/images/1/051019%5F0547%5FClusteredvs4.png) Clustered vs Non-clustered Index ## What is an Index? An Index is a key built from one or more columns in the database that speeds up fetching rows from the table or view. This key helps a Database like Oracle, SQL Server, MySQL, etc. to find the row associated with key values quickly. Two types of Indexes are: * Clustered Index * Non-Clustered Index ## What is a Clustered index? Cluster index is a type of index which sorts the data rows in the table on their key values. In the Database, there is only one clustered index per table. A clustered index defines the order in which data is stored in the table which can be sorted in only one way. So, there can be an only a single clustered index for every table. In an RDBMS, usually, the primary key allows you to create a clustered index based on that specific column. ## What is Non-clustered index? A Non-clustered index stores the data at one location and indices at another location. The index contains pointers to the location of that data. A single table can have many non-clustered indexes as an index in the non-clustered index is stored in different places. For example, a book can have more than one index, one at the beginning which displays the contents of a book unit wise while the second index shows the index of terms in alphabetical order. A non-clustering index is defined in the non-ordering field of the table. This type of indexing method helps you to improve the performance of queries that use keys which are not assigned as a primary key. A non-clustered index allows you to add a unique key for a table. ### RELATED ARTICLES * [DBMS Architecture: 1-Tier, 2-Tier & 3-Tier ](https://www.guru99.com/dbms-architecture.html "DBMS Architecture: 1-Tier, 2-Tier & 3-Tier") * [Indexing in DBMS: What is, Types of Indexes with EXAMPLES ](https://www.guru99.com/indexing-in-database.html "Indexing in DBMS: What is, Types of Indexes with EXAMPLES") * [Difference Between File System and DBMS ](https://www.guru99.com/difference-between-file-system-and-dbms.html "Difference Between File System and DBMS") * [Row vs Column – Difference Between Them ](https://www.guru99.com/difference-between-rows-and-columns.html "Row vs Column – Difference Between Them") ## Characteristic of Clustered Index * Default and sorted data storage * Use just one or more than one columns for an index * Helps you to store Data and index together * Fragmentation * Operations * Clustered index scan and index seek * Key Lookup ## Characteristics of Non-clustered Indexes * Store key values only * Pointers to Heap/Clustered Index rows * Allows Secondary data access * Bridge to the data * Operations of Index Scan and Index Seek * You can create a nonclustered index for a table or view * Every index row in the nonclustered index stores the nonclustered key value and a row locator ## Clustered vs Non-clustered Index in SQL: Key Differences | Parameters | Clustered | Non-clustered | | --------------------- | ----------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------- | | Use for | You can sort the records and store clustered index physically in memory as per the order. | A non-clustered index helps you to creates a logical order for data rows and uses pointers for physical data files. | | Storing method | Allows you to stores data pages in the leaf nodes of the index. | This indexing method never stores data pages in the leaf nodes of the index. | | Size | The size of the clustered index is quite large. | The size of the non-clustered index is small compared to the clustered index. | | Data accessing | Faster | Slower compared to the clustered index | | Additional disk space | Not Required | Required to store the index separately | | Type of key | By Default Primary Keys Of The Table is a Clustered Index. | It can be used with unique constraint on the table which acts as a composite key. | | Main feature | A clustered index can improve the performance of data retrieval. | It should be created on columns which are used in joins. | ## An example of a clustered index In the example below, SalesOrderDetailID is the clustered index. Sample query to retrieve data SELECT CarrierTrackingNumber, UnitPrice FROM SalesData WHERE SalesOrderDetailID = 6 [](https://www.guru99.com/images/1/051019%5F0547%5FClusteredvs1.png) ## An example of a non-clustered index In the below example, a non-clusted index is created on OrderQty and ProductID as follows CREATE INDEX myIndex ON SalesData (ProductID, OrderQty) [](https://www.guru99.com/images/1/051019%5F0547%5FClusteredvs2.png) The following query will be retrieved faster compared to the clustered index. SELECT Product ID, OrderQty FROM SalesData WHERE ProductID = 714 [](https://www.guru99.com/images/1/051019%5F0547%5FClusteredvs3.png) ## Advantages of Clustered Index The pros/benefits of the clustered index are: * Clustered indexes are an ideal option for range or group by with max, min, count type queries * In this type of index, a search can go straight to a specific point in data so that you can keep reading sequentially from there. * Clustered index method uses location mechanism to locate index entry at the start of a range. * It is an effective method for range searches when a range of search key values is requested. * Helps you to minimize page transfers and maximize the cache hits. ## Advantages of Non-clustered index Pros of using non-clustered index are: * A non-clustering index helps you to retrieves data quickly from the database table. * Helps you to avoid the overhead cost associated with the clustered index * A table may have multiple non-clustered indexes in [RDBMS](https://www.guru99.com/relational-data-model-dbms.html). So, it can be used to create more than one index. ## Disadvantages of Clustered Index Here, are cons/drawbacks of using clustered index: * Lots of inserts in non-sequential order * A clustered index creates lots of constant page splits, which includes data page as well as index pages. * Extra work for [SQL](https://www.guru99.com/what-is-sql.html) for inserts, updates, and deletes. * A clustered index takes longer time to update records when the fields in the clustered index are changed. * The leaf nodes mostly contain data pages in the clustered index. ## Disadvantages of Non-clustered index Here, are cons/drawbacks of using non-clustered index: * A non-clustered index helps you to stores data in a logical order but does not allow to sort data rows physically. * Lookup process on non-clustered index becomes costly. * Every time the clustering key is updated, a corresponding update is required on the non-clustered index as it stores the clustering key. #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/clustered-vs-non-clustered-index.png","url":"https://www.guru99.com/images/clustered-vs-non-clustered-index.png","width":"601","height":"168","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/clustered-vs-non-clustered-index.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/dbms","name":"DBMS"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/clustered-vs-non-clustered-index.html","name":"Difference between Clustered and Non-clustered index"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/clustered-vs-non-clustered-index.html#webpage","url":"https://www.guru99.com/clustered-vs-non-clustered-index.html","name":"Difference between Clustered and Non-clustered index","dateModified":"2024-06-28T18:36:51+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/clustered-vs-non-clustered-index.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/clustered-vs-non-clustered-index.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown","description":"I'm Fiona brown, a Full Stack Developer with over a decade of experience, sharing practical guides on robust and scalable application development.","url":"https://www.guru99.com/author/fiona","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/fiona-brown-author.png","url":"https://www.guru99.com/images/fiona-brown-author.png","caption":"Fiona Brown","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"@type":"NewsArticle","headline":"Difference between Clustered and Non-clustered index","keywords":"dbms, sql","dateModified":"2024-06-28T18:36:51+05:30","articleSection":"DBMS","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"publisher":{"@id":"https://www.guru99.com/#organization"},"description":"What is an Index? An Index is a key built from one or more columns in the database that speeds up fetching rows from the table or view. This key helps a Database like Oracle, SQL Server, MySQL, etc. t","copyrightYear":"2024","copyrightHolder":{"@id":"https://www.guru99.com/#organization"},"name":"Difference between Clustered and Non-clustered index","@id":"https://www.guru99.com/clustered-vs-non-clustered-index.html#richSnippet","isPartOf":{"@id":"https://www.guru99.com/clustered-vs-non-clustered-index.html#webpage"},"image":{"@id":"https://www.guru99.com/images/clustered-vs-non-clustered-index.png"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/clustered-vs-non-clustered-index.html#webpage"}}]} ```