SQL Server Tour--seventh station Why do we say that a field with few States cannot be indexed

When we were learning SQL Server, most textbooks and seniors said that the less-state fields don't index, and the overhead is not as good as indexing, but how many people really know that,

Or how many people really have a more profound understanding of this, rather than listen to others hearsay ... So remember fast, forget also not slow ... Let me analyze this sentence for a few meanings.

A: Phenomenon

First we use the test data to find the problem, I first set up a person, there are 5 fields, the table SQL is built as follows:

DROP TABLEdbo. personCREATE TABLEPerson (IDINT PRIMARY KEY IDENTITY, NAMEVARCHAR( the), ageINT, EmailVARCHAR( -), IsmanINT )--Create a nonclustered index in the Isman field (0: Female 1: male)CREATE INDEXIdx_isman ondbo. Person (Isman)DECLARE @ch  as INT=0 while @ch<=100000BEGIN    INSERT  intodbo. Person (Name,age,email,isman)VALUES    (      REPLICATE(CHAR(@ch), -),      @ch,      CAST(CAST(RAND()*1000000000  as INT) as VARCHAR(Ten))+'qq.com',      @ch%2    )    SET @ch=@ch+1END

The above SQL can be found in the table has 5 fields, the ID is a clustered index, Isman is a nonclustered index, Isman is two states (0,1), and insert 10w records, as follows:

SQL is done, the next thing to do is query: Isman=1 records, such as:

Hemp eggs .... Brother obviously is in Isman on the data retrieval, how to become "Clustered Index Scan"??? What does he mean, not to go through my "Idx_isman" index,

But he's gone. "Clustered index (PK__PERSON__3214EC276EF57B66)" .... Also see the above "logical read" for 521 ... Indicates that 521 data pages have been walked in memory.

But I don't obey ... I have to let the execution plan go my index ... The way is to force the designation ... Such as.

See the above diagram, you are not already crazy ... I just fished 5w data, you gave me a walk 10w multiple data pages ... So, 1 records to go two data pages ... While the scan gathers

Index to go 521 data pages, 200 times times the difference between ... No wonder the execution of the plan killed the "Idx_isman" Index ... If you go this way, do you still have to stab SQL Server with a knife???

Second: Analysis of the reasons

Now very angry, the whole person is not good, why this??? To find out the problem, we still have to look at the data page.

1 DBCC TRACEON (3604,2588)2DBCC IND (Ctrip,person,-1)

From the above three graphs, you can probably see that10w data with 697 data pages, with a clustered index of 521, a nonclustered index of 176, which also shows that the above "clustered index Scan" go

Times the all of its own data pages to get the data, but also found that both indexes have a common feature is that there is only one root node (indexlevel=1) and countless (indexlevel=0)

Leaf node, and then there's a picture in my head ...

Above is my idea of the figure, this professional name is called Bookmark search ... After we build the "Idx_isman" index, we build the B-tree structure of the right half graph, where the index records

Will store two values, one is the index value Isman and a clustered index value ID, if you do not believe, you can use the DBCC page to explore the "Idx_isman" index page, you can also through the

DBCC show_statistics to view,

Then the engine was scanned by "Idx_isman" and got the key value, but unfortunately, I was a select *, so I had to spurt out the Name,emai in the record, but

"Index_isman" does not save these fields, so you have to go through the key to "clustered index" B-Tree to find ... Finally, the target record was found through the "clustered index" B-tree, which also

It is called "key lookup" in the execution plan, and then a field such as "Name,email" is sprayed .... the problem is here ... Because I am so back and forth Tigger Tigger ... caused the finding

complete one record, need Tigger 2-3 times data Page ... the specific search records, can refer to the picture of the "Purple Line", and finally caused the 10w multiple Tigger ...

Three: Revelation

What revelation does this example give us??? Think about it and you'll know ... Using nonclustered indexes, do not take too much data ... Because too much data can result in multiple

B-Tree back and forth Tigger ... If you want to do less data, you must index the high-uniqueness field so that the data that is in the nonclustered index B-tree is relatively small and

Reduced the number of B-trees I Tigger to the "PRIMARY key index" ... So the number of times back and forth Tigger far more than the "clustered index" scan to the benefit of the wrong ...

So the conclusion is that a nonclustered index must be established on a field with a higher uniqueness.

SQL Server Tour--seventh station Why do we say that a field with few States cannot be indexed