Most efficient way to store a 20 Meg file in a SQL

问题:

We store documents in an SQL Server 2005 database table with a column format of "Image".

Every time I try to store a PDF file which is greater than 1 Meg, it somehow gets corrupted.

Is there any particularly efficient method in .NET to serialize and store large files (~10megs) into a database?

[Edit] Microsoft actually says the max file size is about 2G Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes. http://msdn.microsoft.com/en-us/library/ms187993.aspx

回答1:

A quick solution if you don't want to refactor your documents out of the db at this point is to use compression on your data:

http://msdn.microsoft.com/en-us/library/system.io.compression.gzipstream.aspx

i doubt that Sql Server is corrupting your files but working in the gzip algorithm might help you find a bug.

回答2:

Wouldn't it make more sense to store the file in the file system, and store a reference to the file in the database? storing 20MB files in the database sounds like it could cause some performace issues.

回答3:

Follow the document centric approach and not store documents like pdf's and images in the database, you will eventually have to refactor it out when you start seeing all kinds of performance issues with your database. Just store the file on the file system and have the path inside a table of your databse.

however, you may already be dealing this issue so take a look at this article.

http://chiragrdarji.wordpress.com/2007/08/31/storing-and-retrieving-docpdfxls-files-in-sql-server/

It explains that max limit of varbinarary is 2 raised to 31 bytes is what you can store in sql server 2005.

回答4:

Shouldn't you be using VARBINARY(Max) for large binary data in SQL Server 2005, anyway?