PART 1 of the guide to SQL Server FileStream Types provided you with the steps necessary to enable SQL FileStream Types in your SQL Server instances. In this Part 2 article I will explain how you setup a Database to use SQL FileStreams, how to create a Table with the FileStream Type attribute. And how to Insert and Select Filestream data…
Setup the SQL Server Instance
A) The first thing you need to do is turn on FileStreams on the DATABASE level.
Simply right-click your SQL Server instance and go to Properties
B) Click on the Advanced tab and the first item on the right is Filestream Access Level. Select “Full access enabled” and hit OK.
You will then be prompted to restart your SQL Server instance:
C) Go back into your SQL Server Configuration Manager and select Restart:
Once that is done, your SQL Server Instance is now ready for FileStream Types on any Database you choose
Setup the Database (FileGroups and Files)
D) If you haven’t already, create a new Database in SQL Server and set up the security for access. Once that is done go to the Properties of your Database (I have used the Database nullableCode in the example below).
E) Go to the Filegroups tab, and add a new Filegroup as shown below. I have called mine NC_FS.
A Filegroup is what SQL Server uses to categorize files associated with a database.
For example: You have a FileGroup for RowData storage files. You have a FileGroup for Log storage files. And now we have added a NC_FS (nullableCode filestream) filegroup aswell.
F) The final step you need to do it setup the FileStream storage file.
Go to the File tab in the Property Window of your Database Table. You will see that there are already two existing Files associated with the database: One is the RowData (*.MDF) file and the other is the Log (*.LDF) file.
Hit the ADD button at the bottom. And a new row will be created.
1) Logical Name: Type in a unique name for the new file. I have called mine nullableCode_FS
2) File Type: Select FileStream Data. This tells SQL Server this file will be used to store FileStream Type data
3) Filegroup: Select the filegroup you created in Step [E].
4) Path: Enter the path of where the FileStream data will be stored. If you enabled Client Access to FileStream types in Part1 of this guide, this is the Windows Folder where all the FileStream files will be created and stored.
That’s it!! Once that is done, you’re all set to start creating Database Tables with the FileStream attribute.
Creating a Database Table with FileStream Types
G) The next thing you should do is create a Database Table.
Below is the SQL that will create an Attachment table with an ID column, FileName column and the all important FileData column. Along with this, there are also some audit fields created.
CREATE TABLE [dbo].[Attachment]( [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL PRIMARY KEY, [FileName] [nvarchar](255) NOT NULL, [FileData] [varbinary](max) FILESTREAM NOT NULL, [UpdatedDateTime] [datetime2](7) NOT NULL, [UpdatedBy] [int] NOT NULL, [RowVersion] [timestamp] NOT NULL ) ON [PRIMARY] FILESTREAM_ON [NC_FS]
Some points of interest:
- A Guid / UniqueIdentifier type is used for the Primary Key for this table.
- The RowGuidCol attribute is also applied to this Primary Key column.
- The column that will hold the FileStream Type is of type VARBINARY(MAX).
- The column that will hold the FileStream Type has the FILESTREAM attribute applied to it.
H) A simple SELECT query is the following:
SELECT FileData.PathName() AS FilePath , ID , FileName FROM Attachment
Some points of interest:
- Notice that in my SELECT Query I didn’t include the FileData column. There is a big performance hit when you run SELECT Queries either via TSQL or Linq that ‘peeks’ into the FileData (FileStream Type) column. This is intensified when you have files larger than 50MB. So please be careful when querying a Database Table with FileStreams. If you accidentally return this FileStream column, you may notice bad response times down the track.
Exploring where the FileStream data is stored
I) Back in Step [F] you added an extra File type to your Database. In that same step you also specified where this new File data will be stored.
If you navigate to the Windows Folder location, you will see some child folders with a GUID folder name:
- nullableCode_FS:This is the filename we provided in Step [F] when adding the new data File to the Database
- 51381f0b-xxxxxxx: This is the unique id for the Attachment Database Table
- 197ba05b-xxxxxx: This is the unique id for the FileData Column
- Inside the 197ba05b-xxxxxx Windows Folder is where the file data will be stored
Inserting FileStream Data using T-SQL
J) The SQL Query below is an example on how to Insert FileStream data:
--insert data DECLARE @img as varbinary(max) SELECT @img = CAST(bulkcolumn as varbinary(max)) FROM OPENROWSET( BULK 'C:!Projects!Test Filesimage1.png', SINGLE_BLOB) as x INSERT INTO Attachment(ID, FileName, FileData, UpdatedDateTime, UpdatedBy) SELECT NEWID(), 'image1.png', @img, GETDATE(), 1
- Declare a VARBINARY(Max) variable that will hold the file data.
- Read the file and store this in the @img variable using the OPENROWSET and BULK commands
- Insert a new Attachment record, passing the relevant fields: Passing in @img to the FileData column
That’s it!! Once that is done, a new record should be inserted into the Attachment table.
Testing the FileStream data via Windows
K) To check that the file is uploaded correctly you can navigate to the Windows Folder specified in Step [I] and check for yourself.
Below is the folder for nullableCode_FS -> Attachment Table -> FileData Column:
Because I know this is a *.PNG image, I can simply open up MSPAINT and drag my file to check its content and ensure that the INSERT has succeeded!!
Next up in this Guide to SQL FileStream Types I will show how to Upload and Download SQL FileStream Types using .NET – as well as transferring FileStream Types across Silverlight pages to Domain Services via Generic Handlers…
Hope this article has helped explain how you can setup your SQL Server instace for FileStream data types.