This columnar approach is best when there is a relatively small distribution of data values as the data is not stored redundantly for every row, which allows these columns of data to take advantage of compression which can reduce storage by upwards of 90%. Conversely, data can also be stored in columns, where rows consist of pointers to the relevant columns of data. This works very well for storing normalized data for use by OLTP systems (optimizing write performance) and to a lesser extent by de-normalized data in OLAP systems. Columnstore Indexes in SQL Serverĭata in SQL Server (and virtually every other RDBMS) is stored in rows, with attributes being stored as columns. I’ll cover in-memory tables in the second installment and indexed views in the last one.
In the first of three blog posts, I’ll cover columnar indexes. A few capabilities struck me as significant to our clients’ needs: columnstore indexes, in-memory tables and indexed views (aka materialized views). Recently, I had the opportunity to take a much more in-depth look at SQL Server 2016. While I’ve used SQL Server on many projects throughout the years, I’ve tended towards Oracle, DB2, and Postgres due to either more advanced features or price (in the case of Postgres). I’ve been getting re-acquainted with SQL Server 2016 after a very long hiatus (read 7.0).
SQL Server 2016 Test Drive: Columnstore Indexes, In-Memory Tables and Indexed Views The award-winning software development partner recognized for innovative employee engagement measures Part 1 of 3 Introduction