BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ
News Articles Presentations Podcasts Guides

Topics

Choose your language

QCon New York

Find real-world practical inspiration from the world’s most innovative software leaders. Attend in-person or online.
QCon San Francisco

Learn what's next in software from world-class leaders pushing the boundaries. Attend in-person or online.
The Software Architects' Newsletter

Your monthly guide to all the topics, technologies and techniques that every professional needs to know about. Subscribe for free.

InfoQ Homepage News New File Storage Mechanism for SQL Server

Building field-level lineage for modern data systems (Live Webinar Apr 13, 2023) - Save Your Seat

New File Storage Mechanism for SQL Server

Bookmarks

Dec 03, 2007 less than one minute

Write for InfoQ

Join a community of experts. Increase your visibility.
Grow your career.Learn more

SQL Server was never designed to act as a file system, but many developers use it that way nonetheless. Countless content management systems rely on either shoving large files into blobs or storing filenames. The former has subpar performance while the later requires manually keeping the file system in sync.

SQL Server 2008 seeks to correct this by introducing a filestream option that can be applied to a varbinary(max) column. When used, binary data is stored as files in a private section of a NTFS drive. This offers the performance of a normal file but without the concurrency and backup issues.

The files are created in a folder specified by creating a new FileGroup. In order to link files to rows, each row must contain a GUID column. No explanation was given for why this was necessary in addition to the actual file column.

Rate this Article

Adoption
Style

This content is in the Microsoft topic

Related Topics:

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

  • about time

    by joey jo jo shabadoo,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    At least they are trying to address this. I've long been an advocate of keeping binary data out of the database and using the filesystem to um, store files. I've gotten weird looks in the past from Microsoft MVPs and n00bs alike. "Just put it in the database, that's what it's there for, it can handle it!".

    Back to top
  • Re: about time

    by Brian Lyttle,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    At least they are trying to address this. I've long been an advocate of keeping binary data out of the database and using the filesystem to um, store files. I've gotten weird looks in the past from Microsoft MVPs and n00bs alike. "Just put it in the database, that's what it's there for, it can handle it!".


    Well I there are limitations to both approaches. Depending on your needs, a single method of backup/restore may be useful. I think performance concerns can be dealt with in many situations, and you can end up with performance issues on the file system too. As with most things the fault is the people rather than the technology.

    Back to top

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

BT