Differential analysis of SQL Server clustered and nonclustered indexes _mssql

Clustered index: Physical storage sorted by index
Nonclustered indexes: Physical storage is not sorted by index
Advantages and Disadvantages
Clustered index: Slow to insert data (time spent on "sort of physical storage", which is first to locate and then insert)
Query data is faster than nonclustered data

The text of a Chinese dictionary is itself aClustered Index。 For example, if we look at the word "Ann", we will naturally open the first few pages of the dictionary, because the pinyin of "an" is "an", and the dictionary of Chinese characters according to Pinyin is the beginning of the English letter "a" and ending with "Z", then the word "Ann" is naturally ranked in the front of the dictionary. If you end up with all the "a" parts you still can't find the word, so you don't have the word in your dictionary; Similarly, if you look at the word "Zhang", you will also turn your dictionary to the last part, because "Zhang" Pinyin is "Zhang". That is, the body part of the dictionary itself is a directory, and you don't need to look up other catalogs to find what you need to find. The body content itself is a sort of directory called "Clustered Index”。
If you know a word, you can quickly find the word from automatic. But you may also encounter words you don't know, do not know its pronunciation, at this time, you can not follow the way you have just found the word you want to search, and need to go according to the "radical" to find the word you are looking for, and then according to the number of the word after the page directly to find the word you are looking for. But the sort of word you find in conjunction with the "Radical catalog" and "CJK ideographs table" is not really the sort of method of body text, for example, you look up the "Zhang" word, we can see in the CJK ideographs table after the radical, "Zhang" page number is 672 pages, CJK ideographs Table "Zhang" above is "Chi" word, but the page number is 63 pages, "Zhang" below is "crossbow "Word, the page is 390 pages. Obviously, these words are not really in the "Zhang" word of the upper and lower sides, now you see the continuous "Chi, Zhang, crossbow" three words is actually theynon-clustered indexIs the word in the dictionary bodynon-clustered indexThe mapping in. We can find the word you need in this way, but it takes two processes to find the result in the TOC and then turn to the page number you want. We refer to this catalogue as purely a catalogue, and the text is purely a sort of text called "non-clustered index”。
From the above example, we can understand what is "Clustered Indexandnon-clustered index”。
Further extending, we can easily understand that each table can only have oneClustered Index, because catalogs can only be sorted in one way. The way to get the query speed is to add: Declare @d datetime before each SELECT statement

Set @d=getdate ()

and add after the SELECT statement:

Select statement execution takes time (ms)]=datediff (Ms,@d,getdate ())

1, the use of aggregate index than the index is not aggregated faster than the primary key

2, with the aggregate index than the normal primary key for the order by when the speed, especially in small amount of data
In fact, if the amount of data is small, useClustered IndexAs a sequence of rows rather than using thenon-clustered indexThe speed is obviously much more, and if the data amount is big, if more than 100,000, the speed difference between the two is not obvious.

3, using the time period within the aggregation index, the search time will be reduced by the percentage of the data in the entire data table, regardless of the number of aggregated indexes used

4, the date column will not be due to the minutes and seconds of input to slow down the query speed

Take the records from the Publish table to section N to article M:
SELECT Top M-n+1 *
From publish
WHERE (id not in
(SELECT top n-1 ID
from publish))

Keyword with ID publish table

Only so the "query optimization" and "pagination algorithm" These two links are not very big topic together, because both need a very important thing--Clustered Index。

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

1, the fastest speed to narrow the scope of the query.

2, the fastest speed for the field sorting.

The 1th article is more used in query optimization, and the 2nd is used more for sorting data when paging.
andClustered IndexOnly one can be created in each table, which makesClustered Indexseem more important.Clustered IndexSelection can be said to achieve "query optimization" and "efficient paging" the most critical factor.
But to both makeClustered IndexThe column conforms to the needs of the query column and the order of the rows, which is usually a contradiction.

Clustered Indexis so important and precious, so be sure toClustered IndexBased on:

1, you most frequently used, to narrow the scope of the query on the field;

2, the fields that you use most frequently and that need to be sorted.

(ii) When to use clustered or nonclustered indexes

The following table summarizes when to use clustered or nonclustered indexes ( very important ).

Action Description	Using Clustered Indexes	Using nonclustered Indexes
Columns are often sorted in groups	Should	Should
Returns data in a range	Should	should not be
One or very few different values	should not be	should not be
A small number of different values	Should	should not be
A large number of different values	should not be	Should
frequently updated columns	should not be	Should
FOREIGN key columns	Should	Should
Primary key columns	Should	Should
Frequently modify index columns	should not be	Should

There can be only one clustered index rule for each table.