How does Microsoft support data update in its new column-store technology?

Microsoft announced a new column-store database technology in its SQL Server 2012 and claimed to add update ability to it in the next release.

Basically, a traditional database writes data one row at a time. With the new column-store technology, SQL Server writes data one column at a time. So, for a table with an ID and name columns and 1,000 rows of data, SQL Server writes the 1,000 IDs first and then writes the 1,000 names next.

The advantage of the column-store technology is that it becomes very quick to filter rows, if data are written this way, because most database operations belong to this category. However, its benefit is limited, if there are frequent updates to the data, such as the data in an On-Line Transactional Processing (OLTP) system.

If there are frequent updates, the column of data written will require frequent rebuild. For example, if the ID of a person changes from 10 characters to 20 characters, there will be no space to update the ID, if it is sequentially written to database column-wise. This essentially slows down the database process. Then, how does Microsoft fix this problem?

The solution to this problem is the introduction of “logical delete”.

When new rows of data are added to a table, SQL Server can just append the columns of those data to the end of each individual column. So, data insert is not an issue to column-store today.

When rows of data are deleted, SQL server does not physically remove / delete the data in the column-store. Instead, it marks them as deleted in the column-store. This is called “logical delete”.

When rows of data are updated, SQL Server marks those records as deleted and inserts those new rows to the end of the column-store.

As a result of the “logical delete”, records marked as deleted would accumulate quickly. To remedy the growing overhead to the database, DBAs would need to add a new maintenance task to rebuild the column store by actually deleting those rows of data in the column-store. This is how Microsoft can support data update with its new column-store technology.