 |
|
8000XXXX Errors Alerts ASP.NET 2.0 Classic ASP 1.0
Databases Access DB & ADO General SQL Server & Access Articles MySQL Other Articles Schema Tutorials Sql Server 2000 Sql Server 2005 General Concepts Search Engine Optimization (SEO)Search
ASP FAQ Tutorials :: Databases :: Other Articles :: Should I store images in the database or the filesystem?
Should I store images in the database or the filesystem?
I *strongly* recommend storing the file in the filesystem and its LOCATION in the database, though there are several components out there that make storing images in a database really easy. My preference is ASPUpload from Persits Software. You can see how simple the code is by looking at AspUpload.com - User Manual. Chapter 4.
You can also handle this type of task without a component, but it's a lot more code. There are good examples available at ASP 101 and StarDeveloper.
Here are the arguments for each methodology.
Keep images on the filesystem, and the location and other data in the database
You can also handle this type of task without a component, but it's a lot more code. There are good examples available at ASP 101 and StarDeveloper.
Here are the arguments for each methodology.
Keep images on the filesystem, and the location and other data in the database
- if there is any chance that you will migrate to a different database platform, your current BLOB format might be incompatible with, or at least a pain to convert to, the new format -- since, like web browsers, each vendor has implemented things with their own slant.
- when your database really goes south, to the point where even the backup is useless, you still have the files on the filesystem (though their usefulness is questionable, depending on how much related data was kept in the database). Which is arguably better than losing all of your data *and* all of your files.
- having the images in the file system allows you to access the images from many different standard applications (FTP, web browser, etc) without having to write application code to pull the data out of the database, since you can't just 'SELECT image FROM table' and have the image appear in Enterprise Manager or Query Analyzer.
- with some databases, e.g. Access and MSDE, the data inside is limited to 2 GB (SQL Server Express 2005, as of July 2004, is planned to support 4 GB), whereas the file system is only restricted by the size of the volume. Also, most hosts charge a premium for SQL Server storage space, so in that case it would be cheaper to store them in the file system.
- in SQL Server 7.0, when a table has an IMAGE or TEXT column, a page is reserved for every row, whether or not that column has any data... so if you don't have an image for each record, there is potential to have a lot of wasted space. There are workarounds to this, of course, such as splitting the image data into its own table with a foreign key to the main table. SQL Server 2000 *can* combine multiple images < 8kb on one page (but typically, high-volume sites with plenty of images are not focusing on file sizes < 8kb anyway). Also, in SQL Server 2000, if your images are less than 8kb, you can use the text in row option by running the following code:
EXEC sp_TableOption N'TableName', 'TEXT IN ROW', 'ON'
Of course you will have to be careful with this option if you have other TEXT / IMAGE columns in the table.
- performance wise, including an <IMG SRC> tag generated by the database and pointing to a file that already exists is going to be faster than pulling the file out of the database, generating a temp file on the web server, and streaming that to the user. Also, table scans take more resources when there is an image datatype as opposed to a varchar that simply holds a 'pointer' to the file's location.
- can be quite complicated extracting images, say from an Access database, since it adds OLE header info to the file (see KB #175261). With SQL Server it's not so bad; see KB #173308 for an example that works right out of the box, KB #258038 for a VB example using ADODB.Stream, and KB #194975 for samples that use ADO's GetChunk and AppendChunk methods.
- The number of KB articles involving BLOB/IMAGE columns in SQL Server is astounding. Here is a brief subset:
So, this should give you some idea of the number of problems associated with using the IMAGE datatype.KB #152805 ,KB #162032 ,KB #164972 ,KB #170296 ,KB #171369 ,KB #177067 ,KB #177114 ,KB #187966 ,KB #192489 ,KB #193640 ,KB #195530 ,KB #195539 ,KB #197043 ,KB #198487 ,KB #207396 ,KB #217102 ,KB #223758 ,KB #230044 ,KB #244962 ,KB #254253 ,KB #252948 ,KB #255632 ,KB #257767 ,KB #263553 ,KB #271344 ,KB #272220 ,KB #276041 ,KB #280684 ,KB #286257 ,KB #298835 ,KB #308775 ,KB #317670 ,KB #324432 ,KB #834604 ,KB #888494 ,KB #890759 ,KB #895203
- you can protect your images better with SQL Server and file system security, as opposed to simply file system.
- it can be challenging to keep database and filesystem in sync with each other - if the record is deleted in SQL Server (e.g. through an ad hoc query, or a rollback) how does the file system portion of the application know to also delete the image in the file system? One approach is to use extended stored procedures in SQL Server to encompass the file system operations into the database transactions, however this can become as complex, or more so, than storing the BLOB directly in the database - which gives you greater transactional control and consistency.
Related Articles
How do I build a query with optional parameters? How do I calculate the median in a table? How do I create a store locator feature? How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns? How do I deal with multiple resultsets from a stored procedure? How do I debug my SQL statements? How do I determine if a column exists in a given table? How do I enable or disable connection pooling? How do I enumerate through the DSNs on a machine? How do I find a stored procedure containing <text>? How do I get a list of Access tables and their row counts? How do I get the latest version of the JET OLEDB drivers? How do I handle alphabetic paging? How do I handle BIT / BOOLEAN columns? How do I handle error checking in a stored procedure? How do I ignore common words in a search? How do I page through a recordset? How do I present one-to-many relationships in my ASP page? How do I prevent duplicates in a table? How do I prevent my ASP pages from waiting for backend activity? How do I prevent NULLs in my database from mucking up my HTML? How do I protect my Access database (MDB file)? How do I protect my stored procedure code? How do I protect myself against the W32.Slammer worm? How do I remove duplicates from a table? How do I rename a column? How do I retrieve a random record? How do I return row numbers with my query? How do I send a database query to a text file? How do I simulate an array inside a stored procedure? How do I solve 'Could not find installable ISAM' errors? How do I solve 'Operation must use an updateable query' errors? How do I temporarily disable a trigger? How do I use a SELECT list alias in the WHERE or GROUP BY clause? How do I use a variable in an ORDER BY clause? Should I index my database table(s), and if so, how? Should I use a #temp table or a @table variable? Should I use a view, a stored procedure, or a user-defined function? Should I use recordset iteration, or GetRows(), or GetString()? What are all these dt_ stored procedures, and can I remove them? What are the limitations of MS Access? What are the limitations of MSDE? What are the valid styles for converting datetime to string? What datatype should I use for my character-based database columns? What datatype should I use for numeric columns? What does "ambiguous column name" mean? What is this 'Multiple-step OLE DB' error? What is wrong with 'SELECT *'? What naming convention should I use in my database? What should I choose for my primary key? What should my connection string look like? When should I use CreateObject to create my recordset objects? Where can I get this 'Books Online' documentation? Where do I get MSDE? Which database platform should I use for my ASP application? Which tool should I use: Enterprise Manager or Query Analyzer? Why are there gaps in my IDENTITY / AUTOINCREMENT column? Why can I not 'open a database created with a previous version...'? Why can't I access a database or text file on another server? Why can't I use the TOP keyword? Why do I get 'Argument data type text is invalid for argument [...]'? Why do I get 'Not enough space on temporary disk' errors? Why does ASP give me ActiveX errors when connecting to a database? Should I use COALESCE() or ISNULL()? Where can I get basic info about using stored procedures?