Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Using INSERT, UPDATE and DELETE to manage SQL Server FILESTREAM Data

MSSQLTips author Ashish Kumar Mehta By:   |   Read Comments (2)   |   Related Tips: More > FILESTREAM

Problem:
One of the Junior SQL Server DBAs in my company approached me yesterday with a dilemma. He wanted to know how he can create a FILESTREAM enabled database and how to use different DML statements such as INSERT, UPDATE, DELETE and SELECT against a SQL Server 2008 FILESTREAM enabled database. In this tip you will see how database administrators can quickly create a FILESTREAM enabled database and how to use DML statements to manage the data.

Solution:
In SQL Server 2008 one can store BLOBs (e.g. Images, Video, Word, Excel, PDF, MP3, etc) in the NTFS file system rather than in a database file. This can be done by using the new FILESTREAM feature which was introduced in SQL Server 2008. There are different ways in which a DBA can enable the FILESTREAM feature, to know how you can refer to my previous tip titled Different ways to enable FILESTREAM feature of SQL Server 2008. However, in order to enable the FILESTREAM feature you need to be a member of SYSADMIN or SERVERADMIN fixed server role.


Creating a FILESTREAM Enabled Database

Let us first create a FILESTREAM enabled database namely FileStreamDB using the T-SQL below:

Create a FileStream database in SQL Server 2008

Use Master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'FileStreamDB')
DROP DATABASE FileStreamDB
GO

USE master
GO

