I can't compare sql_variant field with IS NULL operator

I mean I can't query null values when column type sql_variant

For example docsdate table look like this:

ValID DocID     Value开发者_运维知识库  <--sql variant column)

 1. 488 146      30.10.2007
 2. 740 190      31.03.2008
 3. 570 161      31.10.2008 
 4. 242 103      NULL
 5. 248 104      NULL

When query like select * from docsdate where value is null no rows returned

Any idea?

This works fine for me. Are you sure your NULL values are actually proper NULLs and not strings containing the text NULL? Do they show up with a yellow background in SSMS?

create table #docsdate 
(
ValID int,
DocID int,
value sql_variant
)

INSERT INTO #docsdate
SELECT 488,146,'30.10.2007' UNION ALL
SELECT 740,190,'31.03.2008' UNION ALL
SELECT 570,161,'31.10.2008' UNION ALL 
SELECT 242,103,NULL UNION ALL
SELECT 248,104,NULL

SELECT * FROM #docsdate
WHERE value IS NULL

DROP TABLE #docsdate