Filestreams, transactions and SQL Server 2008 R2

I was under the impression that the database engine/programming model changes in SQL Server 2008 R2 were minimal and could be counted on one hand. Today I was running an old demo that I had, having to do with filestreams on the R2 Nov CTP. Usually I run this one line-by-line, but I was in a hurry and ran the whole thing. Funny…I didn't remember this few errors caused by limitations when using filestream. Hmmm…

In SQL Server 2008 R2, filestream storage now support snapshots transaction isolation levels. Both flavors, read-committed snapshot and snapshot. This should expand the possibilities for using this feature because, in SQL Server 2008 (non-R2), you can't even enable either of these isolation levels at the database level if you have a filestream filegroup/column. Now, you can not only enable the levels, but the filestream goes exhibits the transactional semantics.

This is actually doc'd in the SQL Server 2008 R2 BOL, right here.  BTW, although the chart in the BOL lists the streaming access doesn't support ReadUncommitted, RepeatableRead, or Serializable iso levels, in my experiments, you don't use an error using these iso levels with streaming. The stream just doesn't exhibit the expected transactional semantics.

So you can count 'em on one hand, 'eh? Between this enhancement and the perf improvements blogged about recently on the SQL Server Storage Engine blog, maybe I'll need another hand.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.