SQL Server:如何存储二进制数据(例如Word文件)? [英] SQL Server: How to store binary data (e.g. Word file)?

问题描述

我不是DBA，所以我只是不知道使用各种在SQL Server中存储二进制数据的方法的利弊.

I'm not a DBA, so I just don't know the upsides and downsides of using various methods of storing binary data in an SQL Server.

• varbinary(n)仅存储8000个字节
• varbinary(max)存储2 GB
• BLOB用于大文件，但带有管理"开销(文件仅在数据库中引用，但实际上位于本地硬盘上)

• varbinary(n) stores only 8000 bytes
• varbinary(max) stores 2 GB
• BLOBs are meant for big files, but come with "administrative" overhead (the files being only referenced in the DB, but actually sitting on the local harddisk)

我基本上每年要存储大约1000个文档(每年都将这些文档存储在SQL Server 2008 R2数据库中(用简单的ASPX表单上载填充，然后通过带有下载的简单Gridview进行查看).所有文档可能约为2MB-8MB(Word，Excel文件).我的猜测是，我应该使用具有以下布局的表MyDocuments:

I basically have about 1000 documents (per year) which I want to store in a SQL Server 2008 R2 database (being filled with a simple ASPX form upload, being viewed by a simple Gridview with download). All documents will probably be around 2MB - 8MB (Word, Excel files). My guess is that I should be using a table MyDocuments with the following layout:

MyDocuments 
    Data       varbinary(max)
    Title      varchar(255)
    ModifiedOn datetime()
    ModifiedBy varchar(100)

我在使用varbinary(max)前进吗?还是我走错了方向(例如在性能方面)?

Am I on track with varbinary(max)? Or am I going in the wrong direction (e.g. Performance-wise)?

推荐答案

SQL Server 2008中有'FILESTREAM'存储.它允许结合使用SQL Server 2008和NTFS文件系统来存储BLOB数据并有效地访问BLOB数据.

There is 'FILESTREAM' storage in SQL Server 2008. It allows storage of and efficient access to BLOB data using a combination of SQL Server 2008 and the NTFS file system.

您可以查看以下内容:

SQL Server 2008中的文件流存储

小于256K的对象最好存储在数据库中，而大于1M的对象最好存储在文件系统中.

Objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem.

性能:可变与文件流

http://www.sqlskills .com/BLOGS/PAUL/post/SQL-Server-2008-FILESTREAM-performance.aspx
到BLOB或不到BLOB:数据库或文件系统中的大对象存储
SQL Server 2008 FILESTREAM性能

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-FILESTREAM-performance.aspx
To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem
SQL Server 2008 FILESTREAM performance

这篇关于SQL Server:如何存储二进制数据(例如Word文件)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！