1, the primary key is a clustered index.

2. As long as the index is established, the query speed can be significantly improved.

3. Add all the fields that need to improve the query speed to the clustered index to improve the query speed.

(d) Summarize the experience of using indexes not found in other books.

1. It is faster to use an aggregated index than to use the primary key of a non-aggregated index.

2. Using aggregate index is faster than using ordinary primary key as order by, especially in the case of small data.

3. Using time periods in clustered indexes, no matter how many clustered indexes are used, the search time will be reduced in proportion to the percentage of data in the whole data table.

4. The date column will not slow down the query speed because of the input of minutes and seconds.

(5) Other precautions

1. Don't index commonly used small tables.

2. Don't choose social security number (SSN) or ID number as the key.

3. Don't use the user's key

4. Don't index memo/comment fields or large text fields (many characters).

5. Use the system-generated primary key

Second, improve the SQL statement.

1, whether the Like statement belongs to SARG depends on the wildcard type used.

2. Otherwise, it will lead to full table scanning.

3. Statements that do not conform to SARG form caused by non-operators and functions.

4, the role of IN is equivalent to OR.

5. Try not to use it, the less the better.

6.exists and in have the same execution efficiency.

7. Using the charindex () function is as efficient as adding the wildcard% before it.

8. Alliance is not absolutely more effective than or.

9. Field extraction should follow the principle of "how much is needed, how much is extracted" and avoid "selection *"

10 and count(*) are not slower than count (field).

1 1, order by is the most efficient sort by clustered index columns.

12, high efficiency top