Rajendra Gupta

SQL Server FILESTREAM queries and Filegroups

February 1, 2019 by

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

No 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.

Database Name

FILESTREAM Container

(file system location)

Remarks

FileStreamDemodatabase_test

C:\sqlshack\Demo

It works fine.

FileStreamDemodatabase_test _New

C:\sqlshack\Demo

Error: Since other database is using this location.

FileStreamDemodatabase_test _New

C:\sqlshack\Demo\New

Error: we cannot use the child folder as well. In this case, the parent folder is being used by the FILESTREAM database ‘DemoSQL.’

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.
  • 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.
  • 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.
  • 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.

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.

    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.

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

FILESTREAM in SQL Server
Managing data with SQL Server FILESTREAM tables
SQL Server FILESTREAM Database backup overview
Restoring a SQL Server FILESTREAM enabled database
SQL Server FILESTREAM database recovery scenarios
Working with SQL Server FILESTREAM – Adding columns and moving databases
SQL Server FILESTREAM internals overview
Importing SQL Server FILESTREAM data with SSIS packages
SQL Server FILESTREAM queries and Filegroups
Viewing SQL Server FILESTREAM data with SSRS
SQL Server FILESTREAM Database Corruption and Remediation
Export SQL Server FILESTREAM Objects with PowerShell and SSIS

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
101 Views