SQL Server FILESTREAM查询和文件组

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.

在本系列SQL Server FILESTREAM（请参见底部的TOC）中，我们介绍了此功能的各个方面，以将大型对象存储到文件系统中。

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

在以前SQL Server FILESTREAM文章中，我们介绍了此功能的以下优点：

• The benefit of NTFS data streaming
  NTFS数据流的好处
• Less overhead on SQL Server for the large BLOB objects
  大型BLOB对象在SQL Server上的开销更少
• Backup includes the metadata along with the FILESTREAM container data
  备份包括元数据以及FILESTREAM容器数据
• FILESTREAM also allows Point-in-time restore for the database
  FILESTREAM还允许对数据库进行时间点还原
• FILESTREAM provides the transactional consistency also which is the primary requirement of any database
  FILESTREAM还提供事务一致性，这也是任何数据库的主要要求

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

现在，让我们回顾一下SQL Server FILESTREAM的其他功能。

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.

我们不能将FILESTREAM容器用于另一个数据库。 此限制也适用于FILESTREAM容器的子文件夹。 在上一篇文章中，我们将文件系统'C：\ sqlshack \ Demo'用于示例数据库。

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

数据库名称	FILESTREAM容器 （文件系统位置）	备注
FileStreamDemodatabase_test	C：\ sqlshack \ Demo	它工作正常。
FileStreamDemodatabase_test _New	C：\ sqlshack \ Demo	错误：由于其他数据库正在使用此位置。
FileStreamDemodatabase_test _New	C：\ sqlshack \ Demo \ New	错误：我们也不能使用子文件夹。 在这种情况下，FILESTREAM数据库'DemoSQL'将使用父文件夹。

检查数据库中是否启用了FILESTREAM (Checking whether FILESTREAM is enabled or not in database)

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

我们可以使用文件组在数据库级别检查SQL Server FILESTREAM功能。

Use FileStreamDemodatabase_test
Go
SELECT  *  FROM  sys.filegroups

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.

SQL Server FILESTREAM文件组的类型为“ FD”，因此我们可以使用上述命令检查属性，也可以使用print语句提供输出。

IF EXISTS ( SELECT  *  FROM    sys.filegroups   WHERE   type = 'FD' )   
BEGIN        
 PRINT 'FILESTREAM Filegroup Exists for the database; you can check the Physical file location'+@Physicalfilename   
 END 
ELSE     
BEGIN       
PRINT 'FILESTREAM Filegroup does not exist for this database'
 END

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

如果我在FILESTREAM数据库上运行此查询，则会得到以下输出。

Otherwise, we get the output below.

否则，我们将获得以下输出。