Saving Text / Images to SQL Server

[calahans]

I have wrote an app to store docs to a database using the image data type. I've read that this data type uses 2mb of space regardless of the size of the object you are storing in the field. <br>
<br>
Is this true, how do you get around this?<br>
<br>
Thanks

 

[DarkMan]

Try storing the image data in a file, and using just a varchar pointer to that file. Then have your application use that pointer to access the image....

 

[calahans]

That is a good idea. I think I'll change my code to point to a common docs area and use pointers as you say.<br>
<br>
Thanks,<br>
<br>
C

 

[bigfoot]

Microsoft has a site that does what you want. This is a site that has loads of images that are kept on disk and refered to rather then kept in SQL Server.

 

[markshiffer]

Where did you read about the 2mb per store for a Text field in SQL server?<br>
<br>
I haven't noticed this, that is why I am asking. I have a database with roughly 20000 text documents stored in it and it is only 34 MB total.

 

[DarkMan]

The text and image fields are stored on their own data pages, which are 2KB long. What the deal is, is that text and image data will take up a multiple of 2KB in size regardless of how long the actual data is.<br>
<br>
For example, the text field will be 2KB long until the data is one byte over, then it will take up 4KB, etc.

 

[markshiffer]

Ok, multiples of 2KB is much different the 2MB!!! That was what struck me as odd. That makes more since, but it still can cause a problem for some, thanks..;.

 

[jnicho02]

'text' and 'image' fields were added in SQL Server 6.5 but in my experience are best avoided. They are supposed to be used as a straight dump for documents and images, however programmers are tempted to used them when they may have char fields over 255 - this is bad news because they can be difficult to access within programs.<br>
SQL 7.0 works better because outside data sources can be included as tables in a query, including a reference to a picture... I see a database that generates websites coming on..<br>
By the way, you'd be surprised how quickly those 2K's add up.

 

[calahans]

Thanks guys thats a help