Differences in clustered and nonclustered indexes

Original address: http://www.cnblogs.com/flashicp/archive/2007/05/08/739245.html

Differences in clustered and nonclustered indexes:
The body of a Chinese dictionary is itself a clustered index. For example, we have to check the word "Ann", it will be very natural to open the first few pages of the dictionary, because "ann" Pinyin is "an", and alphabetical order of Chinese characters in the dictionary is the English letter "a" beginning and "Z", then the word "Ann" naturally ranked in the front of the dictionary. If you have turned over all the parts that begin with "a" and still cannot find the word, then it means that you do not have the word in your dictionary, and if you look up the word "Zhang", you will also turn your dictionary into the last part, because the pinyin of "Zhang" is "Zhang". That is, the body part of the dictionary is itself a directory, and you do not need to look up other directories to find what you need to find. The body content itself is a directory of rules arranged by a certain rule called a "clustered index".
If you know a word, you can quickly check it out automatically. But you may also encounter the words you do not know, do not understand its pronunciation, at this time, you can not follow the method to find the word you want to check, and need to go to the "radicals" to find the word you are looking for, and then according to the page number after the word directly to a page to find the word you are looking for. But the sort of words you find in combination with the "radicals" and "gept" is not really the sort method of the body, for example, you check the word "Zhang", we can see in the Gept table after the Radicals "Zhang" page number is 672 pages, gept table "Zhang" above is "Chi" word, but the page number is 63 pages, "Zhang" below is "crossbow "Word, page is 390 pages. Obviously, these words are not really in the "Zhang" the word of the upper and lower side, now you see the continuous "Chi, Zhang, crossbow" three words is actually their order in the nonclustered index, is the dictionary body of words in the non-clustered index mapping. We can find the words you need in this way, but it takes two procedures to find the results in the catalog and then turn to the page numbers you need.

We put this kind of directory purely as a directory, the body is purely the sort of body is called "nonclustered index".

From the example above, we can understand what is "clustered index" and "nonclustered index".

Further, we can easily understand that each table can have only one clustered index, because the catalog can only be sorted in one way.


The query speed is calculated by adding: Declare @d datetime in front of each SELECT statement

Set @d=getdate ()

and add it after the SELECT statement:

Select [Statement execution takes time (milliseconds)]=datediff (Ms,@d,getdate ())

1. Using aggregate index is faster than primary key with not aggregate index

2, using the aggregate index than the general primary key for the order by when the speed, especially in the case of small data volume

In fact, if the amount of data is very small, it is much faster to use the clustered index as the rank sequence than the non-clustered index, and if the data volume is large, such as more than 100,000, the speed difference between the two is not obvious.

3. Using the time period within the aggregated index, the search time is scaled down by the percentage of the data in the data table, regardless of how many of the aggregated indexes are used

4. The date column will not slow down the query speed because there is a minute or seconds input

From the Publish table, remove the records from article N to article M:
SELECT TOP m-n+1 *
From publish
WHERE (id not in
(SELECT TOP n-1 ID
from publish))

Keywords with id publish table

So the "query optimization" and the "paging algorithm" are not very big topics to put together, because both need a very important thing-clustered index.

As we have mentioned in the previous discussion, the clustered index has the two biggest advantages:

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

2, the fastest speed of the field sorting.

The 1th is used in query optimization, and the 2nd is used to sort data when paging.
The clustered index can only be created in each table, which makes the clustered index more important. The selection of clustered indexes can be said to be the most critical factor in achieving "query optimization" and "efficient paging".
However, it is often a contradiction to make the clustered index columns conform to the needs of the query columns and the order of the rows.

Clustered indexes are so important and valuable, so be sure to build the clustered index in:

1. The fields you use most frequently to narrow the scope of the query;

2. The fields you use most frequently, which need to be sorted.

Differences in clustered and nonclustered indexes