• Scott Roberts (9/24/2008)


    Nice article, but I have a couple of questions/suggestions:

    1. Are there any tests demonstrating the performance difference between FILESTREAM and traditional VARBINARY(MAX) columns? I've seen mention of varbinary being cumbersome in SQL2000 and older, but I was under the impression that it was much better in SQL2005.

    We have done extensive perf testing. Generally for blobs under 256K to 1 MB it's quicker to access via TSQL. For larger blobs it's quicker to access the file directly.

    2. Files can be accessed through the file system, but is this recommended? For example, would it be acceptable for a web site to directly access FILESTREAM files on the file system? Or are they just there in case someone wants to manually eye-ball one of them?

    The files can only be accessed via SQL functions, and under the control of SQL Server. There is a filter driver which verifies that the identity attempting the open has access to that cell in the database. Also, the access happens in the context of the current SQL transaction.

    3. How can I convert existing varbinary(max) columns to use FILESTREAM? Will a simple ALTER TABLE do it?

    No. Since it requires moving all of the data, you need to ALTER TABLE ADD COLUMN, copy the data, and then delete the old column.

    4. What is the *PRIMARY* motivation for FILESTREAM? Is it for performance, or for accessing DB files from the file system?

    Performance for accessing large blobs of data (think streaming video out of the DB)

    Thanks again for taking the time to write the article. It was very informative and obviously got me thinking.

    Scott


    Kevin Farlee
    SQL Server Storage Engine PM