Durch die aktuelle Ausgabe des SQL-Server-Magazine wurde ich auf ein Problem aufmerksam: Wenn man LOBs (Large Objects) in einem Index speichert, dann kann man verschiedene Dinge nicht tun. Dazu reicht es schon, wenn das LOB-Feld nur auf der Index-Seite gespeichert ist, bspw. beim Clustered-Index.

online operations

Dazu steht in den "Guidelines for Performing Online Index Operations":

When you perform online index operations, the following guidelines apply:
Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.

Kimberly L. Tripp beschreibt es in dem SQL-Server-Magazine-Artikel "Database Design for Performance" noch etwas genauer:

SQL Server 2005 allows any column to be in the leaf level of an index—including LOB types. If a LOB type is in the leaf level of an index, the index won't support online operations.

Das gilt also auch für Clustered-Indexes und betrifft im Wesentlichen "ALTER INDEX REBUILD". Wenn es rechtzeitig vorher weiß, dann sollte das kein Problem sein. Dann kann man nötigenfalls die Tabelle auf zwei aufteilen…

unerwartete Fehlermeldung

Meines Erachtens besteht aber auch ein Zusammenhang zu folgendem Phänomen: In bestimmten Fällen erscheint bei einem Update eine unerwartete Fehlermeldung:

The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time.

bzw.

Msg 8629, Level 16, State 3, Line 1 – Warnung: Der Abfrageprozessor konnte aus dem Optimierer keinen Abfrageplan erzeugen, da eine Abfrage nicht gleichzeitig eine text-, ntext- oder image-Spalte und einen Gruppierungsschlüssel aktualisieren kann.

Um bei einem UPDATE in das Problem zu laufen, müssen folgende Bedingungen erfüllt sein:

  • Die Tabelle hat einen Clustered Index.
  • Die Tabelle hat wenigstens ein Feld vom Typ IMAGE, NTEXT, TEXT, NVarchar(max), Varchar(max), Binary(max) oder XML.
  • Der Inhalt des Feldes wird nicht in dem Datensatz gespeichert.
  • Der Zugriffsplan ist so ausgelegt, dass mehrere Datensätze geändert werden (selbst wenn tatsächlich nur einer da ist).

-- ggf. Testtabelle löschen
if object_id('tempdb..#t1') is not null
drop table #t1
go
– Testtabelle anlegen
create table #t1 (
c1 int not null,
c2 int not null,
primary key clustered (c1,c2),
c3 text null,
c4 int identity)
go
– Tabelle mit Testdaten füllen
insert #t1 values (1,7,'test')
go
– Das geht problemlos:
update #t1 set c2=10, c3='test again' where c2=7
go
– Die Fehlermeldung verursachen:
update #t1 set c2=10, c3=replicate('x',8000)+replicate('y',8000) where c2=7

go
– ggf. Testtabelle löschen (aufräumen)
if object_id('tempdb..#t1') is not null
drop table #t1

aus den TechNet-Foren

Als Abhilfe kann ich zwei Möglichkeiten anbieten:

  1. Den Custered Index entfernen oder in einen "normalen" Index ändern.
  2. Das Statement wird in zwei Einzelteile zerlegt: einen auf die LOB-Spalten und einen auf die anderen.