Get records with empty binary items
There's a column Photo with type "Image" in MS SQL table (database – MS SQL Server 2008 R2). The task is to get all the records where Photo is empty. Values of empty images is "0x00000000".
To get the size of binary column in MS SQL you can using the operator DATALENGTH(ColumnName). For example,
The result for expression "DATALENGTH(Attachment)" for value "0x00000000" is equal to 4. That’s why to get all the records from table you should add DATALENGTH(ColumnName) to WHERE clause:
SELECT Name, Position, Department from Employees WHERE DATALENGTH(Photo) < 5