FileStream in SQL Server 2008+

One of my friends is working on one project which used tostore images and some document files. He is using BLOB fields to get this taskdone, when he was discussing this with me, I suggested him to use FileStreamfeature instead of the way he is using currently. There are some reasons forthat and I am going to explore it in this article.
In SQL Server 2005, we had two option to deal with binarydata (image, document etc.).
1.)    Storeimages/documents in OS’ file system and keep pointer (path) of thatimages/documents into database table
2.)    Use BLOB fields to store images/documents directly in database table.
So far we used to take any of the above approaches but bothhave their own limitations. In first approach, transactional consistency is theissue, when you take backup of database, it won’t get backup of the folderswhere we are keeping our files (images/documents). In second approach, you canstore binary data directly in SQL Server database table so whenever you getdatabase backup, it will backed up by its own but it affects performance whileconverting that binary data from database table itself, other than that, BLOB field has limitation of 2GB.
To overcome these limitations, Microsoft developer teamprovided very cool feature, named “Filestream” in SQL Server 2008. With help of“Filestream”, you can store images/documents/videos directly in windows NTFSfile system, it has no limitations of 2GB like BLOB and when you take backup ofdatabase, your Filestream data will be backed up by its own. Apart from that,you can get advantage of NTFS streaming APIs for efficient and performancedriven file operation.
To create Filestream enable column in your table, you haveto have a file group in your database which is enable for “FileStream” and youwill also need one column in your table which should have varbinary(max) data typeso that images/documents/videos could be stored there.
Even before doing any of the above things, you have toenable “Filestream” in your SQL Server which is by default disable.
USE MASTER
GO
–if file streamis not already enable in your server
–look atfollowing link to enable it.
EXEC sp_configure filestream_access_level, 2
–why I haveused 2? know it from below link.
—-http://technet.microsoft.com/en-us/library/cc645956.aspx
RECONFIGURE
GO
CREATE DATABASE SQLHubFileStream ONPRIMARY
(
      NAME = SQLHubFileStream_data,
      FILENAME = N’D:\TestDB\SQLHubFileStream_data.mdf’
),
FILEGROUPSQLHubFileStream_FS CONTAINS FILESTREAM
(
      NAME =SQLHubFileStream_FILESTREAM,
      FILENAME = N’D:\TestDB\SQLHubFileStream_FS’
)
 LOG ON
(
      NAME = SQLHubFileStream_LOG,
      FILENAME = N’D:\TestDB\SQLHubFileStream_log.ldf’
);
GO
USESQLHubFileStream
GO
CREATE TABLE Customers
(
      ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
      Name varchar(25),
      CustPhoto VARBINARY(MAX) FILESTREAM
)
GO
–I have alreadykept “Ritesh-Teerth.JPG” file in my D drive,
–you have toprovide your own path and file name here.
INSERT INTO Customers
VALUES
(NEWID(),‘Ritesh Shah’,(select * FROM OPENROWSET(BULK ‘D:\Ritesh-Teerth.JPG’, SINGLE_BLOB) AS img))
GO
select * from Customers
go
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

FileStream in SQL Server 2008+

One of my friends is working on one project which used tostore images and some document files. He is using BLOB fields to get this taskdone, when he was discussing this with me, I suggested him to use FileStreamfeature instead of the way he is using currently. There are some reasons forthat and I am going to explore it in this article.
In SQL Server 2005, we had two option to deal with binarydata (image, document etc.).
1.)    Storeimages/documents in OS’ file system and keep pointer (path) of thatimages/documents into database table
2.)    Use BLOB fields to store images/documents directly in database table.
So far we used to take any of the above approaches but bothhave their own limitations. In first approach, transactional consistency is theissue, when you take backup of database, it won’t get backup of the folderswhere we are keeping our files (images/documents). In second approach, you canstore binary data directly in SQL Server database table so whenever you getdatabase backup, it will backed up by its own but it affects performance whileconverting that binary data from database table itself, other than that, BLOB field has limitation of 2GB.
To overcome these limitations, Microsoft developer teamprovided very cool feature, named “Filestream” in SQL Server 2008. With help of“Filestream”, you can store images/documents/videos directly in windows NTFSfile system, it has no limitations of 2GB like BLOB and when you take backup ofdatabase, your Filestream data will be backed up by its own. Apart from that,you can get advantage of NTFS streaming APIs for efficient and performancedriven file operation.
To create Filestream enable column in your table, you haveto have a file group in your database which is enable for “FileStream” and youwill also need one column in your table which should have varbinary(max) data typeso that images/documents/videos could be stored there.
Even before doing any of the above things, you have toenable “Filestream” in your SQL Server which is by default disable.
USE MASTER
GO
–if file streamis not already enable in your server
–look atfollowing link to enable it.
EXEC sp_configure filestream_access_level, 2
–why I haveused 2? know it from below link.
—-http://technet.microsoft.com/en-us/library/cc645956.aspx
RECONFIGURE
GO
CREATE DATABASE SQLHubFileStream ONPRIMARY
(
      NAME = SQLHubFileStream_data,
      FILENAME = N’D:\TestDB\SQLHubFileStream_data.mdf’
),
FILEGROUPSQLHubFileStream_FS CONTAINS FILESTREAM
(
      NAME =SQLHubFileStream_FILESTREAM,
      FILENAME = N’D:\TestDB\SQLHubFileStream_FS’
)
 LOG ON
(
      NAME = SQLHubFileStream_LOG,
      FILENAME = N’D:\TestDB\SQLHubFileStream_log.ldf’
);
GO
USESQLHubFileStream
GO
CREATE TABLE Customers
(
      ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
      Name varchar(25),
      CustPhoto VARBINARY(MAX) FILESTREAM
)
GO
–I have alreadykept “Ritesh-Teerth.JPG” file in my D drive,
–you have toprovide your own path and file name here.
INSERT INTO Customers
VALUES
(NEWID(),‘Ritesh Shah’,(select * FROM OPENROWSET(BULK ‘D:\Ritesh-Teerth.JPG’, SINGLE_BLOB) AS img))
GO
select * from Customers
go
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile