clustered_vs_nonclustered_index.txt (1383B)
What is the difference between clustered and non-clustered index? # Clustered Index Definition: A clustered index determines the physical order of data in a table. The rows of the table are stored in the same order as the clustered index. Properties: -> Only one per table. Each table can only support one clustered index, since the rows of the table can only be stored in one order. -> Often it is the PK. When a Primary Key is defined, it's created as a clustered index. -> The data and the index are stored together. -> Help speed up queries that inspect a range of values e.g. BETWEEN, ORDER BY. -> Slows down INSERT/DELETE/UPDATE queries, since these operations can cause a re-ordering of rows. # Non-Clustered Index Definition: A non-clustered index creates a structure that is separate from the table data. This structure contains pointers (row locators) to the data's physical location. Properties: -> You can have as many as you want. Since this is a separate structure, you can attach as many as you want. -> Does not affect the physical order of rows in the table. -> Less helpful for range queries or sorting than a clustered index. Still very helpful for speeding up WHERE/JOINs in queries. -> Takes up additional space to store the structure of the index. More non-clustered indexes = more space used. -> Lower impact on INSERT/DELETE/UPDATE queries.