Difference between index clustered and nonclustered in ms SQL Server

Document directory

• Data Source

I really don't know what the difference is. Isn't it all an index? Later, I learned it only after reading msdn for a moment. I first explained it with my own meaning, attach the original text.

 

Clustered: the data and the index are stored together. Each table can only have one item. The data will be sorted too quickly.

 

Nonclustered: the data and indexes are separated and put. Just like the C ++ index, the index stores the data location, and the data may not be sorted.

 

Replica SET Index Structure

In SQL Server, the index structure is B-type category. Each score in index B category is an index metric. In type B, the primary node is the root node. At the bottom of the index, the index is divided into two vertices. Any index between the root and the shard points can be regarded as a medium weight. In the dataset index, the shard points contain the data records of the basic data table. The root and middle-distance indexes contain the indexes that store the index data column. Each index item column contains the index item value and the index item column that points to the dynamic segmentation or index segmentation indexes in the B-type category. The shards in each batch are indexed using the forward-to-backward join string.

 

Non-replica SET Index Structure

The non-replica SET index has the same B-type replica SET index structure as the replica SET index, but there is a difference between the following:

• The data columns of the basic data table do not follow the sorting order of their non-dataset indexes, and are sorted and stored in the row.
• The sharding indexes of non-dataset indexes are composed of index indexes (instead of data indexes.

Non-replica set indexes can be defined in the data table or replica set indexes with replica set indexes or heap indexes. Each index data column in a non-dataset index contains the index data value of the non-dataset and the data column locator. This locator points to the data column with this index value in the collection index or heap.

The data column locator in a non-dataset index data column is the index of the data column or the metadata SET index of the data column, as described below:

• If the data table is a heap index (indicating that no dataset index exists), the data column locator is the index of the data column. This metric will be set up based on case-sensitive identification, allocation, and item columns in the score column. The entire metric is also known as a data column (RID ).
• If a data table has a metadata SET index or an index is stored in an index, the data column locator is the metadata SET index of the data column. If the dataset index is not unique, SQL Server 2005 addsUniqueifierThe value generated in the internal part of the index, so that any re-indexing index becomes a unique index. The user cannot see the value of the four-digit metagroup. This value is added only when the replica SET index must be changed to a unique index for non-replica set indexing. SQL Server uses the dataset index indexes stored in the metadata columns of non-dataset sets to search for the dataset index, so that the data columns can be retrieved.

 

Data Source

Data Table and index Architecture

Use indexes to improve SQL server performance