SQL Server Index

The order of clustered indexes is the physical order in which data is stored, so a table can have at most one clustered index.

The order of nonclustered indexes is independent of the physical storage order.

The data in SQL Server is indexed by the B-tree, so the leaf nodes of the clustered index store the data itself, not the leaf nodes of the clustered index, only the references to the data, the pointers.

SQL Server stores data in pages, a page occupies 8k of space, and is allocated with 8 pages, or one extents, 64k.

Database read

Logical reads, when the query is executed, reads the pages that are stored in memory, which are pre-read into memory by the database.

Physical reads, when the query is executed, SQL Server checks to see if the required data exists in memory and, if not, reads it from the hard disk into memory.

Pre-read, a SQL Server processing read, a subset of the data is pre-read into memory, these data are not necessarily required.

Fill factor

When you add data, if the current page is full, the new page is split and index fragmentation occurs. The fill factor is the scale of the data that is populated on the page, the default is 100%, and if the fill factor is 90% on a page, the page will not be split when the next new data is added (the data is not very large) and will not produce index fragmentation, but it will require more storage space and will increase the depth of the B-tree. directly affects database efficiency.

In general, the fill factor setting is dependent on the situation.

If most of the operations of a table are read, you can set the fill factor to 100%, because there is very little time to split the new page. If the read and write operations are accounted for in half, you can set the scale to 80,90. If there is a large number of write operations, then the scale is set to 50,70.

Of course, you can also set the fill factor to 100%, and then often check the index fragmentation and rebuild the index when needed.

Principles of Index Building

Indexed fields are as small as possible, and the index of int is much better than the text index

Indexed fields are as high as possible, and do not index on a table of 100 million data with gender.

SQL optimization

Try to avoid putting the expression on the left side of where

Try to avoid where it appears in! = and <>

Try to avoid where to null-judge a field

To avoid using cursors, you can use the while loop instead

Try to avoid returning large amounts of data, you can use pagination

Do not use SELECT *

Try to avoid in and not, use exists instead

SQL Server Index