Handling NULL instances of varbinary(max) FILESTREAM columns

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