2

I want to have an automatically updated structure in SQL Server that can handle aggregate queries well. Can I create an indexed view in SQL server 2014 that has a columnstore index as its clustered index?

uh_big_mike_boi
  • 3,350
  • 4
  • 33
  • 64

1 Answers1

3

You can't create a columnstore index on a view as per the documentation:

CREATE COLUMNSTORE INDEX (Transact-SQL)

In the limitations and restrictions it explicitly states:

"Cannot be created on a view or indexed view."

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • 2
    Dude that is specifically for NONCLUSTERED. – uh_big_mike_boi Jul 22 '16 at 14:52
  • 2
    It says nonclustered, but I think that may just be a typo, it is definitely the case that you can't create them, if you try to create a clustered columnstore index on a view you will get the error "The statement failed because a columnstore index cannot be created on a view. Consider creating a columnstore index on the base table or creating an index without the COLUMNSTORE keyword on the view." – steoleary Jul 22 '16 at 14:59
  • 2
    Starting with SQL Server 2016 (13.x), you can create a nonclustered columnstore index on an indexed view. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-columnstore-index-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 – Venkataraman R Feb 09 '21 at 16:06
  • That last comment should be the new answer – Henrov Feb 23 '21 at 07:44