The bug occurs when trying to delete rows from a table that has a
NULL value for an
image column. This works fine normally, but if there is a foreign key referencing the table (to any of its columns), any rows that have had their image column updated to be
NULL fail to be deleted. This SQL demonstrates the problem:
Not all of the delete queries work correctly. The output of the script is four result sets with the count of how many rows are in the table at each point. All of them should be 0 (as is the case on SQL Server 2000), but in SQL Server 2005 without SP3 they are actually 0, 3, 3 and 0.
The simple delete query:
does not delete any rows after the values for the Data column have been updated to
NULL, even though a similar select query:
Notably, if either the foreign key is removed, or the:
query is not performed, the script behaves as expected. Additionally, using
ntext instead of
image does not work as well, but using the new
varbinary(max) data types does work.
Apparrently, the distinction between
NULL values stored as a result of an insert or an update has precendece in the
If the table does not have in row text, SQL Server saves space by not initializing text columns when explicit or implicit null values are added in text columns with INSERT, and no text pointer can be obtained for such nulls. To initialize text columns to NULL, use the UPDATE statement. If the table has in row text, you do not have to initialize the text column for nulls and you can always get a text pointer.
This points to the “text in row” option having a bearing on this behaviour. Indeed, altering this option after creating the tables:
results in the script working as expected. Useful as a potential workaround.
The bug is present in all versions of SQL Server 2005, but not in SQL Server 2000 or 2008.