Include support for Calculated Columns on Columnstore Indexes
For a typical DataWarehouse implementation, it is common to have a good number of calculated measures through calculated columns.
I personally know and have also worked with a very high number of Healthcare DataWarehouses using Computed Columns. The expected effort for converting & maintaining all ETL processes into inserting the data directly into new columns, plus the risk associated with the development where the blockers preventing from the conversion of those Fact Tables into Clustered Columnstore Index is simply too costly.
In some of the cases, 3rd party tools will simply not allow a regular maintenance of the ETL process for a reasonable cost.
Upvotes: 52<=-=Oct 2 2015 9:58AM=-=>
Thanks for submitting this request and I do appreciate you informing us of health industry where computed columns are used commonly. We won’t be able to addess this in SQL 2016 but consider this feedback in the next release
Certain problems can only be solved by computed columns, and it would be very nice to use the sweet columnstore index on these tables. For example, a date filter in Tableau will actually write 3 filters on datepart(year,mydate), datepart(month,mydate), and datepart(day,mydate). I can completely solve this performance issue by creating persisted computed columns on those functions (and SQL Server will substitute the persisted column when a query uses a filter on these functions). That’s great. But then, I can’t columnstore index these new columns. So I tried to create an indexed view on the whole table including these columns. But SQL Server won’t substitute use the indexed view for these functions. I am soo close. (yes, I’ll point this out to Tableau as well, but they say that my database should be able to handle it.)<=-=Jul 8 2016 7:02AM=-=>
Just ran into this issue right away when trying to test a change to our dev environment to use columnstore for certain tables. I agree this should be a priority to bring columnstore toward feature parity. I ran into the issue on SQL 2014, and frankly was very surprised that it remains an issue in SQL 2016 given all of the marketing of columnstore improvements that has come along with the SQL 2016 release.<=-=Dec 2 2016 7:50AM=-=>
Another team within our organization has now hit this issue as well, and will need to spend time implementing a custom workaround because this feature doesn’t exist.<=-=Jan 31 2017 1:19AM=-=>
We have a situation where the partition key is a computed column to allow ETL of data partition by partition for improved performance so we can’t even create a non-clustered computed column using other columns<=-=Jan 31 2017 1:19AM=-=>
Sorry, non-clustered columnstore index