SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best way to store images


Best way to store images

Poll
What is best way to store images

12.5% - 2 votes Store them in binary type in SQL SERVER
12.5% 2 votes
87.5% - 14 votes Copy them in a folder and only store their 'names' in SQL SERVER
87.5% 14 votes
Member votes: 16, Guest votes: 0. You don't have permission to vote in this poll
Author
Message
masoudk1990
masoudk1990
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 299
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
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85535 Visits: 41081
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32166 Visits: 18551
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

OTF
OTF
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 4128
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"?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85535 Visits: 41081
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
OTF
OTF
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 4128
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.
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16565 Visits: 10061
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Snigdha Vartak
Snigdha Vartak
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 247
Storing files on File Server is better option if file size is huge.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85535 Visits: 41081
Ed Wagner (3/12/2013)
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.


I've recently discovered a reason to store even large images in the database. We have call recordings that get stored on disk and then imported into SQL Server. Don't ask me why but I decided to doa reconciliation between the call files on disk and SQL Server. It turned out that people had moved the files several times, left some behind, moved some to different directories (some of which were never backed up), had lost many files in the process, etc, etc, ad infinitum.

None of that happened with the calls stored in the database even after several moves of the database. The cool part is that the files are right there with the keys to look them up. The "bad" part is that backups have gotten huge and, of course, a restore would take hours. There's a solution for that, though. These calls are, by their very nature, "read only". We're partitioning the call recording table by month with each partition in its own read-only file group. We'll only keep the current month and the previous 3 months in the PRIMARY file group to make restores a no brainer and to allow for "piece-meal" restores of the other file groups (again, 1 per month). This means instead of a 5.5 hour restore in a DR situation, we can get "back in business" in less than a half hour and take our sweet time restoring the older months and in an online fashion. Much like an archive bit in a file system, it allows us to really only backup the data that has changed (new call recordings) or that is super important even if it hasn't changed (previous 3 months).

I used to be one that said "Hell no, not in my database" to large binary "files" but I'm really starting to see some of the advantages to storing such data in the database when you combine that with some of the tools for handling larger tables. The added bonus is that the front-enders love it, management loves it (no lost or difficult to find recordings), and it has become a whole lot easier to manage the calls because they're guaranteed to all be in the same place.

One final thing... we keep the informational data for each call in a separate table and have a "sister" table for the recordings. This is kind of the best of both worlds because we can very quickly search the information table (very narrow row sizes and easy to index) and the recordings are "pulled up" only when the correct information is found. Of course, the recording table has the row ID of the informational table in it to provide the link between the two.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85535 Visits: 41081
Snigdha Vartak (8/20/2013)
Storing files on File Server is better option if file size is huge.


When you make such a statement, it would be good if you provided a reason why.

Shifting gears, I used to think what you stated but, as you can see from my previous post, I'm having a serious change of heart about that. If the files are less than 2GB, they fit quite nicely in the database. Obviously, if they won't fit into 2GB per "file" then you will have to restort to the file system instead. If that's what you meant by "huge", then I agree but not because it's a "better" option... if the files are > 2GB, they won't fit into the MAX datatypes and that eliminates that particular option. Filestream of the old fashioned "store the file path" option would be a way around that.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search