2

I am building a MVC3 web app against a SQL Server 2008 database. The web app will allow users to upload photos and documents.

Currently the files are stored in a column of type "image".

Is this an okay thing to do, or is it an outdated approach?

Is there any advantages in moving storage to another data type or start using FILESTREAM?

Stats - 500 (users) x 7 (avg documents each) x 2MB (avg doc size)

EDIT 1

Can anyone comment; is what I'm doing currently--storing as "image" data type--bad?

JustinStolle
  • 4,182
  • 3
  • 37
  • 48
Paul Brown
  • 4,926
  • 9
  • 32
  • 44
  • 1
    Yes, the `IMAGE` datatype has been deprecated since 2005 - use the `VARBINARY(MAX)` instead. – marc_s Apr 01 '11 at 04:50

5 Answers5

4

You should store the images on the filesystem. See this thread: Storing Images in DB - Yea or Nay?

As for the documents, are they just static files the user uploads/downloads, or will you search for content within the documents? Do you need to do anything special with the documents?

Community
  • 1
  • 1
mfanto
  • 14,168
  • 6
  • 51
  • 61
  • +1, that link is worth giving a good read to get the pros and cons – Abe Miessler Mar 31 '11 at 22:36
  • I'd store the documents on the filesystem then as well. Especially if they are Office docs with lots of metadata, you will be using up expensive storage for no real pro's. – mfanto Mar 31 '11 at 22:43
  • 2
    Storing them separately would also mean you'd have to back them up separately and also introduces the possibility that your tables could reference files that aren't there, for what that's worth. – JustinStolle Mar 31 '11 at 22:59
2

(Moving my comments to an answer)

From MSDN: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

It's "bad" if you care about the data type's planned obsolescence. It sounds like varbinary(max) or even filestream are the preferred data types going forward.

Storing files in the database vs. file system is a separate debate that everyone else seems to be jumping on instead.

JustinStolle
  • 4,182
  • 3
  • 37
  • 48
1

You might want to check out the new FILESTREAM datatype in SQL SERVER 2008. Read the link - it will give you some guidelines on deciding if this is the appropriate storage type.

dugas
  • 12,025
  • 3
  • 45
  • 51
1

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 VARBINARY column is more efficient

  • if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

  • 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 VARCHAR(MAX) or VARBINARY(MAX) 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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

In my experience it is better to store your files on disk and store a path to the file in your DB. This frees up your DB to focus on the stuff it should be doing rather than file storage.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486