SQL SERVER clustered index non-clustered index difference

Transfer from http://blog.csdn.net/single_wolf_wolf/article/details/52915862

I. Understanding the structure of the index

Indexes work in databases similar to the role of catalogs in books to improve the speed of finding information. Use an index to find data without scanning the entire table to quickly find the data you need. Microsoft's SQL Server provides two types of indexes: Clustered indexes (clustered index, also called clustered indexes, clustered indexes), and nonclustered indexes (nonclustered index, also called nonclustered indexes, non-clustered indexes).

The basic unit of data storage in SQL Server is the page. The disk space allocated by the data file (. mdf or. ndf) in the database can be logically divided into pages (numbered from 0 to n consecutively). Disk I/O operations are performed at the page level. That is, the smallest unit of data that SQL Server reads or writes to data every time is the data page.

Let's take a quick look at the architecture of the index:

　　1. Clustered index Structure

In SQL Server, indexes are organized by the B-tree structure.

Clustered index structure in a single partition:

--Build useradddate clustered index create CLUSTERED index [ix_adddate] on [User] ([adddate] ASC)

Clustered indexes (Clustered index) features

• The leaf node of a clustered index is the actual data page
• The sort order in a clustered index simply means that the data page chain is logically ordered. Instead of physically stored on disk in order
• The physical position of the row is the same as the row position in the index
• There can be only one clustered index per table
• The average size of a clustered index is about 5% of the size of the table

　　2. Nonclustered index structure

Nonclustered indexes have the same B-tree structure as clustered indexes, and the significant difference between them is the following two points:

1. The data rows of the underlying table are not sorted and stored in the order of the nonclustered keys.

2. The leaf layer of a nonclustered index is made up of index pages rather than data pages.

Illustrates the nonclustered index structure in a single partition:

Index of the containing column

You can extend the functionality of a nonclustered index by adding a containing column (called a nonkey column) to the leaf level of the index. Key columns are stored at all levels of the nonclustered index, and non-key columns are stored only at the leaf level.

Here's a simple example to illustrate the difference between clustered and nonclustered indexes:

We have a Chinese dictionary that can take its body as a clustered index, sorted alphabetically by the beginning of the pinyin, and no longer need to look for other directories. When you encounter a word that you do not know, you need to combine the "radicals" and "gept tables" to find the results in the table of contents and then turn to the page numbers you need. In this way, the sorting of the words in the directory is not the actual sorting method of the body. Consider this as a nonclustered index.

Also, be aware that there can be only one clustered index per table.

--Build useradddate nonclustered index create nonclustered index [ix_adddate] on [User] ([adddate] ASC)

Nonclustered indexes (unclustered index) features

• A non-clustered index of a page, not a data, but a page that points to a data page.
• If an index type is not specified, the default is a nonclustered index.
• The order of leaf node pages is different from the physical storage order of the tables
• You can have up to 249 nonclustered indexes per table
• Create a clustered index before the nonclustered index is created (otherwise, an index rebuild is thrown)

　　Second, choose which kind of index to establish

　　1. When to create a clustered index improves performance

Clustered index will improve the performance of most table, especially if it meets the following conditions:

Unique, narrow, sustained growth, preferably only upward increase. For example:

• Identity
• Date, identity
• GUID (only if using Newsequentialid () function)

2. Non-clustered index method for high performance

Nonclustered indexes because the nodes of the B-tree are not specific data pages, sometimes, for this reason, nonclustered indexes are not even as fast as the history of the tables. However, the nonclustered index has an attribute, if you want to query the content, in the nonclustered index and is overwritten, you do not need to continue to the clustered index, or RID (row identifier in the heap structure) to find data, this time can greatly improve performance, this is the problem of coverage (covering).

Because the clustered index leaf nodes are specific data, the coverage of the clustered index is 100%, and the problem of improving performance by increasing coverage is only possible with nonclustered indexes (nonclustered Indexes).

When all the columns in the query are included on index, we say this index covers the query. The order of the columns is not important here (the order of the Select time is not important, but the order of index establishment can be taken care of).

In SQL Server 2005, to increase the benefits of this covering, you can even extend the functionality of nonclustered indexes by adding non-key columns to the leaf level of a nonclustered index.

Add: Nonclustered indexes have an advantage only if the query is highly selective.

　　III. scenarios using clustered or nonclustered indexes (note: Priority is recommended, should, should not be)

Iv. Comparison of primary keys and clustered indexes

Here are some examples of how the test is used in the public reviews Web:

　　V. The cost of using the index

• Indexes need to occupy physical storage space outside the data table
• Creating indexes and maintaining indexes takes a certain amount of time
• When the table is updated, the index needs to be rebuilt, which reduces the maintenance speed of the data.

Reference: http://kb.cnblogs.com/page/144409/

SQL SERVER clustered index non-clustered index difference