In previous lessons, we setup the server to handle FILESTREAM, then we looked at how to create or alter a database to work with FILESTREAM. In this lesson we will create a table to store a FILESTREAM, then insert a few rows into it.
Before we create our table, be aware of some requirements necessary for FILESTREAM to work. First, you must have a special column that FILESTREAM uses to uniquely identify the stream. It must be a unique non null identifier of type ROWGUIDCOL. If we specify a default of NEWSEQUENTIALID the column becomes self maintaining. When we insert a new value into the row, SQL Server takes care of creating a GUID for us and we essentially can ignore the column. Here is an example:
, FileStreamID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID()
, DocumentExtension VARCHAR(10)
, DocumentName VARCHAR(256)
Here the column “FileStreamID” will become the required column for FILESTREAM. Note the column name is not important, I could have called it “FSID”, “FSIdentity”, or even “Spock”. You’ll also note I created an ID column for our use in normal day to day operations. This is not a requirement of FILESTREAM, just good practice. There is a second requirement however. For the column that will be storing the documents, it must be VARBINARY(MAX), and it must add the FILESTREAM clause.
You will also note the default of “0x” (hex 0). This will be important if you wish to insert a new row without supplying the document at the time the row is created. It will create a file to act as a placeholder until such time as the real document is supplied.
You can also alter an existing table to add FILESTREAM capabilities. Simply use the ALTER TABLE command, add the unique identifier column (in this example “FileStreamID”) and the VARBINARY(MAX) column to hold your data (“Document” in the above example).
It’s time to insert some data. Normally the best way to insert data is using a client application, such as something written in .Net. It is possible to add documents though via T-SQL. To supply data for these examples, I decided a little culture was in order. I went to http://shakespeare.mit.edu/ and copied some of the plays into Microsoft Word documents. Note I used the older “.doc” format from 2003, not the 2007 “.docx”. This is not particularly important as far as FILESTREAM is concerned, but will come into play in later lessons when we look at doing Full Text Searches on this data.
'doc' AS DocumentExtension
, 'Hamlet.doc' AS DocumentName
, * FROM OPENROWSET(BULK 'D:\Docs\Hamlet.doc', SINGLE_BLOB)
Here we’ve inserted a new row into the table, and have ignored the “ID” and “FileStreamID” letting SQL Server create the values. The “DocumentExtension” and “DocumentName” columns are straightforward. To supply the document however, we need to use an OPENROWSET. This will supply the data from a disk file as a single BLOB (Binary Large OBject).
Let’s verify what was just inserted with this query:
, DocumentExtension AS Ext
Notice the CAST, its necessary in order to get a view into the stored document.
|1||A54A8FFE-F742-DE11-AC61-002243CE0AAB||doc||Hamlet.doc||ÐÏà¡±á > þÿ|
As mentioned previously, it is also possible to insert a new row, then add the document later. To insert a new row into our example table, we simply use:
VALUES ('doc', 'AMidsummerNightsDream.doc');
Now that the row exists, we can update it.
SINGLE_BLOB) AS TheDocument
WHERE ID = 2 ;
While it is possible to add data in this format, it’s fairly rare. The majority of the time you will want to use a client side application, which is what we’ll look at in the next installment of this series.