views:

54

answers:

3

Hi, how big (binary(xy)) should I make my table column in SQL database if I want to store there pictures taken by camera - that means variable size up to.. I don't know.. 7MB? But if I should rather limit the size up to 2MB or something, I would. Whats your opinion?

EDIT

Or where else should I store them? I am building a web gallery using asp.net mvc.

+3  A: 

What you're talking about is a varbinary column. Of course, if you make varbinary greater than 8000, it immediately converts it into a varbinary(max) column, meaning it can store up to 2GB. This has to do with how SQL Server stores rows (8k per page).

Therefore, each row stores the column as a pointer to the bits, anyway. So, what I would do, if I were me, would be to store the images on the file system, and store the location of those files inside the database.

Eric
A: 

If you want to store images in SQL Server then use the varbinary(max) column type. It permits up to 2Gb (if I recall).

Also, as you are using SQL Server 2008 (I don't know about the express edition tho') you could use the new filestream data type.

Of course the big advantage of storing this in the database is that you only have one thing to back up and you don't have issues with the file system and database getting out of sync. The new filestream type is an interesting development because it can help alleviate these problems.

The disadvantage of storing this data in the database is that you put additional strain on the database, especially if the bandwidth between your database and webserver is already strained.

Colin Mackay
While this FILESTREAM is really cool, it plays havoc with things like backups and specially restore: you'll never get back to a specific point in time if you use that.
OMG Ponies
So, it turns out to be more of a hindrance than a help? However, just storing the files in the file system manually has issues with data sync'ing especially with backup/restore too.
Colin Mackay
A: 

As others have already stated in comments (which BTW you guys should have been answers despite the pendantic police) you really have to have some killer reason to store images in the database. Otherwise just place them in the file system.

Especially this is true in the case where the images are delivered from a web server. The web server is way more effecient at delivering images from the file system than your code will be extracting them from a database.

AnthonyWJones