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 defThe initial query used to determine the list of blank files is
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.