SQL Server 2012 FileTable locks when writing

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

Answer 1

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, [e-mail]

Answer 2

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

Answer 3

See this article for a little insight into this behavior:

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

John