Image data type

Question

Image data type

crazy_new

SSCarpal Tunnel

Points: 4804
More actions
December 20, 2016 at 2:17 am

#401240

Hi Guys,
I need to design a database for a website that will store logos and documents. My question is, what would be the best way to go about storing the images? Should I use file stream or store the documents and images in the database. Also, if I use image data type with file stream, should I still use varbinary max? In that case would in not be better to just store the image in the database using varbinary max?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The IMAGE data type is deprecated, has been since SQL 2005 and really should not be used any longer (and can't be marked as File Stream).

Use VARBINARY(MAX)

Whether you store the data in the database or as file stream depends on the size and the usage and several other things. First size, the sweet spot for filestream is for files over 1MB in size. Smaller than that, storing them in the DB is faster.

If you are going filestream, then apps that fetch the data should get the file handle via SQL Server and then use NTFS to fetch the file itself, rather than pulling it through the SQL Server buffer pool

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 2

Thanks for the reply Gila, it helps a lot..Just one more think I am curious about, would I always use varbinary(max) or would I be able to use for example varbinary(4000) when I use filestream?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Um, considering that I said the sweet spot for file stream is >1MB, and the max a varbinary(4000) can store is 4k, I'm not even sure why you'd consider it, however

https://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx

FILESTREAM storage is implemented as a varbinary(max) column in which the data is stored as BLOBs in the file system. The sizes of the BLOBs are limited only by the volume size of the file system. The standard varbinary(max) limitation of 2-GB file sizes does not apply to BLOBs that are stored in the file system.
To specify that a column should store data on the file system, specify the FILESTREAM attribute on a varbinary(max) column. This causes the Database Engine to store all data for that column on the file system, but not in the database file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 4

Thanks that makes a lot of sense. Even thought the column is varbinary(max) the data is still stored on the file system and not in the data file, so it would not make the DB grow as quick as actually storing the image in the DB as varbinary (max).

Thanks for the advise and the link. That answers my question perfectly.