As you might have already know that we can use DBMSes to store any sort of data weather its simple texts, special texts, documents, photos, movies … anything … and as I said earlier, its good to store data in DBMS as it complies with ACID property.
Today’s post is about a feature that was first included in SQL Server 2008, it was really difficult to store large objects (vlob, blob, lob). Though clever SQL users have found couple of work arounds like, storing data as a image or varbinary data types but then it was very difficult to manage data and significantly reduce performance of query. Another way was to store file in file system and just store paths pointing towards those files in sql server, but again this feature was lacking securing and consistency (and thus violating ACID compliance) … So, finally Microsoft came with really cool solution that was hybrid of both above mentioned method. It allowed users to store LOB files in file system still gave enough security and consistency to stored file … it is called Filestream !!
By default Filestream is not enabled in SQL Server. So, it has to be enabled first. By using following query,
1: EXEC sp_configure filestream_access_level, 2
2: GO
3: RECONFIGURE
To get more details about options better go to MSDN Help.
Next step is to create a sample database with sample table to work with.
1: CREATE DATABASE [FileStreamDB] ON PRIMARY
2: ( NAME = N'FileStreamDB',FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf',
3: SIZE = 100MB , MAXSIZE = 500 MB, FILEGROWTH = 100 Mb )
4: LOG ON
5: ( NAME = N'FileStreamDB_log',
6: FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf',
7: SIZE = 50MB , MAXSIZE = 200 MB , FILEGROWTH = 50 Mb)
8: GO
9:
10: -- Add file group to work with Filestream
11: ALTER DATABASE [FileStreamDB]
12: ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM
13: GO
14:
15: -- Add file to filegroup (NOTE: its just location not filename !!)
16: ALTER DATABASE [FileStreamDB]
17: ADD FILE (NAME = N'FileStreamDB_FSData',
18: FILENAME = N'C:\FileStreamDB\FileStreamData')
19: TO FILEGROUP FileStreamGroup
20: GO
21:
22: sp_helpdb filestreamdb
And now, create a test table to work with it. Here I am using a table which has an Identity column, Filestream column, column to generate unique guid for filestream data and datetime. And the script is as follows,
1: Use FileStreamDB
2: GO
3:
4: CREATE TABLE [FileStreamDataStorage]
5: (
6: [ID] [INT] IDENTITY(1,1) NOT NULL,
7: [FileStreamData] VARBINARY(MAX) FILESTREAM NULL,
8: [FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL
9: NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
10: [DateTime] DATETIME DEFAULT GETDATE()
11: )
12: ON [PRIMARY]
13: FILESTREAM_ON FileStreamGroup
14: GO
Finally, so test … we will insert a word file …
1: Use FileStreamDB
2: GO
3:
4: INSERT INTO [FileStreamDataStorage] (FileStreamData)
5: SELECT * FROM
6: OPENROWSET(BULK N'C:\1\test.doc' ,SINGLE_BLOB)
7: AS Document
8: GO
And the file can be selected as,
1: USE FileStreamDB
2: GO
3: SELECT ID
4: , CAST([FileStreamData] AS VARCHAR) as [FileStreamData]
5: , FileStreamDataGUID
6: , [DateTime]
7: FROM [FileStreamDataStorage]
8: GO
Don’t get scared if you some cryptic words in place of your real file, as it is now converted into something that SQL Server likes. But if you try to manually copy that file and paste it to another location and rename it with its original extension it will work just fine 
!!! Now, to delete and update script is just as follows,
1: -- Updating file
2: USE FileStreamDB
3: GO
4:
5: UPDATE [FileStreamDataStorage]
6: SET [FileStreamData] = (SELECT *
7: FROM OPENROWSET(
8: BULK 'C:\1\fs_test.pdf',
9: SINGLE_BLOB) AS Document)
10: WHERE ID = 1
11:
12:
13: --- Deleting File
14: USE FileStreamDB
15: GO
16:
17: DELETE [FileStreamDataStorage]
18: WHERE ID = 1
19: GO
And this concludes this tutorial of “How to use Filestream to store data in SQL Server”. And if you want to try on your machine, you can download whole script. Or if you think my explanation of FileStream too lame (I don’t blame you for this one) then go ahead and read more in MSDN.
Also, I have uploaded am working on front end application in ASP.NET and IIS with SQL Server which can be used to upload / download documents stored in database using filestream. And will soon finish it.
Thanks for reading,
It’s Just a Thought … 