I am mostly an older-school SQL Server guy now working in ASA. I have a very large (hundreds of millions of records) table and I want to create a few views for reporting analysts that filters things out, transforms a column or two, creates a calculated field, that kind of thing based on some business rules. I doubt that a standard view will give me the performance I'm looking for, so I started looking into materialized views.
First, I've seen them called both indexed views and materialized views. In ASA what's the right term, or is it both, and what's the difference?
Second, it looks like maybe you can only create a single clustered columnstore index. Is that true?
Third, I would like to be able to join to other tables on a few fields that typically I would create an index on in a traditional table. Can multiple non-clustered indexes be created like this for performance purposes?
Thanks so much!
