在SQL Server中存储图像？ [英] Storing images in SQL Server?

问题描述

我制作了一个小型演示站点，并在其上我将图像存储在sql server上的image列中。我有几个问题......

I have made a small demo site and on it I am storing images within a image column on the sql server. A few questions I have are...

• 这是个坏主意吗？

• Is this a bad idea?

会不会影响我网站上的效果？

Will it affect performance on my site when it grows?

另一种方法是将图像存储在光盘上，并仅将对图像的引用存储在数据库中。这必定是许多人常见的困境。我会欢迎一些建议，如果可以的话，我会很乐意犯一个错误。

The alternative would be to store the image on disc and only store the reference to the image in the database. This must be a common dilemma many people have had. I'd welcome some advice and would actually be happy to make a less of a mistake if I could.

推荐答案

Microsoft Research的一篇非常好的论文名为要Blob还是不要Blob 。

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:

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

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

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

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

如果您决定将图片放入SQL Server表格，我强烈建议您使用单独的表格进行存储那些照片 - 不要将员工照片存储在员工表中 - 将它们保存在单独的表中。这样，员工表可以保持精简，平均且非常高效，假设您并不总是需要选择员工照片作为查询的一部分。

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 photo 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 photo, too, as part of your queries.

对于文件组，请查看文件和文件组架构以获取介绍。基本上，您可以从一开始就为大型数据结构创建具有单独文件组的数据库，或者稍后添加其他文件组。我们称之为LARGE_DATA。

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

现在，无论何时创建需要存储VARCHAR（MAX）或VARBINARY（MAX）列的新表，都可以指定这个大数据的文件组：

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

查看文件组的MSDN简介，并使用它！

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

这篇关于在SQL Server中存储图像？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！