- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference between clustered index and non-clustered index in SQL server
Index is a lookup table associated with actual table or view that is used by the database to improve the data retrieval performance timing. In index , keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. Index gets automatically created if primary key and unique constraint is defined on the table. There are two types of index −
- Clustered Index - Table is created with primary key constraints then database engine automatically create clustered index . In this data sort or store in the table or view based on their key and values.
- Non-Clustered Index - Table is created with UNIQUE constraints then database engine automatically create non-clustered index . A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
| Sr. No. | Key | Clustered Index | Non-Clustered Index |
|---|---|---|---|
1 | Basic | Its created on primary key | It can be created on any key |
2 | Ordering | Store data physically according to the order | It don’t impact the order |
3 | Number of index | Only one clustered index can be there in a table | There can be any number of non-clustered indexes in a table |
4 | Space | No extra space is required to store logical structure | Extra space is required to store logical structure |
5 | Performance | Data retrieval is faster than non-cluster index | Data update is faster than clustered index |
- Related Questions & Answers
- Difference Between Clustered and Non-clustered index
- What is NON CLUSTERED INDEX in DB2? Explain with the help of practical example
- Clustered Systems
- What is CLUSTERED INDEX in DB2? Explain with the help of practical example.
- Difference between MySQL and SQL Server
- Difference between Oracle and SQL Server
- Difference between Inverted Index and Forward Index
- Structure of a Clustered System
- Difference between ETL and ELT in SQL Server
- Use of SQL/MDX processor in SAP HANA Index server
- Difference between correlated and non-collreated subqueries in SQL
- Difference between Cost Performance Index (CPI) and Schedule Performance Index (SPI)
- Difference between Star schema and Snowflake schema in SQL Server
- Difference between SQL and PL/SQL
- Difference Between SQL and T-SQL