To comprehend both clustered and non-clustered indexes in SQL Server clearly, it is first necessary to grasp what a database index is. Let’s examine database indexes now.
DATABASE INDEXES
Database indexes are copies of your data that are arranged in a particular way in a table.
By wisely using indexes on database tables, database managers might potentially improve speed. By using indexes, you can accelerate the execution of queries on frequently used columns and increase the database’s overall processing speed. Additionally, we can state that an index is utilised to quickly and reliably retrieve the data. The many index kinds include:
SQL Server supports two main types of indexes:
Non-Clustered and Clustered. They are implemented using a B-tree structure, and the root node, which holds the index rows, is at the top of each index. These keep references to the subsequent level in the index tree. The lowest level of an index, known as the leaf level, might have numerous intermediary nodes, each of which contains pointers to the next level of the index.
CLUSTERED INDEX
Clustered indexes are an important component of the overall database design in SQL Server. They are frequently forgotten about, misinterpreted, or, if the database is small, viewed as insignificant. Because the leaf/lower level of the clustered index is made up of the actual data pages of the table, they dictate the logical order in which the table data is stored. Based on the clustered index key, a clustered index organises and saves the table or view’s data rows according to order.
We must carefully select the columns on which this index will be generated because only one clustered index is permitted per table. The key for a clustering index should be narrow because it identifies a row and is common to all non-clustered indexes, among other factors. It should also be unique because if a clustered index isn’t, SQL will add a secret “unifier” column to rows with duplicate values, making the index unique. The key columns in a clustered index shouldn’t change because if the key values for a row change, that row needs to be physically moved. When the inserted values for a clustered index key are always larger than the existing values, fragmentation is reduced.
Structure of Clustered Index
NON-CLUSTERED INDEXES
Indexes that are not clustered are kept separate from the table. They have a sorted list of references to the database table and are formed independently of the table itself. The number of non-clustered indexes that may be generated on a table in SQL Server 2005 and before was limited to 249, but in SQL Server 2008, that number has been expanded to 999. Non-clustered indexes, which you provide to “point” back to the data pages in the clustered index, are sortings of the columns rather than copies of the database. The clustered index you select is crucial since it affects all other indexes, which is why.
There are 2 modes for non-clustered indexes: Non-Unique and Unique.
The choice of acceptable columns for non-clustered indexes is one of the most challenging tasks database managers must complete. Any columns that are often used in the WHERE clauses of SQL queries should be given non-clustered index consideration. Columns referred to by JOIN and GROUP BY operations are also excellent possibilities.
Structure of Non-Clustered Index
Other indexes with a brief description are:
Unique Indexes
Every row in the table or view is unique in some fashion thanks to a unique index, which guarantees that the index key does not include any duplicate entries. Uniqueness is possible for both clustered and non-clustered indices.
Index with included columns
a non-clustered index that has nonkey columns added on top of the key columns.
Spatial Index
With the aid of a spatial index, some actions on spatial objects (spatial data) in a column of the geometry data type can be carried out more quickly. The number of objects on which relatively expensive spatial procedures must be used is decreased by the spatial index.
Filtered Index
a non-clustered index that has been tuned in particular for covering queries that select from a clearly defined subset of data. It employs a filter predicate to index a subset of the table’s rows. When compared to full-table indexes, a well-designed filtered index can enhance query performance, lower index maintenance expenses, and lower index storage costs.
XML
an image of the XML binary large objects (BLOBs) that has been shred and preserved in the XML data type column.
Full-text
The Microsoft Full-Text Engine for SQL Server creates and maintains a specific kind of token-based functional index. In character string data, it effectively supports advanced word searches.
Thanks
