Freigeben über

Handling NULL instances of varbinary(max) FILESTREAM columns

  • Artikel

The table structure I proposed in my last post used the following column definition for my FILESTREAM column:

CREATE TABLE [dbo].[eventMedia] (
...
    [file] [varbinary](max) FILESTREAM);

So by default I am allowing NULL's to be stored in the file column.

Zach and I changed the design a bit to the following:

CREATE TABLE [dbo].[eventMedia] (
...
    [file] [varbinary](max) FILESTREAM DEFAULT(0x));

So by default we are initializing every file instance to an empty binary string. The net effect of this change is a zero-length file will get created in the file system when new rows get inserted into the eventMedia table.  So why did we do this?

Without stealing Zach's thunder, you can't open up a handle to an instance if the column is NULL.  If you can't open up a handle to it, that means you can't write to it either, which will result in a dead-end.   So all you DBA's creating FILESTREAM columns take note!

- Roger

Comments

  • Anonymous
    October 18, 2007
    Don't worry about stealing my thunder...  You're the one who figured it out!

  • Anonymous
    October 18, 2007
    PingBack from http://www.artofbam.com/wordpress/?p=10280

  • Anonymous
    October 14, 2014
    I realize this is SUPER old, but I need a quick method to find out which of my rows have an empty FILESTREAM column. Judging by how long my query ( [File] IS NULL ) is running, I think SQL SERVER is checking the actual file in NTFS, rather than some other form of "knowing" it's empty. Any thoughts? My table has less than 17K rows, which is small for SQL access for large for NTFS access.