| Author |
 Topic  |
|
|
AnonJr
Moderator
United States
5768 Posts |
 Posted - 29 November 2005 : 10:03:58
|
In preparation for a hopeful move to SQL Server of some flavor (MSDE/2000/2005 Express) I've got a data typing question:
The app I have running now is using Access as a back end. I have an OLE Object field that I'm using to store some binary files (images, PDFs, and SWFs mostly). Most of them are around 1MB; but I would like to plan ahead for larger files.
If I'm reading right, there are a couple of options for storing binary data in SQL Server. Which one would be the best? or is it going to depend on size?
I was thinking that the "Image" data type would work, but ... I'd rather be certain. |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 29 November 2005 : 10:23:02
|
yep, image. From Books Online:
Binary Data
Binary data consists of hexadecimal numbers. For example, the decimal number 245 is hexadecimal F5. Binary data is stored using the binary, varbinary, and image data types in Microsoft® SQL Server™ 2000. A column assigned the binary data type must have the same fixed length (up to 8 KB) for each row. In a column assigned the varbinary data type, entries can vary in the number of hexadecimal digits (up to 8 KB) they contain. Columns of image data can be used to store variable-length binary data exceeding 8 KB, such as Microsoft Word documents, Microsoft Excel spreadsheets, and images that include bitmaps, Graphics Interchange Format (GIF), and Joint Photographic Experts Group (JPEG) files.
In general, use varbinary for storing binary data, unless the length of the data exceeds 8 KB, in which case you should use image. It is recommended that the defined length of a binary column be no larger than the expected maximum length of the binary data to be stored.
But if you can possibly help it, don't do it - save the binaries as files and store links to them in your db - way less db bloat. Indeed it is VERY rare you would ever be better off storing the binary direct into the db. VERY VERY rare.
hth :) |
 |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 29 November 2005 : 11:07:01
|
Thanks, that does help a lot. Given the convoluted restrictions I'm working under at this point in time, this has been the best solution. 
I do agree that as soon as I can run it otherwise, I will. 
I'll probably be making further inquiries later. The meetings which were originally started to get me access to SQL Server have somehow re-opened the "Buy vs. Build" debate with reguards to the Web-based training we do.
I may be slightly biased, but I still think we're better using a homegrown solution. IS would rather have a company to call when something goes wrong. Such is life.
The results of this will determane if we continue to use the app I created w/snitz or if we go with some commercial package. (I'll still use snitz in my personal projects though  ) |
|
|
| |
Topic |
|
|
|
Topic Locked
