开发者

SQL Server: How to store binary data (e.g. Word file)?

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) 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)

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 sh开发者_JS百科ould be using a table MyDocuments with the following layout:

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

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


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.

You can check these out:

FILESTREAM Storage in SQL Server 2008
Saving and Retrieving File Using FileStream SQL Server 2008

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

Performance: Varbinary vs 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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