What are clustered and non-clustered indexes in SQL server?
With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.
With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.
It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
Writing to a table with a clustered index can be slower if there is a need to rearrange the data.
Clustered vs non-clustered index:
A clustered index means you are telling the database to store close values actually close to one another on the disk. This has the benefit of rapid scan / retrieval of records falling into some range of clustered index values.
For example, you have two tables, Customer and Order:
Customer ---------- ID Name Address Order ---------- ID CustomerID Price
If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the “CustomerID” column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.
P.S. The index on CustomerID will obviously be not unique, so you either need to add a second field to “uniquify” the index or let the database handle that for you but that’s another story.
Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.
In SQL Server, row-oriented storage both clustered and nonclustered indexes are organized as B trees.
The key difference between clustered indexes and non clustered indexes is that the leaf level of the clustered index is the table. This has two implications.
- The rows on the clustered index leaf pages always contain something for each of the (non-sparse) columns in the table (either the value or a pointer to the actual value).
- The clustered index is the primary copy of a table.
Non clustered indexes can also do point 1 by using the
INCLUDE clause (Since SQL Server 2005) to explicitly include all non-key columns but they are secondary representations and there is always another copy of the data around (the table itself).
CREATE TABLE T ( A INT, B INT, C INT, D INT ) CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B) CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A, B) INCLUDE (C, D)
The two indexes above will be nearly identical. With the upper-level index pages containing values for the key columns
A, B and the leaf level pages containing
A, B, C, D
There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
The above quote from SQL Server books online causes much confusion
In my opinion, it would be much better phrased as.
There can be only one clustered index per table because the leaf level rows of the clustered index are the table rows.
The book’s online quote is not incorrect but you should be clear that the “sorting” of both non clustered and clustered indices is logical, not physical. If you read the pages at leaf level by following the linked list and read the rows on the page in slot array order then you will read the index rows in sorted order but physically the pages may not be sorted. The commonly held belief that with a clustered index the rows are always stored physically on the disk in the same order as the index key is false.
This would be an absurd implementation. For example, if a row is inserted into the middle of a 4GB table SQL Server does not have to copy 2GB of data up in the file to make room for the newly inserted row.
Instead, a page split occurs. Each page at the leaf level of both clustered and non clustered indexes has the address (
File: Page) of the next and previous page in logical key order. These pages need not be either contiguous or in key order.
e.g. the linked page chain might be
1:2000 <-> 1:157 <-> 1:7053
When a page split happens a new page is allocated from anywhere in the filegroup (from either a mixed extent, for small tables or a non-empty uniform extent belonging to that object or a newly allocated uniform extent). This might not even be in the same file if the filegroup contains more than one.
The degree to which the logical order and contiguity differ from the idealized physical version is the degree of logical fragmentation.
In a newly created database with a single file, I ran the following.
CREATE TABLE T ( X TINYINT NOT NULL, Y CHAR(3000) NULL ); CREATE CLUSTERED INDEX ix ON T(X); GO --Insert 100 rows with values 1 - 100 in random order DECLARE @C1 AS CURSOR, @X AS INT SET @C1 = CURSOR FAST_FORWARD FOR SELECT number FROM master..spt_values WHERE type = 'P' AND number BETWEEN 1 AND 100 ORDER BY CRYPT_GEN_RANDOM(4) OPEN @C1; FETCH NEXT FROM @C1 INTO @X; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO T (X) VALUES (@X); FETCH NEXT FROM @C1 INTO @X; END
Then checked the page layout with
SELECT page_id, X, geometry::Point(page_id, X, 0).STBuffer(1) FROM T CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% ) ORDER BY page_id
The results were all over the place. The first row in key order (with value 1 – highlighted with an arrow below) was on nearly the last physical page.
Fragmentation can be reduced or removed by rebuilding or reorganizing an index to increase the correlation between logical order and physical order.
ALTER INDEX ix ON T REBUILD;
I got the following
If the table has no clustered index it is called a heap.
Non clustered indexes can be built on either a heap or a clustered index. They always contain a row locator back to the base table. In the case of a heap, this is a physical row identifier (rid) and consists of three components (File:Page: Slot). In the case of a Clustered index, the row locator is logical (the clustered index key).
For the latter case if the non clustered index already naturally includes the CI key column(s) either as NCI key columns or
INCLUDE-d columns then nothing is added. Otherwise, the missing CI key column(s) silently gets added to the NCI.
SQL Server always ensures that the key columns are unique for both types of indexes. The mechanism in which this is enforced for indexes not declared as unique differs between the two index types, however.
Clustered indexes get a
uniquifier added for any rows with key values that duplicate an existing row. This is just an ascending integer.
For non clustered indexes not declared as unique SQL Server silently adds the row locator into the non clustered index key. This applies to all rows, not just those that are actually duplicates.
I realize this is a very old question, but I thought I would offer an analogy to help illustrate the fine answers above.
If you walk into a public library, you will find that the books are all arranged in a particular order (most likely the Dewey Decimal System, or DDS). This corresponds to the “clustered index” of the books. If the DDS# for the book you want was
005.7565 F736s, you would start by locating the row of bookshelves that is labeled
001-099 or something like that. (This endcap sign at the end of the stack corresponds to an “intermediate node” in the index.) Eventually you would drill down to the specific shelf labelled
005.7450 - 005.7600, then you would scan until you found the book with the specified DDS#, and at that point you have found your book.
But if you didn’t come into the library with the DDS# of your book memorized, then you would need a second index to assist you. In the olden days you would find at the front of the library a wonderful bureau of drawers known as the “Card Catalog”. In it were thousands of 3×5 cards — one for each book, sorted in alphabetical order (by title, perhaps). This corresponds to the “non-clustered index”. These card catalogs were organized in a hierarchical structure, so that each drawer would be labeled with the range of cards it contained (
Ka - Kl, for example; i.e., the “intermediate node”). Once again, you would drill in until you found your book, but in this case, once you have found it (i.e, the “leaf node”), you don’t have the book itself, but just a card with an index number (the DDS#) with which you could find the actual book in the clustered index.
Of course, nothing would stop the librarian from photocopying all the cards and sorting them in a different order in a separate card catalog. (Typically there were at least two such catalogs: one sorted by author name, and one by title.) In principle, you could have as many of these “non-clustered” indexes as you want.
Find below some characteristics of clustered and non-clustered indexes:
- Clustered indexes are indexes that uniquely identify the rows in an SQL table.
- Every table can have exactly one clustered index.
- You can create a clustered index that covers more than one column. For example:
create Index index_name(col1, col2, col.....).
- By default, a column with a primary key already has a clustered index.
- Non-clustered indexes are like simple indexes. They are just used for fast retrieval of data. Not sure to have unique data.
Clustered Index vs non-clustered index in sql server:
A Clustered Index is basically a tree-organized table. Instead of storing the records in an unsorted Heap table space, the clustered index is actually B+Tree index having the Leaf Nodes, which are ordered by the clusters key column value, store the actual table records, as illustrated by the following diagram.
The Clustered Index is the default table structure in SQL Server and MySQL. While MySQL adds a hidden clusters index even if a table doesn’t have a Primary Key, SQL Server always builds a Clustered Index if a table has a Primary Key column. Otherwise, the SQL Server is stored as a Heap Table.
The Clustered Index can speed up queries that filter records by the clustered index key, like the usual CRUD statements. Since the records are located in the Leaf Nodes, there’s no additional lookup for extra column values when locating records by their Primary Key values.
For example, when executing the following SQL query on SQL Server:
SELECT PostId, Title FROM Post WHERE PostId = ?
You can see that the Execution Plan uses a Clustered Index Seek operation to locate the Leaf Node containing the
Post record, and there are only two logical reads required to scan the Clustered Index nodes:
|StmtText | |-------------------------------------------------------------------------------------| |SELECT PostId, Title FROM Post WHERE PostId = @P0 | | |--Clustered Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[PK_Post_Id]), | | SEEK:([high_performance_sql].[dbo].[Post].[PostID]=[@P0]) ORDERED FORWARD) | Table 'Post'. Scan count 0, logical reads 2, physical reads 0
Since the Clustered Index is usually built using the Primary Key column values, if you want to speed up queries that use some other column, then you’ll have to add a Secondary Non-Clustered Index.
The Secondary Index is going to store the Primary Key value in its Leaf Nodes, as illustrated by the following diagram:
So, if we create a Secondary Index on the
Title column of the
CREATE INDEX IDX_Post_Title on Post (Title)
And we execute the following SQL query:
SELECT PostId, Title FROM Post WHERE Title = ?
We can see that an Index Seek operation is used to locate the Leaf Node in the
IDX_Post_Title index that can provide the SQL query projection we are interested in:
|StmtText | |------------------------------------------------------------------------------| |SELECT PostId, Title FROM Post WHERE Title = @P0 | | |--Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[IDX_Post_Title]),| | SEEK:([high_performance_sql].[dbo].[Post].[Title]=[@P0]) ORDERED FORWARD)| Table 'Post'. Scan count 1, logical reads 2, physical reads 0
Since the associated
PostId Primary Key column value is stored in the
IDX_Post_Title Leaf Node, this query doesn’t need an extra lookup to locate the
Post row in the Clustered Index.
Hope you learned something from this post.
Follow Programming Articles for more!