23

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dennis G
  • 21,405
  • 19
  • 96
  • 133
  • Better would be to compress them and then go for a single save. – A G Dec 15 '10 at 13:35
  • Not an option as they are single documents which are referenced as single documents in the GridView. Think document management system. – Dennis G Dec 15 '10 at 13:41
  • Well then compress each one before insert. Excel and word files gets a pretty high compression ratio. This would also reduce database size. – A G Dec 15 '10 at 13:51
  • 1
    You're correct about database size. Doesn't help my question on actually HOW to save the compressed files then. – Dennis G Dec 15 '10 at 13:53
  • You can use 'Sharpziplib' library. The files would get saved as normal non zipped ones. Just save the original filename in an extra column so that it could be displayed to the user. Compress/Decompress will be done just before upload/download. – A G Dec 15 '10 at 14:06
  • I think docx files are already zip files. Though I'm not sure if they are using the best compression strategy. – MutantNinjaCodeMonkey Dec 15 '11 at 16:45
  • You're right about docx, they are zipped. But this question nowhere mentioned docx files :-) – Dennis G Dec 15 '11 at 17:40
  • It would be worth mentioning that there are products available that allow document sharing, eg I believe this is what sharepoint does. – MikeKulls Sep 20 '12 at 00:35

1 Answers1

20

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

Community
  • 1
  • 1
A G
  • 21,087
  • 11
  • 87
  • 112