Explain the query plan that sql server selects

Question

In this blog post , I need to clarify why the SQL server chose a particular type of scan:

For simplicity's sake, suppose that col1 is unique and always increases in value, col2 has 1000 different values, and there are 10,000,000 rows in the table, and that the clustered index consists of col1, and the nonclustered index exists on col2.

Imagine a query execution plan created for the initial past parameters: @ P1 = 1 @ P2 = 99

These values ​​will result in an optimal query for the following using the substituted parameters:

Choose * from t, where col1> 1 or col2

99 order by col1;

Now imagine a plan for executing the query if the initial parameter values ​​were: @ P1 = 6,000,000 and @ P2 = 550.

As before, the optimal query is created after replacing past parameters:

Choose * from t, where col1> 6000000 or col2> 550 order in col1;

These two identical parameterized SQL expressions can potentially cache very different execution plans due to differences in the originally passed parameter values. However, since SQL Server only caches one execution plan for each query, the likelihood is very high that in the first case, the query execution plan will use a clustered index scan because of the 'Col1> 1 parameter change. Whereas in the second case, a query execution plan using an index search is likely to be created.

from: http://blogs.msdn.com/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx

, ?

Answer 1

, :

SQL Server , col1 > 1, .

col1 > 6000000, .

Answer 2

, , , , , .

, , , .

A , , . . , .

A , , , , , .

Answer 3

Please note that in both cases a clustered index will be used. In the first example, this is the SCAN clustered index, where, as in the second example, it will be the clustered SEEK index, which in most cases will be faster than the blog author claims.

SQL Server knows that a clustered index is increasing. Therefore, in the first case, it will perform a clustered index scan.