Stewart "Arturius" Campbell (3/16/2015)
Koen Verbeeck (3/16/2015)
Carlo Romagnano (3/16/2015)
Hany Helmy (3/15/2015)
Gr8 question, thanx 🙂
Did anyone tried this feature before? And is there any actual performance gained?
I tried just a little. The answer is as usual "depends".
If you query the whole table, there's a gain.
The point of columnstore indexes is that there should be a tremendous gain when you only read a few columns, because the columnstore indexes will only read those indexes, while traditional row store tables/indexes have to read all rows/pages and filter the unnecessary columns out.
Have used nonclustered columnstore indexes before, with tremendous performance gains; agree whoeheartedly with Koen's statement.
Thanx 4 the info, but as far as I know nonclustered columnstore indexes cannot be updated in SQL 2012 (even in 2014 I guess) unless you perform a silly workaround of dropping the index, perform the DML operation on the table, then rebuild/create the index again! Or to use the switch/swap partitions method and both ways are just not practical for a 24/7 mission critical system running OLTP on some huge database.
I think it is best suited for the enterprise data warehouse environments not OLTP.
Thanks & Best Regards,
SQL Server Database Consultant