In this article, we will see how to work with FileStream in SQL Server 2008 for storing unstructured data like documents and images on file system. Many a times, we always create unstructured data like Images, Documents and Video files. This data is often stored separately outside the database, which depending on your application requirements may increases complexities.
FileStream integrates the SQL Server database engine with an NTFS file system. We can achieve this using VARBINARY (MAX) on the file system. To reduce the effect of the FileStream data on database engine, FileStream uses NT system cache for caching file data and the buffer pool memory is used by the query processor.
If your data is more than 1 MB or if fast access to data is important to your application, then you should use the FileStream object. If the data is less than 1 MB, then it is always better to use VARBINARY (MAX) in the database.
Check if FileStream is Enabled
To start using the FileStream object, you should check whether the FileStream on the instance of SQL Server Database engine is enabled or not. To do so, go to Start > Microsoft SQL Server 2008 > Configuration Tool and click on SQL Server Configuration Manager.
Now right click the instance of SQL Server and go to properties to check whether FileStream is enabled or not. Click on FileStream tab and it should look like below –
Now go to SQL Server Management Studio, and execute the stored procedure shown below to enable or disable the FileStream –
EXEC sp_configure filestream_access_level, 2 RECONFIGURE
Filestream_access_level has three options –
- 0 – Disable FileStream support for this instance.
- 1 – Enable FileStream support for T-SQL access.
- 2 – Enable FileStream support T-SQL and Win32 streaming access.
CREATE DATABASE CustomerProfiles
PRIMARY ( NAME = CSPKFG,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CustProfile.mdf'),
FILEGROUP CPFileStreamGroup CONTAINS FILESTREAM( NAME = CustDoc,
FILENAME = 'c:\FSData\custdocs')
LOG ON ( NAME = custlog,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\CustProfile.ldf')
Now create a table which will store the Customer Profile in document format –
and add the following row to the table –
INSERT INTO CustomerDocuments VALUES (newid (),1,'Pravinkumar', NULL);
When you insert a null value in a FileStream column, the database engine does not create a file on the file system.
INSERT INTO CustomerDocuments
VALUES (newid (),2,'Yash',CAST ('' as varbinary(max)));
The above insert statement will insert a Zero length record.
INSERT INTO CustomerDocuments
VALUES (newid (),3,'Yash',CAST ('Sample Data' as varbinary(max)));
The above insert will create a file on the file system. If you now fire a select statement as shown below –
SELECT * FROM CustomerDocuments
The output is shown here–
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|