Two of the improvement of system performance--The establishment of "clustered index"

Previously on the database has been a period of research, the physical storage mechanism of the database also have some understanding, just read FREEDK write "SQL Server index structure and its use" benefit is not shallow, I also really realized that the database index on the importance of system performance.
In my traditional view, a primary key should normally be set to a clustered index, but now with a new understanding, primary key meaning is "unique identity record" about primary key design I also no longer detail, you can refer to my "Database primary key design thinking", and the index is mainly "improve database performance", especially "clustered index."
And now I'm going to focus on building the database index, the key is clustered index, "clustered index" and "nonclustered index" is simple to understand: "Clustered index" is the physical arrangement, and "nonclustered index" is the use of index tables (equivalent to the directory of books), the use of the index can improve performance, that is, " Avoid full table scans. "
So a table can have only one "clustered index", because in physics, there is no "two" physical storage methods. That's the way it is. Clustered indexes have the greatest impact on query performance, so pay great attention to the establishment of "clustered index", I now realize, is also FREEDK recommended "Document Date" as a clustered index, I said "Document Date" refers to the day-to-day business records, generally will have a date field, and 80% This "date field" is used in the query, and it is best to put it in the first effect of the where, because the query for other conditions in the specified area is much less wide and the performance is significantly improved.
As for other indexes, this can improve the performance of the system as much as possible by "avoiding full table scans", depending on the needs of the business, for some fields that will be queried. Of course, too many indexes will also cause the system burden, want to build so many "catalog" will naturally increase the burden.
There is also a "clustered index" because it is physically stored, and the performance is certainly improved, but for inserts and deletes, can cause the data "to move" (Inserts a record in the middle, will cause the second half part record to move later), certainly the database has its own storage mechanism, will use "the page" to block the storage, may avoid certain "the movement" , so the "clustered index" is best to be "incremental" in "most" cases, and the "Document Date" We are currently using is exactly the same.


Comments:

Since the growth ID as a clustered index is also more appropriate, the general datasheet "Document Date" and ID growth direction is the same.
The design of a clustered index is generally placed in the index of the qualified field of the most common query result set
If the limits of the ID and Date fields are the same (common as forum data)
can also be placed on the index of the self-added ID

Generally speaking, the ID and date of a document are also qualified (unless the same is the case with a GUID key), so a clustered index with a document ID and a document date is almost the same

The advantage of a clustered ID is that you can narrow the number of pages needed to read a contiguous set of results in a clustered field, and the disadvantage is that other indexes cannot directly fetch the actual data, but instead locate the pointer to the clustered index before fetching the actual data.
Looking at the message above, I found that everyone was about the same as my first thought, however, since the ID is not appropriate as a "clustered index" is not to narrow the result set search scope, so in the daily use of "Document Date" query, can not reflect the performance of high-performance query, and for primary key ID, establish a general index on it, Because the primary key ID search is generally to locate a record, there is no "locked range" of such a performance effect. In fact, everyone should read my reference article and then leave a message, perhaps your point of view has been changed.
Reference article I saw it a long time ago. The ID itself is as orderly as the date field. This is a valid general document for page IO limits the arrangement of dates and the ID are the same as for example, documents from January to April so the ID will also be between the 1~400 so that the two restrictions are exactly the same. And the efficiency of the query is simply to say that the index of the document date on the ID is not equal to saying that the index is not indexed on the date field
@progame:
Yes, because the direction is consistent, the "document date" conforms to the criteria for building a clustered index. Building a clustered index on the document date is a lot better than not building a clustered index performance. So, to build a clustered index on the "Document Date".
That's exactly what I mean.
However, the reason that an ID is not appropriate for a clustered index is that it cannot narrow the result set search scope

That's not true. In many cases, the self-increasing ID can be done as a clustered index.
To do a clustered index with a date field there's nothing wrong. I'm not denying that you're saying that a clustered index with an ID can also
@progame:
OK, agree, ID can also do a clustered index, but there is no "date" to the good, because a table can only have a "clustered index", so basically should adopt "date".
I agree with the method of building a clustered index on the document date because the ID is rarely used for querying in the actual application, and it is easy to lock the scope of the query into a small block on the document date. Also, ask a question: if there are different types of documents (the difference between the document number), the query is most of the time to check a certain type of documents, it should be in the document date and document number two columns on the build of a clustered index. I agree with the method of building a clustered index on the document date because the ID is rarely used for querying in the actual application, and it is easy to lock the scope of the query into a small block on the document date. Also, ask a question: if there are different types of documents (the difference between the document number), the query is most of the time to check a certain type of documents, it should be in the document date and document number two columns on the build of a clustered index.