Szacunek liczności SARG, dlaczego nie pełny skan?

Dlaczego nie ma pełnego skanowania (w SQL 2008 R2 i 2012)?

Dane testowe:

DROP TABLE dbo.TestTable
GO  
CREATE TABLE dbo.TestTable
(
   TestTableID INT IDENTITY PRIMARY KEY,
   VeryRandomText VarChar(50),
   VeryRandomText2 VarChar(50)
)
Go
Set NoCount ON
Declare @i int
Set @i = 0
While @i < 10000
Begin
   Insert Into dbo.TestTable(VeryRandomText, VeryRandomText2)
      Values(Cast(Rand()*10000000 as VarChar(50)), Cast(Rand()*10000000 as VarChar(50)));
   Set @i = @i + 1;
End
Go
CREATE Index IX_VeryRandomText On dbo.TestTable
(
    VeryRandomText
)
Go

Podczas wykonywania zapytania:

Select * From dbo.TestTable Where VeryRandomText = N'111' -- bad

Otrzymaj ostrzeżenie (zgodnie z oczekiwaniami, ponieważ porównanie danych nchar z kolumną varchar):

<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[DemoDatabase].[dbo].[TestTable].[VeryRandomText],0)" />

Ale potem widzę plan wykonania i widzę, że nie używa pełnego skanowania, jak się spodziewałbym, ale zamiast tego szuka indeksu.

Oczywiście jest to całkiem dobre, ponieważ w tym konkretnym przypadku wykonanie jest znacznie szybsze niż w przypadku pełnego skanowania.

Ale nie rozumiem, w jaki sposób SQL Server podjął decyzję o podjęciu tego planu.

Ponadto, jeśli sortowanie na serwerze byłoby zestawieniami Windows na poziomie serwera i SQL Server na poziomie bazy danych sortowania, spowodowałoby to pełne skanowanie tego samego zapytania.

Porównując wartości różnych typów danych, SQL Server przestrzega reguł pierwszeństwa typu danych . Ponieważ nvarchar ma wyższy priorytet niż varchar, SQL Server musi przekonwertować dane kolumny na nvarchar przed porównaniem wartości. Oznacza to zastosowanie funkcji w kolumnie, co spowoduje, że zapytanie nie będzie możliwe do wysłania.

SQL Server dokłada jednak wszelkich starań, aby uchronić Cię przed błędami, dlatego wykorzystuje technikę opisaną przez Paula White'a w poście na blogu Dynamic Seeks and Hidden Implicit Conversions, aby wyszukać zakres wartości, a następnie dokonać ostatecznego porównania z konwersja wartości kolumny na nvarchar, w predykacie resztkowym, aby odfiltrować wszelkie fałszywe alarmy.

Jak już zauważyłeś, nie działa to, gdy zestawienie kolumny jest zestawieniem SQL. Powód, jak sądzę, można znaleźć w artykule Porównanie zestawień SQL do zestawień Windows

Zasadniczo zestawienie systemu Windows używa tego samego algorytmu dla varchar i nvarchar, gdzie zestawienie SQL używa innego algorytmu dla danych varchar i tego samego algorytmu jak zestawienie Windows dla danych nvarchar.

Tak więc przejście od varchar do nvarchar w ramach sortowania Windows będzie korzystało z tego samego algorytmu, a SQL Server może generować zakres wartości z, w twoim przypadku, literału nvarchar, aby uzyskać wiersze z indeksu kolumny sortowania SQL varchar. Jednak gdy zestawienie kolumny varchar jest zestawieniem SQL, nie jest to możliwe z powodu zastosowania innego algorytmu.

Aktualizacja:

Demonstracja różnych porządków sortowania dla kolumn varchar przy użyciu sortowania w systemie Windows i SQL.

SQL Fiddle

Konfiguracja schematu MS SQL Server 2014 :

create table T(C varchar(10));

insert into T values('a-b'),('aa'),('ac');

Zapytanie 1 :

select C
from T
order by C collate SQL_Latin1_General_CP1_CI_AS;

Wyniki :

|   C |
|-----|
| a-b |
|  aa |
|  ac |

Zapytanie 2 :

select C
from T
order by C collate Latin1_General_100_CI_AS;

Wyniki :

|   C |
|-----|
|  aa |
| a-b |
|  ac |

Musisz pamiętać, że węzły liści indeksu nieklastrowanego składają się ze stron indeksu zawierających klucz klastrowania lub identyfikator RID do zlokalizowania wiersza danych.

W klauzuli where podajesz: VeryRandomText = N'111'Ponieważ na VeryRandomText istnieje indeks nieklastrowany (Utwórz indeks utworzy indeks nieklastrowany, chyba że wyraźnie powiesz mu, aby utworzył klastrowany), najtańszym sposobem na znalezienie danych jest zeskanowanie indeksu w celu znalezienia rowid i następnie pobierz dane dla wiersza.

Jeśli utworzysz indeks klastrowany

CREATE clustered Index IX_VeryRandomText On dbo.TestTable (VeryRandomText)

lub klucz podstawowy na VeryRandomText dostaniesz skan tego indeksu.

Zobacz książki online lub tutaj: http://www.sqlforge.com/w/Clustered_index,_nonclustered_index,_or_heap