欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server FileStream详解

程序员文章站 2022-03-25 10:13:08
filestream是sql server 2008中的一个新特性,允许以独立文件的形式存放大对象数据,而不是以往一样将所有数据都保存到数据文件中。以往在对业务系统的文件进...

filestream是sql server 2008中的一个新特性,允许以独立文件的形式存放大对象数据,而不是以往一样将所有数据都保存到数据文件中。以往在对业务系统的文件进行管理时有两种方法,一种是将文件保存到服务器文件系统中,数据库中只保存了该文件的路径,在使用该文件时应用程序连接到服务器读取文件;另一种是将文件以varbinary(max)或image数据类型保存到sql server中。而sql server 2008提供了filestream,结合这两种方式的优点。

filestream使sql server数据库引擎和ntfs文件系统成为了一个整体。transact-sql语句可以插入、更新、查询、搜索和备份filestream数据。filestream使用nt系统缓存来缓存文件数据。这有助于减少filestream数据可能对数据库引擎性能产生的任何影响。由于没有使用sql server缓冲池,因此该内存可用于查询处理。

以往我们对文件管理有两种方法:

 1.数据库只保存文件的路径,具体的文件保存在文件服务器(nfs)上,使用时,编程实现从文件服务器读取文件;

 2.将文件直接以varbinary(max)或image数据类型保存在数据库中。

  上面两种文件存放方式都有问题:第一种方法因为会访问磁盘,故受i/o影响性能不是很好,而且不能很好的进行文件备份;第二种方法虽然解决了文件备份(数据库的备份)问题,但是由于字段的字节数太大,对数据库本身也会造成影响,性能也很低下。

  微软在sql server 2008推出了一种新的方式 - filestream,它不是一种新的数据类型,而是一种技术,它使sql server数据库引擎和ntfs文件系统成为了一个整体,它结合了上面两种方式的优点:filestream使用nt系统来缓存文件数据,而对文件数据的操作可使用transact-sql语句对其进行插入、更新、查询、搜索和备份。

一、filestream配置

 1.配置sql server安装实例:start -> all programs -> microsoft sql server 2008 r2 -> configuration tools -> sql server configuration manager

 SQL Server FileStream详解

右击属性,切换到filestream标签,勾选如下配置

 SQL Server FileStream详解

2. 打开sql server,并配置如下 

SQL Server FileStream详解

   以上也可以通过如下脚本执行:

exec sp_configure filesteam_access_level, 2
reconfigure 

  最后重启sql server service

SQL Server FileStream详解 

二、实例展示

  创建filestream类型文件/组

SQL Server FileStream详解

--create filestreamgroup 
alter database [archive]
add filegroup [filestreamgroup] contains filestream 
go
--create filestream and association with filestreamgroup above
alter database [archive]
add file ( name = n'filestream', filename = n'd:\company\data\sql server\filestream') to filegroup [filestreamgroup]
go

  创建测试表(注意:如果表包含filestream列,则每一行都必须具有唯一的行id)

--create table
create table archive.dbo.attachment (
  [id] [uniqueidentifier] rowguidcol not null primary key,
  [filename] nvarchar(100) null,
  [createuser] nvarchar(100) null,
  [createdatetime] datetime null,
  [content] varbinary(max) filestream null 
)
filestream_on [filestreamgroup] 

  插入一些测试数据

 --insert some records
insert into attachment values 
(newid(),'file name 1','shg.cpan', getdate(),null),
(newid(),'file name 1','shg.cpan', getdate(),cast('' as varbinary(max))),
(newid(),'file name 1','shg.cpan', getdate(),cast('this is a attachment, which contains all introduction for filestream' as varbinary(max))) 

  从前台插入一些数据

using (sqlconnection conn = new sqlconnection("server=10.7.15.172;database=archive;uid=sa;pwd=1234;connect timeout=180"))
{
  conn.open();
  using (sqlcommand cmd = conn.createcommand())
  {
    string id = guid.newguid().tostring();
    cmd.commandtext = "insert into attachment values('" + id + "','file name 2','shg.cpan','" + datetime.now + "',@content)";
    sqlparameter param = new sqlparameter("@content", sqldbtype.varbinary, 1000000000);
    param.value = file.readallbytes(@"d:\folder\131 u_ex151207.log");
    cmd.parameters.add(param);
    cmd.executenonquery();
  }
  conn.close();
}

  检索数据 

select datalength(content)/(1024.0 * 1024.0) as mb,* from attachment  

结果

 SQL Server FileStream详解

  文件系统

SQL Server FileStream详解  

   上面的文件都是上传的真实文件,只不过没有后缀,如果重命名加上后缀,即可读取,如最后一个是excel文件,加上.xls,即可用excel软件打开此文件

三、注意事项

  请注意以下事项:

 •并不是所有的文件存储都适合使用filestream,如果所存储的文件对象平均大于1mb考虑使用filestream,否则对于较小的文件对象,以varbinary(max)blob存储在数据库中通常会提供更为优异的流性能;
 •filestream可以使用在故障集群上(failover cluster),但此时filestream文件组必须位于共享磁盘资源上;
 •filestream 与其他 sql server 功能的兼容性: