Mix of IM Rowstore with Disk Columnstore

Off-Topic
1
  1. Is it possible to have a Transaction that access both In-memory Rowstore and disk Columnstore?
  2. Is it possible to have a Query with a JOIN of a In-memory Rowstore with a disk Columnstore?
  3. Is it possible to INSERT into [Columnstore table1] (…) SELECT FROM [Rowstore table2] ?

thanks

2

Yes, to all of the above.

3

@adam, thanks for the quick reply

What isolation levels are supported?
Can a single transaction with a single commit do update 2 tables, one in IM rowstore, second in disk columnstore?
MVCC, Joint Clock / single commit serial number between two stores, Joint recovery- do they exist?

Also, this forum post (Move the data in memory (rowstore) to disk) says the opposite - MOVE data from rowstore to columnstore is IMPOSSIBLE, which kind of means “NO” to all my questions… strange…

Can you elaborate? maybe point to some documentation

Thanks

4

Hi Vlad,

read committed is the only supported isolation level as of right now. We’ll be adding support for strong isolation levels next year.

A single transaction can write to many tables, as long as they are in the same database.

I’m not clear on your questions related to MVCC. Rowstore table do use MVCC so readers can run queries without blocking behind writers.

Your previous question was asking about moving data stored in a in-memory rowstore table to disk. There is no built-in way of doing it (i.e., data in rowstore tables must always be held in memory). As mentioned on the post columnstore tables do keep recently written rows in memory in rowstore automatically under the hood, so depending on your workload its often possible to just use a columnstore table directly. That said, you can manually move data from a rowstore table to a columnstore by deleting the data from the rowstore table and moving it into a columnstore table (via insert…select) in the application.

-Adam

1 Like