text, ntext, image datatypes - worth using?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: text, ntext, image datatypes - worth using?

  1. #1
    Bill Clark Guest

    text, ntext, image datatypes - worth using?


    Rob,

    Historically, any file type storage in db's has been dubious at best, with
    corruption problems creeping in over time. I was curious if you might know
    if there has been any attempt to remedy or address this issue? I am expecting
    that the method of storing references (path, file name, etc) to files held
    outside the database is still preferred to having the db handle the data
    file, but am willing to give it another go if there are percieved improvements,
    or suggested attributes to help limit the intrusion of long term corruption
    problems. The ability to stream the data from the db offers some nice performance
    benefits, but am still leary of the maintenance issues surrounding these
    datatypes. Let me know what you think about this. Thanks.

    Bill Clark
    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: text, ntext, image datatypes - worth using?

    Actually, performance is usually the #1 reason to go with a file system
    solution rather than blobs. That performance difference has been chiseled
    into some with recent releases, and is expected to be addressed head on in
    Yukon.

    Serving up such things from the database can provide a nice level of
    abstraction and can, therefore, add some nice security aspects. For example,
    there are some ASP pages out there were you think you're downloading
    directly from a file, but it's actually being streamed from the database.

    Another nice issue with storing in the database is that it is backup up
    along with the other data in your database and you don't need to worry about
    the synching issues with whether the pointers (to the files) that you've
    stored in the database are synchronized time wise (no orphaned pointers or
    files) with the binary data you're storing. It's just plain simpler from a
    maintenance perspective.

    One more thing that had, historically, been in favor of the file system
    solution but is no longer in that camp is full text indexing. Some
    installations chose a file system option to allow for the use of Index
    server or some other file system indexing tool that could look through
    binary data. Index server uses a set of filters to be able to do text
    searches on a wide variety of documents including popular formats such as
    Word, Excel, Acrobat, etc.. You can even create your own Index Server filter
    for your own custom formats. Such searches were not supported for SQL Server
    image data types (which is where you would store such binary data).

    Fortunately, SQL Server 2000 Full Text Search now includes the ability to
    utilize index server filters against data stored in an image field - so that
    argument is no longer a reason to use a file based system.

    Those are kind of the quick reasons to go with blob storage in the database
    vs. a file based solution. The file based solution usually performs a bit
    better, but also usually generates many headaches of its own that may not be
    worth the performance boost. Also, the performance gains may vanish as
    future versions of SQL Server place more focus in that area.


    --
    Rob Vieira
    Visit www.ProfessionalSQL.com Today. It's new and improved!

    "Bill Clark" <bclark@speche.com> wrote in message
    news:3a6dbf02$1@news.devx.com...
    >
    > Rob,
    >
    > Historically, any file type storage in db's has been dubious at best, with
    > corruption problems creeping in over time. I was curious if you might

    know
    > if there has been any attempt to remedy or address this issue? I am

    expecting
    > that the method of storing references (path, file name, etc) to files held
    > outside the database is still preferred to having the db handle the data
    > file, but am willing to give it another go if there are percieved

    improvements,
    > or suggested attributes to help limit the intrusion of long term

    corruption
    > problems. The ability to stream the data from the db offers some nice

    performance
    > benefits, but am still leary of the maintenance issues surrounding these
    > datatypes. Let me know what you think about this. Thanks.
    >
    > Bill Clark



    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center