Count rows with column varbinary NOT NULL tooks a lot of time

Question

This query

SELECT COUNT(*) 
FROM Table 
WHERE [Column] IS NOT NULL

takes a lot of time. The table has 5000 rows, and the column is of type VARBINARY(MAX).

What can I do?

Answer 1

Your query needs to do a table scan on a column that can potentially be very large without any way to index it. There isn't much you can do to fix this without changing your approach.

One option is to split the table into two tables. The first table could have all the details you have now in it and the second table would have just the file. You can make this a 1-1 table to ensure data is not duplicated.

You would only add the binary data as needed into the second table. If it is not needed anymore, you simply delete the record. This will allow you to simply write a JOIN query to get the information you are looking for.

SELECT
    COUNT(*)
FROM dbo.Table1
INNER JOIN dbo.Table2
    ON Table1.Id = Table2.Id