Indexsuche mit Coalesce

Question

Ich habe eine Tabelle [MyTable] mit einer Spalte [MyColumn] NVarchar(50). Ich habe einen nicht geclusterten Index für diese Spalte, während ich nun die beiden folgenden Abfragen ausführe:

SELECT  1
FROM    [MyTable] M
WHERE   M.[MyColumn] = @MyColumn

SELECT  1
FROM    [MyTable] M
WHERE   M.[MyColumn] = COALESCE(@MyColumn, M.[MyColumn] )   

Ich habe festgestellt, dass die erste Abfrage Indexsuche (nicht geclustert) und die zweite verwendet Index-Scan (nicht geclustert) . Darf ich wissen, wie ich die Indexsuche mit coalesce oder isnull nutzen kann?

Answer 1

Das ist nicht einfach, denn wie Alex schon sagte, erzwingt die Verwendung der Funktionen einen Scan, da der Optimierer weiß, dass er jede Zeile überprüfen muss.

Was Sie tun KÖNNEN, ist die Erstellung einer Berechnete Säule für das Ergebnis Ihrer Funktion, und diese Spalte indizieren .

Eine schönere Art der Suche gibt es eigentlich nicht.

EDIT:

Wenn Sie Ihre Frage erneut lesen, ist dies möglicherweise keine Option für Sie, es sei denn, Sie überdenken Ihre Logik. Sie binden eine Variable in die Funktion ein, und es gibt absolut keine Möglichkeit, diese zu indizieren.

EDIT 2:

Versuchen Sie anstelle Ihrer derzeitigen Logik etwas wie:

...
WHERE (M.[MyColumn] = @MyColumn 
       OR @MyColumn IS NULL)

Answer 2

Darf ich wissen, wie ich die folgenden Funktionen nutzen kann Indexsuche mit Coalesce oder isnull?

Vielleicht ist das keine Antwort auf Ihre Frage, aber Sie können zwei verschiedene Abfragen haben. Eine für den Fall, dass @MyColumn is null und eine für den Fall, dass Sie die @MyColumn in der Where-Klausel.

IF @MyColumn IS NULL
BEGIN
  SELECT  1
  FROM    [MyTable] M
END  
ELSE
BEGIN
  SELECT  1
  FROM    [MyTable] M
  WHERE   M.[MyColumn] = @MyColumn
END

Answer 3

Mit Funktionen wie COALESCE o ISNULL in der Where-Klausel bittet den Server um die Suche nach dem Ergebnisse dieser Funktionen - die unbekannt sind, bis sie für jede Zeile in der Ergebnismenge ausgeführt werden, so dass es keine Möglichkeit gibt, einen Index zu verwenden.

Um die Vorteile des Indexes voll auszunutzen, verwenden Sie keine Funktionen in der WHERE-Klausel, sondern modifizieren Sie sie mit Standardbedingungen, z. B. WHERE MyColumn = @MyColumn OR @MyColumn IS NULL

Answer 4

Ich vermute, Sie werden diese Abfrage in einer komplexeren Abfrage verwenden, möglicherweise mit EXISTS :

EXISTS 
( SELECT  1
  FROM    [MyTable] M
  WHERE   M.[MyColumn] = COALESCE(@MyColumn, M.[MyColumn] )
)

Versuchen Sie stattdessen dies:

EXISTS 
( SELECT  1
  FROM    [MyTable] M
  WHERE   M.[MyColumn] = @MyColumn
)
OR EXISTS
( SELECT  1
  FROM    [MyTable] M
  WHERE   @MyColumn IS NULL
)

---

Oder dieses:

CASE WHEN @MyColumn IS NULL
     THEN 1
     ELSE 
        ( SELECT  1
          FROM    [MyTable] M
          WHERE   M.[MyColumn] = @MyColumn
        )
END

Answer 5

In der Abfrage mit der Koaleszenzklausel weiß der Optimierer, dass "MeineSpalte" ein Wertebereich ist, so dass er sich für eine Suche im Index entscheidet. Die einzige Methode, eine Suche zu verwenden, wenn eine Nicht-Null-Variable übergeben wird, besteht darin, zwei gespeicherte Prozeduren zu kodieren und die geeignete über eine logische Prüfung der Variablen aufzurufen.

Wenn Sie eine so einfache Situation wie in Ihrem Beispiel haben und einen Index Seek verwenden möchten, wenn die Variable NICHT NULL ist, dann sollten Sie die Abfrage wie folgt kodieren:

If @MyColumn is NULL
Begin
    EXEC MyStoredProcWithMyColumn=Mycolumn
END
ELSE
Begin
    EXEC MyStoredProcWithMyColumn=Variable @MyColumn
END

nach der Erstellung von zwei gespeicherten Prozeduren, von denen eine die Daten unter Verwendung der Where-Klausel mit der Variablen und eine mit der Where-Klausel für die Spalte gleich sich selbst zurückgibt.