locked
SQL Server 2012 FileTable locks when writing RRS feed

  • Question

  • Hi,

    I played and tested this a bit and i found one big disadvantage in the FileTable feature.
    While copying a file, you cannot execute any select queries on the DB!
    What happens if you constantly need to copy\delete\modify files?

    Can it really be that no reads are available or am I missing something ?

    Would be happy to hear what you think about it.

    Matan

    Monday, February 27, 2012 1:25 PM

All replies

  • On the entire database? I find that somewhat difficult to believe.

    If you say that you cannot run a SELECT with a WHERE condition on the FileTable while the copying is running, I would believe you. But I would expect that you are still able to access other rows in the table while the copying is running. Can you clarify?

    Unfortunately, I don't have the possibility to test the scenario myself right now.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 27, 2012 3:20 PM
  • Hi,

    I apologize I wasn't accurate. That is probably because my background is software development and not DBs.

    I think that you are correct.

    The scenario is: executing a SELECT Top 1 on a FileTable while copying is running.

    I have a few files (and therefore a few rows at the table). Then, I copy a new file to the folder, and execute the Select.

    The Select is halting as long as the copying is running.

    I now added: "WHERE name = 'file_name' " where the name is of another file that exists already at the folder, and the query succeeds.

    I guess it is possible to query for other rows while the copying is running.

    Thank you for the fast reply.


    Matan

    Tuesday, February 28, 2012 7:25 AM
  • See this article for a little insight into this behavior:

    http://msdn.microsoft.com/en-us/libarary/gg492061(v=sql.110).aspx

    John

    

    Thursday, March 1, 2012 7:37 PM