Which data-type for storing images and documents in SQL Server 2005

I must extend an existing .开发者_如何学Gonet-Application to store images and documents (mostly pdf's) in a SQL Server 2005 database.

Which SQL Server datatype is used best for saving these files and is it meaningfull to create a separate table that holds the fields, or is it no problem to put these fields directly in the "normal" business tables, as long as the select statements do not select them (or SELECT *).

---

SQL Server 2005: Blobvarchar(max)
SQL Server 2008: FileStream

---

VARBINARY or FILESTREAM

No need to store them in a separate table - there are built-in controls you can use to force it to be stored in/out of row using sp_tableoption:

http://msdn.microsoft.com/en-us/library/ms189087.aspx

http://msdn.microsoft.com/en-us/library/ms186981.aspx

http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-manage-unstructured.aspx

(Note that there might be benefits to storing in a separate table if you are using different filegroups and backing them up independently or something like that - but that might be considered exceptional)

---

Use varbinary(max) to store images and documents in sql server. I would recommend having them in a seperate table and not the main table by itself.

---

To answer the other part of your question, if you are storing binary data in a database, don't mix the data in a table with other fields. You probably won't be doing any searching or indexing on the binary data, and access to any metadata will be slowed down by the size of the table. Keep just the images/files in a separate table.

Though I can think of good reasons to keep files in a SQL server database (for example: not a web app), what @JonH says as well: keep them in the file system unless you have a compelling reason not to.