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

From:  https://www.mssqltips.com/sqlservertip/1850/using-insert-update-and-delete-to-manage-sql-server-filestream-data/ 

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 p_w_picpath 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 p_w_picpath file as shown below. You can also open the p_w_picpath using any of the available p_w_picpath 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 p_w_picpath 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.