BUG: DocErr: IS NULL Now Supports TEXT and IMAGE Columns

• Microsoft SQL Server, version 6.0

SYMPTOMS

The Microsoft SQL Server "Database Developer's Companion" incorrectly states on page 72 that the only WHERE conditions you can use on TEXT columns are LIKE and NOT LIKE. These conditions cannot determine the NULL status of a TEXT or IMAGE column. Microsoft SQL Server version 6.0 adds functionality to support using IS NULL and IS NOT NULL with TEXT and IMAGE columns.

WORKAROUND

In previous versions of SQL Server, it was illegal to use IS NULL and IS NOT NULL with TEXT and IMAGE columns, and doing so would result in error message 403, indicating an invalid operation. SQL Server 6.0 has been enhanced to allow IS NULL and IS NOT NULL to work correctly with TEXT and IMAGE columns.

The following query gives an example of the new behavior:

   use pubs
   go
   drop table junk
   go
   create table junk (a text null, b image null)
   go
   insert into junk (a) values ('a is not null but b is')
   insert into junk (b) values ('b is not null but a is')
   go
   select * from junk where a is null
   go
   select * from junk where b is not null
   go
   select * from junk where a like null
   go

STATUS

Microsoft has confirmed this to be a problem with the documentation in Microsoft SQL Server version 6.0 for Windows NT. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.