SQL Server - FileStream content length using len and datalength

Basically there are some differences between len and datalength functions which almost all SQL developers may know. len() returns the no of characters by trimming the right side and datalength() returns the no of bytes used to store the data structure in the table.

I would like to add one more scenario to this comparison which is nothing but finding length of FileStream content. If you are new to SQL FileStream please refer this article. The scenario we were encountering was so simple. We had to list out all the files which are empty. To be more on business side, need to delete all the zero byte files in file stream storage table.

Below is the table def

CREATE TABLE [DocumentContent](
    [DocumentContentID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [FileStreamContent] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [PK_DocumentContent] PRIMARY KEY NONCLUSTERED 
(
    [DocumentContentID] ASC
))

The initial query used to determine the list of blank files is

select DocumentContentID 

from DocumentContent 
where len(FileStreamContent) <=1

This works without errors but the time taken to execute is really huge and directly proportional to the no of documents in the table also the size of the documents. Sometimes exceeds 30 seconds and started getting timeouts and eventually deadlocks. So modified as follows.

select DocumentContentID 

from DocumentContent 
where datalength(FileStreamContent) <=1

It rocks. Now the query takes normal time even if the table has many rows.