The Differences, Advantages and Disadvantages of Clustered vs. Non-Clusted Indexes in SQL Databases
An index is a method used by SQL driven databases such as Oracle, SQL Server and MySQL to quickly and efficiently located a record or records in a database table. Search Engines such as Google, Bing and DuckDuckGo to name three all use indices to search their large databases for user requests along with their ranking signals. There are two main groups of indexes or indices and they are :-
- Clustered
- Non-clustered
The advantage of using them is to speed up the search and access of data in a table. For a small table, the improvement can be negligible but for a large database table, the improvement can be impressive at times if used properly.
An index can be based on one or more fields. It should be noted that fields that are a few limitations on indexes, both clustered and non-clustered.
- A SQL Server index should not exceed 900 bytes
- An index may contain no more than 16 fields.
Only certain fields can be used in an index, for example, text field cannot be used. There is another type of index called Fulltext which we will be considered at a later date.
Clustered Index
Clustered Indexes are physically sorted in the database so that they can be accessed quicker which is their main advantage. The disadvantage is that clustered indexes are slower to write because they have to be physically stored together. A database table can only have one clustered index.
Created Clustered Index idx_Index on Persons(Town)
You need to specify clustered otherwise the SQL Server will assume it to be non-clustered. If you only have one index in a table, it should be a clustered index as it is retrieve efficient and then all the others are non-clustered.
Non-Clustered Index
These index type main advantage is that they are quicker to write to the database because they don't need to be physically stored in the database. You can as many non-clustered indexes per table as you like. The more indexes you do have on a table, the more work the DBMS has to do to maintain the indices.
Create NonClustered Index Idx_Index on Persons(Town)
As you can see, the only difference as far at the SQL developer is aware of is the extra word, NonClustered, everything else is similar. Nonclustered is an optional keyword because anything without the clustered keyword will be non-clustered. Internally to the database, the work is complicated but is hidden from our view.
Last Modified : June 2023