Last updated on 13th Jul 2020| 1303
(5.0) | 16935 Ratings
E-mail this post
There are two main index types: Clustered index and Non-Clustered index.
A clustered index alters the way that the rows are physically stored. When you create a clustered index on a column (or a number of columns), the SQL server sorts the table’s rows by that column(s).
It is like a dictionary, where all words are sorted in an alphabetical order. Note, that only one clustered index can be created per table. It alters the way the table is physically stored, it couldn’t be otherwise.
In the example below, all rows are sorted by computer_id, as a clustered index on the computer_id column has been created.
ON [dbo].[nics] ([computer_id] ASC)
A non-clustered index, on the other hand, does not alter the way the rows are stored in the table. Instead, it creates a completely different object within the table, that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data.
It is like an index in the last pages of a book. All keywords are sorted and contain a reference back to the appropriate page number. A non-clustered index on the computer_id column, in the previous example, would look like the table below:
CREATE NONCLUSTERED INDEX [IX_NONCLUSTERED_COMPUTER_ID]
ON [dbo].[nics] ([computer_id] ASC)
| Computer_id | Row Locator |
|---|---|
| 5000025 | 234 |
| 5000031 | 345 |
| 5000045 | 112 |
| 5000046 | 348 |
| 5000055 | 234 |
| 5000059 | 984 |
Note, that SQL server sorts the indexes efficiently by using a B-tree structure. This is a tree data structure that allows SQL Server to keep data sorted, to allow searches, sequential access, insertions and deletions, in a logarithmic amortized time. This methodology minimizes the number of pages accessed, in order to locate the desired index key, therefore resulting in an improved performance.
SQL Server Index Maintenance
As great as indexes can be when it comes to improving the performance of your database, they do require some upkeep to keep them performing optimally. Over time as INSERTs, UPDATEs and DELETEs are executed against your table the indexes associated with each table can become fragmented. There are many factors that determine how quickly an index will become fragmented but luckily, we have some tools built into SQL Server that will show us exactly how fragmented an index has become. The sys.dm_db_index_physical_stats dynamic management view will give you size and fragmentation statistics on each of your indexes. The below query can be used as a starting point to list the fragmentation of your indexes. Note the last parameter, SAMPLED. This parameter determines the scan level when collecting the index statistics. I usually use SAMPLED as most of the systems I work on are quite large but there are other options that get more or less detailed statistics depending on what you require. DEFAULT, NULL, LIMITED, DETAILED are the other options.
SELECT t.name AS TableName,i.name AS IndexName,
ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘SAMPLED’) ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY avg_fragmentation_in_percent DESC;
Once you have determined the level of fragmentation in your indexes you have two different options available in order to address the fragmentation. You can either REORGANIZE the index or REBUILD it. Guidelines from Microsoft suggest that if the level of fragmentation is < 10% then you should do nothing, 10% to 30% you should REORG and > 30% you should REBUILD. These numbers were chosen based on the time it takes to do each operation. A REBUILD creates the new index from scratch where as a REORG just works to remove the fragmentation. An index with little fragmentation can be REORGed rather quickly so once the fragmentation is above 30% the index can usually be rebuilt in the same amount of time it would take to REORG so it makes sense to just REBUILD it. There are some other things to consider when deciding which option to choose. These are listed below.
| REBUILD | REORG |
| requires enough free space to create new index | works in place so no extra space required |
| if interrupted need to restart from scratch | can restart from where operation was interrupted |
| can be done online or offline | always done online |
| generates more trn log than reorg | only generates trn log for blocks that were reorged |
| statistics are updated automatically | statistics update must be done manually |
SQL queries on clustered and non-clustered Indexes
Enhance Your Career with SQL Training By MNC Experts
- Instructor-led Sessions
- Real-life Case Studies
- Assignments
Prerequisite – Indexing in Databases
Indexing is a procedure that returns your requested data faster from the defined table. Without indexing, the SQL server has to scan the whole table for your data. By indexing, SQL server do the exact same thing when you searched for a content in a book by checking the index page. In the same way table’s index allows us to locate the exact data without scanning the whole table. There are two types of indexing in SQL.
- Clustered index
- Non-clustered index
Clustered –
A clustered index is teh type of indexing that established a physical sorting order of rows. Suppose you have a table Student_info which contains ROLL_NO as a primary key TEMPthan Clustered index which is self-created on that primary key will sort the Student_info table as per ROLL_NO. A clustered index is like Dictionary, in teh dictionary sorting order is alphabetical there is no separate index page.
Examples:
Input:
- CREATE TABLE Student_info
- (
- ROLL_NO int(10) primary key,
- NAME varchar(20),
- DEPARTMENT varchar(20),
- );
- insert into Student_info values(1410110405, ‘H Agarwal’, ‘CSE’)
- insert into Student_info values(1410110404, ‘S Samadder’, ‘CSE’)
- insert into Student_info values(1410110403, ‘MD Irfan’, ‘CSE’)
- SELECT * FROM Student_info
- SELECT * FROM Student_info
Output:
| ROLL_NO | NAME | DEPARTMENT |
| 1410110403 | MD Irfan | CSE |
| 1410110404 | S Samadder | CSE |
| 1410110405 | H Agarwal | CSE |
If we want to create Clustered index on other column then first we have to remove the primary key after that we can remove the previous index.
Note that defining a column as a primary key makes that column the Clustered Index of that table. To make any other column, clustered index first we have to remove the previous one as follows below procedure.
Syntax:
//Drop index
drop index table_name.index_name
//Create Clustered index index
create Clustered index IX_table_name_column_name
on table_name (column_name ASC)
Note: We can create only one clustered index in a table.
Explain the types of indexes.
1) Clustered index
2) Non-clustered
Clustered index
– Clustered index exists as sorted row on disk.
– Clustered index re-orders the table record.
– Clustered index contains record in the leaf level of the B-tree.
– There can be only one Clustered index possible in a table.
– Non-clustered index is the index in which logical order doesn’t match with physical order of stored data on disk.
– Non-clustered index contains index key to the table records in the leaf level.
– There can be one or more Non-clustered indexes in a table.
