SQL Server Index

An index is like a book directory. You can quickly find data in a table without scanning the entire table.

You can easily search for directories, but you also need paper to store the directory information. If you add or delete the contents of a book

The directory must also be modified. indexes are similar and can be quickly searched, but some memory overhead and maintenance overhead are required.

First, explain how the index can quickly find data?

An index is a separate, physically distributed storage database structure. It is created for a table and each index

PageAll rows in contain logical pointers pointing to physical locations in the database table. When searching, always first

Search the index page, find the pointer to the required data, and then find the data page to read the data to improve the query efficiency.

　　Creating an index page must occupy part of the memory. It also takes time to maintain indexes when adding, deleting, and modifying indexes,

Therefore, it is necessary to select an appropriate column to create an index.

So what columns should we choose to create indexes?

1. Primary Key column: The most common column used for access is the primary key.

2. Foreign key column: The foreign key is usually associated with other tables for query, set it as an index, and can be connected more quickly

3. columns frequently used as condition searches and columns frequently searched by sort.

　　After selecting a column, you must follow the indexing principles as follows:

1. Do not create indexes for tables with a small amount of data. Because the data is too small, the index effect is not obvious and it takes time to maintain the indexes.

2. You can index a field with many data values or null values, which greatly improves the retrieval speed.

3. When a large amount of data in the table is updated, the index is deleted and updated, and then the index is created.

4. Do not use like '% XX %' in fuzzy search, but use like 'A %'

5. Use less full table Indexes

　　Now that you know the principles, you can create indexes. Generally, indexes are divided into clustered indexes (cluster indexes) and non-clustered indexes (non-clustered indexes ),

Create index is used to indicate that clustered should be declared when a clustered index is created.

For example, to create a descending non-clustered index or clustered index for the NO and name columns in the studentinfo table, you can write as follows:

Create [clustered] index indexname_stuinfo on studentinfo (No, name DESC)

[Clustered] indicates optional. If this parameter is selected, clustering is established.

Note: Only one table can be created for clustered indexes. Therefore, you must delete the original clustered index before creating a clustered index for a table.

For example: if exists (Select name from SYS. indexes where name = 'indexname _ stuinfo ')

Drop index studentinfo. indexname_stuinfo

Go

Create clustered index indexname_stuinfo on studentinfo (No, name DESC)

Go

***** ***************************************