5 ms·

Purported new feature: "Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index design

by nwatson 15y ago

Purported new feature: "Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries." Many DB engines such as Vertica, SenSage, Sybase IQ, all use column-oriented storage. Perhaps the only novel-but-obvious thing here is that these "read-only" indexes are implied to live alongside the regular DB table data, though I'm not sure how these "read-only" indexes would mesh with the need to support deletion on regular tables. http://en.wikipedia.org/wiki/Column_oriented_database http://en.wikipedia.org/wiki/Column_oriented_database

mgkimsal 15y ago
Perhaps the engine will update the "read only indexes" when the corresponding data in regular tables are deleted, but not in realtime, but only on demand or on a predetermined schedule?
- orcadk 15y ago
  The columnstore indexes are purely read-only; they will not be updated without requiring a rebuild. However, you can create columnstore indexes on specific partitions, and thus, by partitioning your source data, you can continue to add new data in new partitions. As most DW/"big data" solutions use partitioning anyways, this is a usable solution, giving you the benefits of the readonly performance as well as the maintainability aspects, while still allowing you to feed in new data. Columnstore indexes are not meant for OLTP type solutions, so there's no need for realtime regular data deletion; it simply doesn't happen on this kind of historical data. You might switch out a partition of your data, or you might switch in a partition of new data, but you won't change the actual data itself. Comparing SQL Servers columnstore index implementation with other columnstore based databases doesn't make sense. I won't laud columnstore indexes as a revolutionary new feature, but the way it's implemented is new, AFAIK. Comparing it to "normal" columnstore based databases will make SQL Server look bad - but the thing is, it's not meant to compete with those. Use it where it makes sense.
mark_integerdsv 15y ago
Haven't read the wiki entry you linked but the description reminds me of Sybase IQs bitwise indexes... Super fast for queries, not so much for inserts. First encountered these in 2005.
j_s 15y ago
FWIW, there are ways to deal with the read-only limitation. Here is an example in an OLTP scenario: http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/my-top-5-sql-server-2012-features-by-aaron-bertrand-guest-blogger.aspx http://blogs.technet.com/b/dataplatforminsider/archive/2011/... "With native or custom partitioning, for example, you could use a sliding window scenario, having a static set of partitions in a table with a ColumnStore index, and load current data (say, for today only) into a separate table with no ColumnStore index. Once the current day rolls into the next, you can continue loading the new day’s data into yet another table, create the ColumnStore index on yesterday’s (now unchanging) daily table, and then switch it into the partitioned table. You could make this relatively seamless to end users by creating a view that unions the partitioned table with the “current” table. You can also consider loading daily data into a non-ColumnStore table and, at the end of each day, drop the ColumnStore index from the reporting table, insert all of yesterday’s data, and rebuild the ColumnStore index."

by nwatson 15y ago

mgkimsal 15y ago

Perhaps the engine will update the "read only indexes" when the corresponding data in regular tables are deleted, but not in realtime, but only on demand or on a predetermined schedule?

orcadk 15y ago
The columnstore indexes are purely read-only; they will not be updated without requiring a rebuild. However, you can create columnstore indexes on specific partitions, and thus, by partitioning your source data, you can continue to add new data in new partitions. As most DW/"big data" solutions use partitioning anyways, this is a usable solution, giving you the benefits of the readonly performance as well as the maintainability aspects, while still allowing you to feed in new data. Columnstore indexes are not meant for OLTP type solutions, so there's no need for realtime regular data deletion; it simply doesn't happen on this kind of historical data. You might switch out a partition of your data, or you might switch in a partition of new data, but you won't change the actual data itself. Comparing SQL Servers columnstore index implementation with other columnstore based databases doesn't make sense. I won't laud columnstore indexes as a revolutionary new feature, but the way it's implemented is new, AFAIK. Comparing it to "normal" columnstore based databases will make SQL Server look bad - but the thing is, it's not meant to compete with those. Use it where it makes sense.

mark_integerdsv 15y ago

Haven't read the wiki entry you linked but the description reminds me of Sybase IQs bitwise indexes... Super fast for queries, not so much for inserts. First encountered these in 2005.

j_s 15y ago

FWIW, there are ways to deal with the read-only limitation. Here is an example in an OLTP scenario: http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/01/my-top-5-sql-server-2012-features-by-aaron-bertrand-guest-blogger.aspx http://blogs.technet.com/b/dataplatforminsider/archive/2011/... "With native or custom partitioning, for example, you could use a sliding window scenario, having a static set of partitions in a table with a ColumnStore index, and load current data (say, for today only) into a separate table with no ColumnStore index. Once the current day rolls into the next, you can continue loading the new day’s data into yet another table, create the ColumnStore index on yesterday’s (now unchanging) daily table, and then switch it into the partitioned table. You could make this relatively seamless to end users by creating a view that unions the partitioned table with the “current” table. You can also consider loading daily data into a non-ColumnStore table and, at the end of each day, drop the ColumnStore index from the reporting table, insert all of yesterday’s data, and rebuild the ColumnStore index."