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 bytesvarbinary(max)
stores 2 GBBLOB
s 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
精彩评论