file storage in sql server

Question

thank u all who response for my query its really to helpfull now i hav facing 1 more problem actually i m design a website for education domain so in education system no. of letters n documents come n i want to store that wole document in database(SQL SERVER 2005 n 2008) with few attribute as doc_id, type etc... so please help to implement dat and if possible provide some syntax or code for doing same as one attribute

Answer 1

There are two options for storing files:

1) You can use the BLOB (Binary Large Object) data type for storing files in the database. 2) You store the file on disk and save its path in the database.

Rest of the attributes like type, summary etc, are simple and shouldn't be much of an issue.

Answer 2

As good an idea as that sounds, its rarely a good idea to store files in the database itself. I chased down that rabbit hole myself despite the better urgings of my peers and ran into a number of scaling/performance issues. Upload the file, store a reference to it, and the related information.

Answer 3

If you're using SQL Server 2008 FILESTREAM Storage is the best option. It is a combination of SQL Server and NTFS. Read more at http://msdn.microsoft.com/en-us/library/cc949109.aspx

Answer 4

Have you tried WSS (Windows Sharepoint Services) or MOSS (Microsoft Office Sharepoint Server)? WSS is completely free and will run on W2K3 or W2K8. It's excellent and does stored everything (included documents, images, etc.) in the database.

Answer 5

You didn't indicate what version. I generally recommend uploading the file to a shared location and then putting a pointer to the file in the database. However, if your files are backed up at a different time than your database, this can lead to consistency problems.

If you're using a version prior to SQL Server 2005, you can store the file in an image data type. In SQL Server 2005 you'll want to consider the varbinary(MAX) data type as the image data type is deprecated. As has been previously mentioned, if you are on SQL Server 2008, you can use FILESTREAM, which will still use the varbinary(MAX) data type, but the data will be stored in the filesystem instead of the database. As to when to consider using FILESTREAM, from Books Online:

In SQL Server, BLOBs can be standard varbinary(max) data that stores the data in tables, or FILESTREAM varbinary(max) objects that store the data in the file system. The size and use of the data determines whether you should use database storage or file system storage. If the following conditions are true, you should consider using FILESTREAM:

• Objects that are being stored are, on average, larger than 1 MB.

• Fast read access is important.

• You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.