datalength versus is not null in queries RRS feed

  • Question

  • I would like if anyone could comment on why "is not null" is so much slower than datalength in these two queries:

    select id
    from document
    group by id, DATALENGTH(blobfield)
    having DATALENGTH(blobfield) > 0

    select id
    from document
    where blobfield is not null

    In a table with 9000 records and approximately 6Gb of blob-data the first query executes immediately whereas the latter takes 50+ seconds on my machine (if not timing out). The server is a SQL2008 on a dedicated quadcore Win2003. It should be adequate.

    Is this a known thing? If not it should be mentioned in the T-SQL pages regarding DATALENGTH and NULL

    Tuesday, October 13, 2009 8:28 AM