Improve SQL Server database operation efficiency by using Indexes

The function of indexing is similar to the book directory. The book directory will be arranged in the order of chapters and will refer to a specific position. In this way, if we look for a chapter location in a book with hundreds of pages, we can scan only the book's directories, and the scanning range is reduced by n times, the query efficiency is naturally improved. In addition, if sqlserver memory is sufficient, the index will

The function of indexing is similar to the book directory. The book directory will be arranged in the order of chapters and will refer to a specific position. In this way, if we look for a chapter location in a book with hundreds of pages, we can scan only the book's directories, and the scanning range is reduced by n times, the query efficiency is naturally improved. In addition, if the SQL server Memory is sufficient, the index will

The function of indexing is similar to the book directory. The book directory will be arranged in the order of chapters and will refer to a specific position. In this way, if we look for a chapter location in a book with hundreds of pages, we can scan only the book's directories, and the scanning range is reduced by n times, the query efficiency is naturally improved. In addition, if the SQL server Memory is sufficient, the index will be placed in the memory, and searching in the memory will naturally improve the efficiency. Therefore, we must make proper use of the index.

　　1) What columns are indexed?

By default, a clustered index is created for the primary key. In addition to this index, which columns need to be indexed? This problem can only be analyzed in specific situations. It depends on the SQL statement to be optimized (usually the statements with a large number of queries and the desired height), and the query is based on the conditions of the column.

For example, one table in the Forum database is the post reply table. The most commonly used table in the forum application is to query the reply on a page of the specified post, the condition for querying the reply table is the id of the primary sticker. At this time, it is inevitable to create an index on the primary sticker field.

　　2) Do I Have To create a clustered index on the primary key?

Generally, SQL server automatically adds a clustered index to the primary key, but with some exceptions, we need to create the clustered index on other columns. For example, we use table partitions, the partition field is not the primary key. In this case, the clustered index must be created on the partition column. In addition, if the number of queries based on the primary key is small, but the number of queries based on other columns is frequent, you can also consider setting the clustered index on a non-primary key. Note that clustering index columns must be difficult to change. If clustering index changes, the records in the clustered index will be relocated, resulting in page separation and fragmentation; second, the index of each non-clustered index will be modified, so that the index key value of all related non-clustered index rows can be corrected. This is a waste of time and space, as well as fragments that need to be organized, increasing unnecessary overhead (reorganizing clustering keys for each column ).