-- Create FileStreamDB Database
CREATE DATABASE [FileStreamDB] ON PRIMARY
( NAME = N'FileStreamDB', FILENAME = N'D:\FileStreamDB\FileStreamDB.mdf' ,
SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% )
LOG ON
( NAME = N'FileStreamDB_log', FILENAME = N'D:\FileStreamDB\FileStreamDB_log.ldf' ,
SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO

ALTER DATABASE [FileStreamDB]
ADD FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM
GO

ALTER DATABASE [FileStreamDB]
ADD FILE (NAME = N'FileStreamDB_FSData', FILENAME = N'D:\FileStreamDB\FileStreamData')
TO FILEGROUP FileStreamGroup
GO

While creating a FILESTREAM enabled database; the DBA needs to specify CONTAINS FILESTREAM clause for at least one of the FILEGROUPs within the create database statement.

The snippet below shows the properties of the sample FileStreamDB database which was created with the above TSQL statements.

 

The below snippet shows all the files which were created within the D:\FileStreamDB folder when the database was created. All the FILESTREAM related data gets stored in FileStreamData folder which is also known as FILESTREAM Data Container.

 

The filestream.hdr is a very important system file which basically contains FILESTREAM header information. Database Administrators need to make sure that this file is not removed or modified by any chance as this will corrupt the FILESTREAM enabled database.

 


Creating a table with FILESTREAM columns to store FILESTREAM Data

In order to store a BLOB using the FILESTREAM feature, you must have a column of datatype VARBINARY(MAX) along with the FILESTREAM attribute. In addition, the table must have a UNIQUEIDENTIFIER column with the ROWGUIDCOL attribute. Keep in mind that in SQL Server 2008 the VARBINARY(MAX) datatype can store more than 2 GB of data.

Execute the TSQL query below to create the FileStreamDataStorage table.

Create a table with FileStream data

Use FileStreamDB
GO
CREATE TABLE [FileStreamDataStorage]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[FileStreamData] VARBINARY(MAX) FILESTREAM NULL,
[FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
[DateTime] DATETIME DEFAULT GETDATE()
)
ON [PRIMARY]
FILESTREAM_ON FileStreamGroup
GO

Inserting FILESTREAM Data to FileStreamDataStorage Table

Let's add a row to the FileStreamDataStorage table by execute the T-SQL below. In this example, we will insert Image1.JPG which is stored in the "C:\SampleFiles" folder. Before you run the below code, make sure you have changed the name of the file (C:\SampleFiles\Image1.JPG) to a valid image file which exists on your computer.

INSERT FileStream Data

Use FileStreamDB
GO
INSERT INTO [FileStreamDataStorage] (FileStreamData)
SELECT * FROM
OPENROWSET(BULK N'C:\SampleFiles\Image1.JPG' ,SINGLE_BLOB) AS Document
GO

Once the statement above has executed successfully, you should see a new folder under "D:\FileStreamDB\FileStreamData". You can open up all the sub folders to find the image file as shown below. You can also open the image using any of the available image viewers directly from the location.


Retrieve FILESTREAM Data from FileStreamDataStorage Table

Even though the FILESTREAM data is stored in the NT File system, you can retrieve the FILESTREAM data from FileStreamDataStorage table by executing the below mentioned TSQL.

SELECT FileStream Data

USE FileStreamDB
GO
SELECT ID
, CAST([FileStreamData] AS VARCHAR) as [FileStreamData]
, FileStreamDataGUID
, [DateTime]
FROM [FileStreamDataStorage]
GO


Updating FILESTREAM Data stored in FileStreamDataStorage Table

You can update a row in the FileStreamDataStorage table by execute the T-SQL below.  In the example below, the existing Image1.JPG with be updated with a new Image2.JPG which is located in C:\SampleFiles folder. Before you run the below code, make sure you have changed the name of the file (C:\SampleFiles\Image2.JPG) to a valid image file which exists on your computer.

UPDATE FileStream Data

USE FileStreamDB
GO
UPDATE [FileStreamDataStorage]
SET [FileStreamData] = (SELECT *
FROM OPENROWSET(
BULK 'C:\SampleFiles\Image2.JPG',
SINGLE_BLOB) AS Document)
WHERE ID = 1
GO

Once a row is update in a table which has FILESTREAM enabled, the row will be get updated immediately within the table and the new FILESTREAM data file will be available within the FILESTREAM Data Container. However, there are scenarios when both the original FILESTREAM data and the new FILESTREAM data will remain within the FILESTREAM Data Container. This generally happens as FILESTREAM Garbage Collector process has not run once the row was updated. The old FILESTREAM data will be removed from FILESTREAM data container once the next CHECKPOINT occurs and the garbage collector process has completed successfully. If you want to trigger the FILESTREAM Garbage Collector thread EXPLICIT, then execute the CHECKPOINT command manually.


Deleting FILESTREAM Data stored in FileStreamDataStorage Table

You can delete a row from FileStreamDataStorage table, by executing the T-SQL below.

DELETE FileStream Data

USE FileStreamDB
GO
DELETE [FileStreamDataStorage]
WHERE ID = 1
GO

Once a row is deleted from a table which has FILESTREAM enabled, the row will be removed from the table immediately. However, the FILESTREAM data file will be removed from the FILESTREAM Data Container only when the FILESTREAM Garbage Collector process has completed. The FILESTREAM Garbage Collector generally happens when the next CHECKPOINT occurs and the garbage collector process has run. This is the only reason why you will sometime see the FILESTREAM data which was deleted still in the FILESTREAM Data Container. If you want to trigger the FILESTREAM Garbage Collector thread EXPLICIT, then execute the CHECKPOINT command manually.

Next Steps:



Last Update: 10/2/2009


About the author
MSSQLTips author Ashish Kumar Mehta
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, December 16, 2009 - 9:14:16 AM - Dr DBA Read The Tip

 Hello,

 Very nice article. It is well written and gets to the point quickly. Please keep up the great work.

 

I have a question regard FILESTREAM data, once you have stored a file (image or doc) using this new feature, how do you read make the file? In the case of the artile you saved an image and show how to retrieve it in T-SQL but I don't see where you would store this file again so that you can open the image.

This new feature is great and I believe it allows Sharepoint 2010 to store data in NTFS.

 Thanks.

 


Monday, December 21, 2009 - 6:26:55 AM - Ashish Kumar Mehta Read The Tip

Hello,

Nice to know that you found this tip very useful.

If you want to reterive the FILESTREAM data, then you can use ASP.NET. A sample code is available in http://msdn.microsoft.com/en-us/library/cc645940.aspx link.

Thanks
Ashish Kumar Mehta

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.