In this series of the SQL Server FILESTREAM (see TOC at bottom), We have gone through various aspects of this feature to store large size objects into the file systems.

In the previous SQL Server FILESTREAM articles, we have covered the following benefits from this feature:

• The benefit of NTFS data streaming
• Less overhead on SQL Server for the large BLOB objects
• Backup includes the metadata along with the FILESTREAM container data
• FILESTREAM also allows Point-in-time restore for the database
• FILESTREAM provides the transactional consistency also which is the primary requirement of any database

Now let’s review a few more features of SQL Server FILESTREAM.

FILESTREAM database Container

We cannot use the FILESTREAM container for another database. This restriction also applies to the subfolder of the FILESTREAM container. In the previous article, we used the file system ‘ C:\sqlshack\Demo’ for the sample database.

Checking whether FILESTREAM is enabled or not in database

We can check whether the SQL Server FILESTREAM feature at database level using the filegroup.

The SQL Server FILESTREAM filegroup type is ‘FD’ therefore we can check the property using the above command, or we can use the print statement to give the output.

If I run this query on the FILESTREAM database, we get the below output.

Otherwise, we get the output below.

Checking whether the table can hold FILESTREAM data using SQL Server FILESTREAM feature

We must have a UNIQUEIDENTIFIER column with ROWGUIDCOL property in the table to hold the FILESTREAM data. You can check this using join system tables and views.

We have included the table name in this query in which SQL Server FILESTREAM data needs to be added.

If the table does not meet the requirements, we get the following output:

Modifying existing VARBINARY(MAX) column to hold FILESTREAM data

We might have a requirement to change the existing Varbinary(max) column to a FILESTREAM column. It might be due to the old versions of SQL Server (older than 2008) does not support FILESTREAM. Therefore, if we want to convert the existing column to FILESTREAM, we require to copy the data (objects) into the file system container. We cannot directly modify the table property to reflect these changes.

Suppose we have a table ‘customers’, which also holds customer images into the varbinary(Max) column.

We need to perform the below steps to change this.

• First, add a new varbinary (Max) column with FILESTREAM property: Use the below code to add this column.
  
  1	ALTER TABLE [dbo].[customers] ADD Image_temp VARBINARY(MAX) FILESTREAM NULL

• Copy data: We need to copy data from the existing column to the new FILESTREAM column. It generates the file in the FILESTREAM container for each update.
  
  1	UPDATE [dbo].[customers] SET  ItemImage_New = Imagedata

• Drop old VARBINARY(MAX) column: Once we have copied the data into the FILESTREAM container, we can drop the old VARBINARY(MAX) column using below query.
  
  1	ALTER TABLE [dbo].[Items]  DROP COLUMN ItemImage

• Rename the column: In this last step, we can rename the new column name to the original name so that we do not need to modify any dependent objects on it.
  
  1 2 3 4

  EXECUTE sp_rename       @objname = '[dbo].[customers].ItemImage_New ',   @newname = 'Imagedata',       @objtype = 'COLUMN'

Identifying the SQL Server FILESTREAM filegroup name using the table name

We might want to know the FILESTREAM filegroup name in which the particular table belongs. We can do it using the join condition between catalog view sys.data_spaces and sys.tables. The catalog view (sys.data_spaces) shows the row for each data space such as filegroup, partition.

Now join this catalog view with sys.tables to get the detail of filegroup belonging to a table.

In the above image, we can see that the table belongs to ‘DemoFileStreamFILESTREAM filegroup.

Triggers for SQL Server FILESTREAM tables

We can create the DML triggers for the FILESTREAM columns as well in the table. Suppose we want to create a trigger for the update or insert activity notification any insert occurs in the FILESTREAM table using the AFTER INSERT trigger.

Execute the below code to create an AFTER INSERT trigger.

Now insert any FILESTREAM object in the table.

In the output, we get the message about the size of the inserted object.

Similarly, we can create AFTER UPDATE trigger and display the required message. For example, in the trigger below, we defined a message to print for every update in the FILESTREAM object.

Now, we are going to replace the existing file with a new file so the trigger will give the updated size of the FILESTREAM object.

Once the update completes for the FILESTREAM table, we get the below message printed.

Multiple FILESTREAM filegroups

We might be using the FILESTREAM for a large number of objects and might be big files such as videos. In the database, we usually create multiple secondary data files on different disks to split the load from a single disk or data store. The question may arise here whether SQL Server allows creating multiple FILESTREAM filegroups. We can create multiple FILESTREAM filegroups due to the disk space limitation. We can also create multiple filegroups to get the benefits of the multiple disks IO read and write latency and performance.

You can notice in the below that we created the multiple FILESTREAM filegroups.

Execute the above code and open the database properties from SSMS. In the ‘Filegroups’ page, you can see two FILESTREAM filegroups and defined ‘FILESTREAM_1’ as default FILESTREAM filegroup.

In the database files, we created multiple FILESTREAM files in the different filegroups.

We have multiple FILESTREAM filegroups in this database. Therefore, we need to define the FILESTREAM filegroup while creating the table. If we do not specify any filegroup, it uses the default FILESTREAM filegroup (in this example FILESTREAM_1) to create the object.

Let us create the tables to use the default FILESTREAM filegroup and specific FILESTREAM filegroup.

• Default filegroup: in the below command, we did not specify any FILESTREAM filegroup.
  
  1 2 3 4 5

  CREATE TABLE [DemoFileStreamTable_1] ( [FileId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, [FileName] VARCHAR (25), [File] VARBINARY (MAX) FILESTREAM); GO

  In the below image, we can see SQL Server created the table in the default FILESTREAM filegroup.

  

• Object creation in specific FILESTREAM filegroup: In the below code, we instructed SQL Server to create the table in the’ FILESTREAM_2′ filegroup.
  1 2 3 4 5 6

  CREATE TABLE [DemoFileStreamTable_2] ( [FileId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE, [FileName] VARCHAR (25), [File] VARBINARY (MAX) FILESTREAM) FILESTREAM_ON FILESTREAM_2 GO

  

Conclusion

In this article, we explored various aspects the SQL Server FILESTREAM feature available from SQL Server 2008 onwards. I will continue writing on this topic to complete this series. Stay tuned for the next article.

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023