Datatype for storing images\documents

Question

Datatype for storing images\documents

PJ_SQL

SSCertifiable

Points: 6703
More actions
September 9, 2016 at 12:57 pm

#330086

Database was storing images so the column was of datatype varbinary(max), since we moving all the images to file system , and will only have information of the pointer what would be the appropriate data type for that?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ryanbesko Default port Points: 1424 More actions · Answer 1

Use a data type of nvarchar(the_size_you_need_for_file_name_or_full_path), or varchar(same_thing), if you don't need to worry about international characters and want to save a few bytes of storage space per row.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 2

Depending on how much space you want to take up, you could use a table for Path and an FK between that and your image table. Then just keep the image filename, and an int for the path. This will help reduce storage a great deal.

Shamless self promotion - read my blog http://sirsql.net

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 3

PJ_SQL (9/9/2016)
Database was storing images so the column was of datatype varbinary(max), since we moving all the images to file system , and will only have information of the pointer what would be the appropriate data type for that?

To be honest, I'm not sure I'd bother. Instead of images, we have "voice images" in the form of a type of compressed WAV files. Since we still have files, we thought we didn't need to have such things in the databases. When we went to find all the files that we had info in the database, we found that more than 10% were missing and another 10% were corrupted.

We partitioned the table based on month (more than 8 years worth) to seriously reduce maintenance and backup requirements and left them in the database. No one takes care of data like a DBA. :w00t:

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 4

This is new implementation that we would be working on, the demo has data stored in database but the final product would store images on filesystem.

So, I was concern was whether to leave the datatype as varbinary(max) or change it to varchar(max)?

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 5

PJ_SQL (9/9/2016)
This is new implementation that we would be working on, the demo has data stored in database but the final product would store images on filesystem.

Understood. I'm actually recommending against that, though.

So, I was concern was whether to leave the datatype as varbinary(max) or change it to varchar(max)?

If you good folks do decide to store the images on the file system, then neither is appropriate. Do you really expect a file path to have over 8K Bytes? VARCHAR(MAX) can handle up to 2 Billion characters and VARBINARY(anything) is the wrong datatype for file paths or UNCs.

Figure out what the longest file path will be during the life of the system, add a 100 or 200 to that to be safe, and "right size" a VARCHAR(xx) to store the file path. Round up to the nearest 500 or 1000 if you want but I can almost guarantee that you won't ever exceed the need of a VARCHAR(8000).

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)