Processing SQL Server FILESTREAM Data: Part 3 - Creating Tables

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:

CREATE TABLE Notification.EmailMessages( 
    EmailMessageId        int            IDENTITY NOT NULL, 
    TransmitStatusId      int            NOT NULL, 
    SubmitDate            datetime       NOT NULL, 
    TransmitDate          datetime           NULL, 
    AttemptCount          int            NOT NULL, 
    FromAddress           varchar( 100 ) NOT NULL, 
    FromAlias             varchar( 100 )     NULL, 
    ToAddresses           varchar( 1000 ) NOT NULL, 
    CcAddresses           varchar( 1000 )    NULL, 
    BccAddresses          varchar( 1000 )    NULL, 
    Subject               varchar( 1000 )    NULL, 
    Body                  text               NULL, 
    timestamp             timestamp      NOT NULL, 
    CONSTRAINT PKEmailMessage 
        PRIMARY KEY( EmailMessageId ), 
    CONSTRAINT FK1EmailMessage 
        FOREIGN KEY( TransmitStatusId ) 
        REFERENCES Notification.TransmitStatus( TransmitStatusId ) 
) 
GO 

CREATE TABLE Notification.EmailAttachments( 
    EmailAttachmentId     int             IDENTITY NOT NULL, 
    EmailMessageId        int             NOT NULL, 
    AttachmentFileId      uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE, 
    SequenceNum           int             NOT NULL, 
    Filename              varchar( 1000 ) NOT NULL, 
    FileData              varbinary( max ) FILESTREAM NULL, 
    timestamp             timestamp       NOT NULL, 
    CONSTRAINT PKEmailAttachments 
        PRIMARY KEY( EmailAttachmentId ), 
    CONSTRAINT FK1EmailAttachments 
        FOREIGN KEY( EmailMessageId ) 
        REFERENCES Notification.EmailMessages( EmailMessageId ) 
) 
ON [PRIMARY] 
    FILESTREAM_ON FilestreamExampleFilegroup ]]

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'