SQL Server Index Structure and usage (2) improvement of SQL statement page 1/3

For example:
Select * From Table1 where name = ''zhangsan'' and TID> 10000
And execution:
Select * From Table1 where TID> 10000 and name = ''zhangsan''
Some people do not know whether the execution efficiency of the preceding two statements is the same, because the two statements are indeed different from the statement sequence. If TID is an aggregate index, then, the next sentence can only be searched from the 10000 records in the table; in the previous sentence, you need to first look up several names = ''zhangsan'' from the entire table, and then propose the query results based on the constraints TID> 10000.

In fact, such a worry is unnecessary. SQL Server has a "query analysis optimizer", which can calculate the search conditions in the WHERE clause and determine which index can narrow the search space of the table scan, that is, it can achieve automatic optimization.

Although the query optimizer can automatically optimize queries based on the where clause, it is still necessary for you to understand the working principle of the "query optimizer". If not, sometimes the query optimizer does not perform quick queries according to your intention.

In the query and analysis phase, the query optimizer checks each stage of the query and determines whether the data volume to be scanned is useful. If a phase can be used as a scan parameter (Sarg), it is called an Optimized phase and the required data can be quickly obtained using the index.

Definition of Sarg: An operation used to restrict search, because it usually refers to a specific match, a matching within a worthy range or an and connection between two or more conditions. The format is as follows:
Column name operator <constant or variable>

Or

<Constant or variable> operator column name
The column name can appear on one side of the operator, while the constant or variable appears on the other side of the operator. For example:
Name = 'zhang san'

Price> 5000

5000 <price

Name = 'zhang san' and price> 5000
If an expression cannot meet the form of Sarg, it cannot limit the search range, that is, SQL Server must determine whether each row meets all the conditions in the WHERE clause. Therefore, an index is useless for expressions that do not meet the Sarg format.

After introducing Sarg, let's summarize the experiences of using Sarg and drawing different conclusions from some materials in practice:

1. Whether the like statement belongs to the Sarg depends on the type of the wildcard used.
For example, name like 'zhang % ', which belongs to Sarg

However, name like '% Zhang' does not belong to Sarg.
The reason is that the wildcard % is enabled in the string so that the index cannot be used.

2. or may cause full table scan.
Name = 'zhang san' and price> 5000 symbol Sarg, but: Name = 'zhang san' or price> 5000 does not match Sarg. Using or will cause a full table scan.
3. statements not in the Sarg format caused by non-operators and functions
The most typical case for statements that do not meet the requirements of the Sarg format is to include non-operator statements, such as: Not ,! =, <> ,! <,!> , Not exists, not in, not like, and other functions. Below are several examples that do not meet the Sarg format:
ABS (price) <5000

Name like '% 3'

Some expressions, such:

Where price * 2> 5000

SQL Server will also regard it as Sarg, and SQL server will convert this formula:
Where price> 2500/2
However, we do not recommend this because sometimes SQL Server cannot guarantee that this conversion is completely equivalent to the original expression.

4. The role of in is equal to or.
Statement:
Select * From Table1 where TID in (2, 3)

And

Select * From Table1 where tid = 2 or tid = 3
If they are the same, they will cause a full table scan. If there is an index on the TID, its index will also become invalid.