Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Best way to store images
Add to briefcase

Best way to store images Expand / Collapse
Rate Topic
Display Mode
Topic Options
What is best way to store images
Poll ResultsVotes
Store them in binary type in SQL SERVER
  		14.29%
1
Copy them in a folder and only store their 'names' in SQL SERVER
  		85.71%
6
Member Votes: 7, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
masoudk1990
Posted Saturday, March 09, 2013 2:59 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 9:46 AM
Points: 14, Visits: 74
Hi.
I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.

With this condition which way is better to store images?

Computer Enterprise Masoud Keshavarz
For more information contact masoudk1990@yahoo.com
Post #1428860
Jeff Moden
Posted Saturday, March 09, 2013 8:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784
masoudk1990 (3/9/2013)
Hi.
I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.

With this condition which way is better to store images?


Generally speaking, I'll allow storing of small images (such as small badge photographs) in a database and store large binaries in a file-path reference only.

In all practicality, I'll usually only use the file-path reference method even for small binaries for two reasons.

1. The data normally originates in a file. If we use the file-path reference method, you can avoid the bit of pain it takes to load a binary into the database. This is especially important if changes to the binary are made. No database action is required, really. Just overwrite the file and you're done.

2. Since the data normally does originate as a file, most people end up wanting to keep the file even if they don't use it for other things. It seems a bit wasteful to me to have the data stored in two places.

3. Of course, there's the front end and related security aspects. It's sometimes a whole lot easier on both if the binary data is stored in the database. Of course, if the binary data will never change, it seems a waste to constantly backup potentially voluminous static data over and over. It also makes restores take much longer should a restore need to be accomplished. If the binary data were stored in files instead of the database, restores would be much quicker. It will also distribute the I/O to possibly more than one machine (a file server and SQL Server) which would usually enhance performance.

So the answer is and although I lean toward the file-path reference method, "It Depends". Every situation is a bit different and I wouldn't rely on any rule of thumb here. Sit down and make a list of requirements vs advantages/disadvantages of each method and then make a decision.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1428897
SQLRNNR
Posted Saturday, March 09, 2013 9:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332
Jeff Moden (3/9/2013)
masoudk1990 (3/9/2013)
Hi.
I recognized if we store an image in binary type in SQL SERVER they take more space. For example a 700kb image take about 900kb space.

With this condition which way is better to store images?

...

So the answer is and although I lean toward the file-path reference method, "It Depends". Every situation is a bit different and I wouldn't rely on any rule of thumb here. Sit down and make a list of requirements vs advantages/disadvantages of each method and then make a decision.


Agreed. I have done it both ways. The file path presents an issue with double-hop sometimes and can be a pain to troubleshoot. One thing I sometimes do is to separate the images into a separate filegroup for backup purposes (when stored in the database). If they are static, you could even turn the filegroup into a read-only filegroup. The backup of that filegroup becomes less painful and requires less overall maintenance.

That said, I too have often found it easier to just store the filepath in the database - especially with frequently changing images or developer design.

In the end, It Depends.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1428902
OTF
Posted Monday, March 11, 2013 5:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 254, Visits: 3,716

This is more of a question that it is a solution but, Jeff, with the advent of filestream technology in more recent versions of sql server have you used that approach or is that more or less what you are refering to by the "file path reference approach"?
Post #1429180
Jeff Moden
Posted Monday, March 11, 2013 5:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784
OTF (3/11/2013)

This is more of a question that it is a solution but, Jeff, with the advent of filestream technology in more recent versions of sql server have you used that approach or is that more or less what you are refering to by the "file path reference approach"?


I'd have to include FileStream in the same bucket as the "File Path Reference" approach with some reservations. To be sure, I've not used FileStream, yet (too much on my plate for now), but I like what I see in the documentation. One of the things that would be important to me is that other applications should be able to use the files in the data containers that FileStream uses without having to go through anything having to do with SQL Server or FileStream. For example, I'd like folks to be able to get to the files using only, say, Windows Explorer and be able to open up a PDF or a JPG or whatever just by double-clicking on it. I also need to be able to "update" the content of a file simply by replacing it through something as simple as the Command Prompt. If that can't be done in FileStream data containers directly (and I haven't found such explicit information in the documentation, yet), then Filestream wouldn't work so well for the things I need to do.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1429203
OTF
Posted Monday, March 11, 2013 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 254, Visits: 3,716
Jeff Moden (3/11/2013)
OTF (3/11/2013)

This is more of a question that it is a solution but, Jeff, with the advent of filestream technology in more recent versions of sql server have you used that approach or is that more or less what you are refering to by the "file path reference approach"?


I'd have to include FileStream in the same bucket as the "File Path Reference" approach with some reservations. To be sure, I've not used FileStream, yet (too much on my plate for now), but I like what I see in the documentation. One of the things that would be important to me is that other applications should be able to use the files in the data containers that FileStream uses without having to go through anything having to do with SQL Server or FileStream. For example, I'd like folks to be able to get to the files using only, say, Windows Explorer and be able to open up a PDF or a JPG or whatever just by double-clicking on it. I also need to be able to "update" the content of a file simply by replacing it through something as simple as the Command Prompt. If that can't be done in FileStream data containers directly (and I haven't found such explicit information in the documentation, yet), then Filestream wouldn't work so well for the things I need to do.


Thanks for the response.
Post #1429329
Ed Wagner
Posted Tuesday, March 12, 2013 5:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,856, Visits: 528
I've never used the filestream so I won't comment on it, but I have stored images (and lots of other file types, such as AutoCad) in Oracle and SQL Server in the past. I've regretted it every time. The database files become huge and fragmentation was always a concern. They're already in a physical file and they're presumably stored so the users can get them again, so I just leave them that way. Live and learn.
Post #1429682
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.