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 ".
The word "an" is naturally placed in 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 the dictionary"
Then you will turn your dictionary to the last part, because the Pinyin of "Zhang" is "Zhang ". That is to say, the text part of the dictionary itself is a directory, and you do not need to check other directories to find
Content. 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 go to the "radicals" to find the word you are looking for, and then go to a page based on the page number after the word to find the word you are looking. However, when you combine the "radical directory" and "word checklist", the words you find are not sorted
The real sorting method of the body. For example, if you check the word "Zhang", we can see that the page number of the "Zhang" in the checkword table after the department head is 672 pages, in the word table, "Zhang" is preceded by "Chi", but the page number is 63.
Page, under the "Zhang" is the word "", the page is 390 pages. Obviously, these words are not really in the upper and lower sides of the word "Zhang", and the continuous "Chi, Zhang, and "word you see is actually him
InNon-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

Only query optimization and pagingAlgorithm"These two links are not a big topic, because both of them 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.