Upload image into new database instead of using the application's database

I am using SQL Server 2000. I need to store an image in my database. I am in middle of my analysis that whether I need to create a new table in m开发者_StackOverflow社区y application's database or I need to create a new database for image upload alone. If I create new table in the same database how the performance will be? Will it affect the application performance?

---

Usually storing images in the database is a bad thing. Have you thought about storing the images within the file system and only storing its location within the database? File systems are designed to store files, so theres little advantage to side-stepping them and using the database.

---

There's a really good paper by Microsoft Research called To Blob or Not To Blob.

Their conclusion after a large number of performance tests and analysis is this:

• if your pictures or document are typically below 256K in size, storing them in a database IMAGE column is more efficient (as of SQL Server 2005, IMAGE is deprecated - use VARBINARY(MAX) instead)

• if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient

• in between those two, it's a bit of a toss-up depending on your use

If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee foto in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee foto, too, as part of your queries.

For filegroups, check out Files and Filegroup Architecture for an intro. Basically, you would either create your database with a separate filegroup for large data structures right from the beginning, or add an additional filegroup later. Let's call it "LARGE_DATA".

Now, whenever you have a new table to create which needs to store IMAGE columns, you can specify this file group for the large data:

 CREATE TABLE dbo.YourTable
     (....... define the fields here ......)
     ON Data                   -- the basic "Data" filegroup for the regular data
     TEXTIMAGE_ON LARGE_DATA   -- the filegroup for large chunks of data

Check out the MSDN intro on filegroups, and play around with it!