文件流vs本地保存在sql server？ [英] file stream vs local save in sql server?

问题描述

我的应用程式在使用者注册后才播放影片档案（档案大于100 MB）。

1. 最好是将它们存储在硬盘驱动器 ？
   或
   我以数据库的形式存储为文件流类型？




2. 数据库，更安全的操纵vs存储在硬？




3. 如何提供数据安全防止操纵？

感谢。

解决方案

Microsoft Research有一篇非常好的文章，名为到Blob或不到Blob 。

他们在大量性能测试和分析是：

• 如果您的图片或文档的大小通常低于256K，将它们存储在数据库VARBINARY列更高效




• 如果您的图片或文档的大小通常超过1 MB，将它们存储在文件系统中更高效（SQL Server 2008的 FILESTREAM 属性，它们仍然处于事务控制和数据库的一部分）

如果你决定把你的图片放到SQL中，服务器表，我强烈建议使用单独的表来存储这些图片 - 不要将员工foto存储在员工表中 - 将它们保存在单独的表中。这样， Employee 表可以保持精简，平均和非常高效，假设你不总是需要选择雇员foto，作为查询的一部分。 / p>

对于文件组，请查看文件和文件组体系结构。基本上，您可以从一开始就为大型数据结构使用单独的文件组创建数据库，或者稍后再添加一个文件组。让我们把它叫做 LARGE_DATA 。

现在，每当有一个新表需要存储 VARCHAR（MAX）或 VARBINARY（MAX）列，您可以为大型数据指定此文件组：

  CREATE TABLE dbo.YourTable 
（.......在此定义字段......）
 ON数据 - 常规数据的基本数据文件组
 TEXTIMAGE_ON LARGE_DATA  - 大块数据的文件组
  

查看MSDN介绍文件组，并玩弄它！

my application play videos files after that user they are registered .(files are larger than 100 MB ) .

1. Is it better to do I store them on the hard drive and Keep file path in database ? Or do I store in database as File Stream Type ?

2. When data is stored in the database, are more secure against manipulation vs with stored in hard ?

3. How to provide data security against manipulation ?

Thanks .

解决方案

There's a really good paper by Microsoft Research called To Blob or Not To Blob.

Their conclusion after a large number of performance tests and analysis is this:

• if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient

• if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

• in between those two, it's a bit of a toss-up depending on your use

If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee foto in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee foto, too, as part of your queries.

For filegroups, check out Files and Filegroup Architecture for an intro. Basically, you would either create your database with a separate filegroup for large data structures right from the beginning, or add an additional filegroup later. Let's call it LARGE_DATA.

Now, whenever you have a new table to create which needs to store VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this file group for the large data:

 CREATE TABLE dbo.YourTable
     (....... define the fields here ......)
     ON Data                   -- the basic "Data" filegroup for the regular data
     TEXTIMAGE_ON LARGE_DATA   -- the filegroup for large chunks of data

Check out the MSDN intro on filegroups, and play around with it!

这篇关于文件流vs本地保存在sql server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！