SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 using VARBINARY(MAX).
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hariarul
Posting Yak Master

India
160 Posts

Posted - 01/04/2007 :  03:41:38  Show Profile
Hi all,

Could anyone let me know whether VARBINARY(MAX) or File system is BEST for storing large files ? Any pros & cons ?

Also , whether SSIS can pick a file from a table with from a VARBINARY(MAX) column and process it ?

Thanks in advance,

DBAnalyst

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/04/2007 :  04:03:49  Show Profile
The file system is better for storing files, in general terms. Storing the Path / Filename in the database to enable the file to be retrieved.

In general:

There is more effort retrieving a file from the DB rather than the OS

Databases that include files cause Backups to be bloated - technology for backing up files less frequently - i.e. when they change, or on a rotational basis so that they are sure to be backed up periodically, but not every time - is very mature.

Files stored in the database are rarely cached downstream, unlike files stored in the OS (this refers to web delivery)

If you have a need to regularly copy the database AND files to other locations then a one-stop-solution of storing the images in the DB may make the publication process easier.

For some security issues (only certain users have access to certain files) storing files in the DB can help

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Image,Images

Kristen
Go to Top of Page

Hariarul
Posting Yak Master

India
160 Posts

Posted - 01/04/2007 :  04:13:38  Show Profile
Thanks Kristen.

If a large document or image is stored in column with IMAGE or VARBINARY(MAX) datatype , would these file internally reside in the mdf or any other physical location ?

HHA
Go to Top of Page

Hariarul
Posting Yak Master

India
160 Posts

Posted - 01/04/2007 :  04:58:35  Show Profile
What can be the maximum file size we could store in VARBINARY(MAX) column ?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 01/04/2007 :  05:23:59  Show Profile  Visit spirit1's Homepage
varbinary(max)'s maximum storage size is 2^31-1 bytes.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/04/2007 :  05:47:05  Show Profile
"would these file internally reside in the mdf or any other physical location"

They will be in the MDF

Kristen
Go to Top of Page

Hariarul
Posting Yak Master

India
160 Posts

Posted - 01/04/2007 :  06:16:58  Show Profile
Thanks Kristen and spirit1.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 01/04/2007 :  08:48:20  Show Profile
The points here:

http://www.aspfaq.com/show.asp?id=2149

whilst a bit stale cover the essentials more eloquently than I have!

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000