Why file stream?
Generally we will not store the unstructured data (Images, docs etc) in the databases. So we will keep unstructured data outside the database and we will only store the link to the unstructured data in the database. The difficulties we are facing due to this is,
- Database Management Complexity
- IF unstructured data is stored in the database, it will decrease the performance.
So Microsoft comes up with a new approach File stream to store the unstructured data.
What is FILESTREAM?
File stream integrates SQL Server Database Engine with NTFS file system by storing varbinary(max) bnary large object (BLOB) data as files on the File system. Win32 File system interfaces provide streaming access to the data. FILESTREAM uses NT Cache for caching file data. This will improve DB Engine performance. FILESTREAM will not use SQL SERVER buffer pool. So SQL SERVER memory will be free, so totally it will improve the performance.
Creating a Database with File stream:
/******Creating a database with FileStream ******/
CREATE DATABASE [Test] ON PRIMARY ( name = N'Test', filename =N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest.mdf', size = 2048kb, maxsize = unlimited, filegrowth = 1024kb ), filegroup [FSG]CONTAINS filestream DEFAULT ( name = N'FS', filename =N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATAFS' )LOG ON ( name = N'Test_log', filename =N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATATest_log.ldf', size = 1024kb, maxsize = 2048gb, filegrowth = 10%)GO
After running the above script we can see two files created in the location C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQL2008R2MSSQLDATA
- $FSLOG : Log of the Filestream
- Filestream.hdr : Header of the Filestream.
Creating a table and Inserting data into it.
--Tables with Filestream Columns must have non-null unique ROWGUID Column
CREATE TABLE dbo.images
(
id INT IDENTITY(1, 1),
ui UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
image VARBINARY(MAX) FILESTREAM NULL
)
--Inserting FileStream Data into tables
-- I had Inserted 3 rows, Only 1 is displaying here.
INSERT INTO [Test].[dbo].[Images]
([UI]
,[Image])
VALUES
(NEWID()
,CAST('' as varbinary(max)))
GO
Querying File Stream Data
FILESTREAM rows in the FILESTREAM location.
This is just an introductory post on FILESTREAM; there is lot of things in FILESTREAM like File Stream Partitioning, Garbage collection etc. I will update these in upcoming posts.