Foreign Keys ohne Index ermitteln

24. Januar 2015

SQL Server setzt für Tabellenbeziehungen nicht automatisch einen Index

In den ersten Versionen von Microsoft SQL Server wurde beim Definieren einer Tabellenbeziehung (Foreign key, Fremdschlüssel) automatisch ein sogenannter impliziter Index auf das entsprechende Feld in der Parent-Tabelle gesetzt. Seit SQL Server 2005 ist dies nicht mehr der Fall, d.h. der Benutzer muss selbst entscheiden, ob er einen Index setzen möchte oder nicht. 

Foreign key: Index setzen oder nicht?

In der Regel wird es sinnvoll sein,  für jede Tabellenbeziehung einen Index zu definieren. Eine Ausnahme besteht eigentlich nur dann, wenn es in der referenzierten Tabelle nur sehr wenige verschiedene Datensätze gibt, denn in dem Fall ist der Gewinn durch einen Index nicht sehr groß. Aber auch dann würde ich den Index setzen - für den Fall, dass irgendwann doch mehr Datensätze eingetragen werden und der Index benötigt wird. Schaden tut ein Index jedenfalls nur dann, wenn in der Tabelle ständig viele Datensätze eingefügt und/oder gelöscht bzw. die Werte im referenzierten Feld ständig geändert würden.

Prüfen, ob für alle Tabellenbeziehungen ein Index vorhanden ist

Um sicher zu gehen, dass man keinen Index vergessen hat, kann man mit einer einfachen Abfrage in den Systemtabellen die Foreign Keys ohne Index ermitteln:

SELECT obj.name AS fkey, tab1.name AS tab1, col1.name AS col1, 
                tab2.name AS tab2, col2.name AS col2
FROM sys.foreign_key_columns fk 
  INNER JOIN sys.objects obj ON obj.object_id = fk.constraint_object_id 
  INNER JOIN sys.tables tab1 ON tab1.object_id = fk.parent_object_id
  INNER JOIN sys.columns col1 ON col1.column_id = fk.parent_column_id 
       AND fk.parent_object_id = col1.object_id 
  INNER JOIN sys.tables tab2 ON tab2.object_id = fk.referenced_object_id 
  INNER JOIN sys.columns col2 ON col2.column_id = fk.referenced_column_id 
       AND fk.referenced_object_id = col2.object_id
WHERE (NOT EXISTS
   (SELECT tabi.object_id, tabi.index_id
      FROM sys.indexes AS tabi 
         INNER JOIN sys.index_columns AS tabic ON tabi.object_id = tabic.object_id 
            AND tabi.index_id = tabic.index_id
      WHERE (tabi.object_id = fk.parent_object_id) 
            AND (tabic.column_id = fk.parent_column_id) 
            AND (tabic.index_column_id = fk.constraint_column_id)))

Für die Foreign keys in der Liste sollte man dann gegebenenfalls einen Index manuell erstellen, um die Performance der SQL-Datenbank zu optimieren.