Processing SQL Server FILESTREAM Data, Part 3 - Creating Tables

CodeProject In Parts 1 and 2 of this series I discussed my experience with the SQL Server FILESTREAM technology, specifically the background of the decision and setup of the SQL Server. In this installment I discuss the tables created and how I specified the FILESTREAM BLOB column.

Setting The Table

So after some struggle I had SQL Server ready to handle FILESTREAMS. What I needed now were the requisite tables to store the data. This is achieved by adding a column to a table and indicating that BLOB data will live there in a file that is stored on a FILESTREAM filegroup. Here are the tables I used for my email and attachments log:

Most of the columns in the EmailMessages table are fairly self-explanatory. The TransmitStatusId column is a reference into a simple lookup table with an integer ID and description that indicates what state the message is in, e.g. Queued, Transmitted, Failed, etc. As you can see in the EmailAttachments table there are two columns that are somewhat out of the ordinary, the AttachmentFileId and FileData columns. But I'll explain each column so you can understand my approach to this design.

• EmailAttachmentId - Monotonically increasing surrogate value to be used as a primary key. I prefer these to a GUID when a natural key is not handy but if you want to have a religious war about it there are plenty of places where the battle rages. Feel free to take it there;
• EmailMessageId - Parent key to the EmailMessages table;
• AttachmentFileId - This is a unique GUID identifier for the row, as signified by the ROWGUIDCOL indicator, necessary for the FILESTREAM feature to uniquely identify the data;
• SequenceNum - Indicates the listing sequence of the attachment, for later reporting purposes;
• Filename - Saves the original file name, since FILESTREAM will create generated file names, and I will want to recreate the file names later when I'm actually transmitting the file via SMTP;
• FileData - The binary column where the file data is stored, although the data is read and written on the operating system file storage not the SQL Server data file.
• timestamp - Yes, I still use timestamp files for concurrency. I'm an old-school kind of guy.

The last part of the CREATE TABLE statement for the EmailAttachments table is where you specify the filegroup on which the FILESTREAM data will be stored. This references the filegroup we created in Processing SQL Server FILESTREAM Data, Part 2 - The Setup. And with that, we're finally ready to start coding!

Next up - Processing SQL Server FILESTREAM Data, Part 4 - Readin' and Writin'