Store file on file system or as varbinary(MAX) in SQL Server -

i understand there lot of controversy on whether bad practice store files blob's in database, want understand whether make sense in case.

i creating asp.net application, used internally @ large company, users needs able attach files 'job' in system. these files pdf's or word documents, never exceeding couple of mb.

i creating new table so:

id (int) jobid (int) filedescription (nvarchar(250)) filedata (varbinary(max) 

is use of varbinary(max) here ideal, or should storing path file , storing file on file system somewhere?

there's paper microsoft research called to blob or not blob.

their conclusion after large number of performance tests , analysis this:

• if pictures or document typically below 256k in size, storing them in database varbinary column more efficient

• if pictures or document typically on 1 mb in size, storing them in filesystem more efficient (and sql server 2008's filestream attribute, they're still under transactional control , part of database)

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

if decide put pictures sql server table, recommend using separate table storing pictures - not store employee foto in employee table - keep them in separate table. way, employee table can stay lean , mean , efficient, assuming don't need select employee foto, too, part of queries.

for filegroups, check out files , filegroup architecture intro. basically, either create database separate filegroup large data structures right beginning, or add additional filegroup later. let's call large_data.

now, whenever have new table create needs store varchar(max) or varbinary(max) columns, can specify file group large data:

 create table dbo.yourtable      (....... define fields here ......)      on data                   -- basic "data" filegroup regular data      textimage_on large_data   -- filegroup large chunks of data 

check out msdn intro on filegroups, , play around it!