Difference between SQL Server clustered index and non-clustered Index

Clustered index: physical storage is sorted by index
Non-clustered index: physical storage is not sorted by index
Advantages and disadvantages
Clustered index: the data insertion speed is slow (the time is spent on the "sort of physical storage", that is, the location must be located first and then inserted)
Faster Data Query than non-clustered data

The text of the Chinese Dictionary itself isClustered Index. For example, if we want to check the word "an", we will naturally open the first few pages of the dictionary, because the Pinyin of "an" is "", the Dictionary of Chinese characters sorted by pinyin starts with the English letter "a" and ends with "z". Then the word "an" is naturally placed at the front of the dictionary. If you still cannot find the word in all the parts starting with "a", it means that you do not have this word in your dictionary. Similarly, if you query the word "Zhang, then you will turn your dictionary to the last part, because the Pinyin of "zhang" is "zhang ". That is to say, the body of the dictionary itself is a directory, and you do not need to query other directories to find the content you need. The body content is a directory arranged according to certain rules.Clustered Index".
If you know a word, you can quickly find it automatically. However, you may also encounter a word you do not know and do not know its pronunciation. At this time, you cannot find the word you want to query according to the method just now, you need to find the word you are looking for based on the "radicals", and then directly go to a page based on the page number after the word to find the word you are looking. However, the words you find in combination with the "radical directory" and "word checking table" are not really the sorting method of the text. For example, you can query the word "Zhang, we can see that the page number of the "Zhang" in the word checking table after the department head is 672, and the "Zhang" in the word checking table is "Chi", but the page number is 63, under "Zhang" is the word "", and the page is 390 pages. Obviously, these words are not really in the upper and lower sides of the word "Zhang". The continuous "Chi, Zhang, and "words you see are actuallyNon-clustered IndexIs the word in the dictionary body inNon-clustered Index. We can find the words you need in this way, but it requires two steps: first find the results in the directory, and then flip to the page number you need. We call this directory purely a directory, and the body is purely a sort of the body called"Non-clustered Index".
Through the above example, we can understand what is"Clustered IndexAndNon-clustered Index".
Further, we can easily understand that each table can have only oneClustered IndexBecause directories can only be sorted in one way. The query speed is obtained by adding: declare @ d datetime before each select statement.

Set @ d = getdate ()

Add the following after the select statement:

Select [statement execution time (MS)] = datediff (MS, @ d, getdate ())

1. Using an aggregate index is faster than using a primary key that is not an aggregate index

2. Using an aggregate index is faster than using an ordinary primary key as order by, especially when the data volume is small.
In fact, if the data volume is small, useClustered IndexCompared to sorting columnsNon-clustered IndexThe speed is much faster. If the data volume is large, for example, more than 0.1 million, the speed difference between the two is not obvious.

3. When you use an aggregate index for a period of time, the search time decreases proportionally according to the percentage of data in the entire data table, regardless of the number of times the aggregate index is used.

4. The date column does not slow down the query because of input in minutes.

Retrieve the n to m records from the publish table:
Select top m-n + 1 *
FROM publish
WHERE (id NOT IN
(Select top n-1 id
FROM publish ))

Keywords with id publish table

The question of linking "query optimization" and "paging algorithm" is not a big one, because they both need a very important thing ――Clustered Index.

As we mentioned in the previous discussion,Clustered IndexThere are two major advantages:

1. Narrow the query range as quickly as possible.

2. Sort fields as quickly as possible.

1st are mostly used for query optimization, while 2nd are mostly used for data sorting during paging.
WhileClustered IndexYou can create only one table.Clustered IndexIt is even more important.Clustered IndexThe selection can be said to be the most critical factor to achieve "query optimization" and "efficient paging.
HoweverClustered IndexA column meets both the needs of the query column and the requirements of the sort column. This is usually a contradiction.

Clustered IndexIs so important and precious, so be sureClustered IndexBuilt on:

1. The most frequently used field to narrow the query scope;

2. The most frequently used field to be sorted.

Ii) when to useClustered IndexOrNon-clustered Index

The following table summarizes when to useClustered IndexOrNon-clustered Index(Important).

Action Description	UseClustered Index	UseNon-clustered Index
Columns are sorted by group.	Ying	Ying
Returns data within a certain range.	Ying	Should not
One or few different values	Should not	Should not
Different decimal values	Ying	Should not
Different values of large numbers	Should not	Ying
Frequently updated Columns	Should not	Ying
Foreign key column	Ying	Ying
Primary Key Column	Ying	Ying
Frequently modify index Columns	Should not	Ying

Each table can have only oneClustered Index.