For more information about indexes, see full table scan and index scan.

Create a simple table structure first

Create table test (a int, B datetime)

Create index idx_ B on test (B)

Next, click test data.

Declare @ I int

Declare @ x int

Set @ I = 0

Set @ x = 0

While (@ I <1000)

Begin

Insert into test values (@ I, dateadd (hour, rand (@ x) * 10000, getdate ()))

Set @ x = @ x + rand () * 1000

Set @ I = @ I + 1

End

Query the following two statements:

Select a from test

Select 1 from test

We can see that the former uses full table scanning and the latter uses indexes, which is confusing. Why does SQLSERVER use index scanning for the latter? There are no filtering conditions.

In fact, there is a reason, because SqlServer knows that the latter will not take table data, then the scanning of the index tree will be faster than the scanning of the table (in fact, not necessarily so ), because the index tree usually occupies less space than the table data space, and Small Data Reading can reduce I/O reading, you must know that this is the most time-consuming operation, but in fact, because the current index uses B fields that account for the vast majority of the Data row space, here, the second statement is slower than the first one.

If you do not believe this, add a non-clustered index of field a to the table first:

Create index idx_a on test ()

Then execute the preceding two queries. This time, SQLSERVER cleverly chooses an idx_a index scan that consumes less space to execute the second query, but wait, why is the first query faster ??? In fact, the reason is very simple. The index tree traversal is more complex than the tablespace traversal, because the index tree actually has intermediate nodes to store data (you can see the structure of B tree, SQLSERVER uses B-tree. Only leaf nodes point to specific data (This point will be different because of clustered indexes. This will be discussed later ), therefore, the full table scan in the first sentence is faster. Does this mean that SQLSERVER has selected an incorrect scan method? Now, I try to increase the number of data entries to 10000 and execute it again. As you can see, index scanning is fast (the select Operation Cost for field a is ignored here, because the cost is really low ).

Run two SQL statements:

Select * from test where B is null

Select * from test where B is not null

We can see that these two queries both use indexes and correct the errors in my previous BLOG. SQLSERVER stores the NULL value in the index tree, which is related to its data structure, the NULL bitmap can process NULL values. The data storage structure in the index leaf node is similar to that in the table data row structure, while Oracle does not store NULL to the index.

Now, I have a meal, and I will talk about it later.