Best possible ways to disable index before insert operation and enable back Index after insert

sql-server indexing sql-server-2014 columnstore
Jay Nani picture Jay Nani · Nov 11, 2016 · Viewed 7.2k times · Source

I'm planning to create a Non-clustered columnstore index on SQL Server 2014.

But non clustered column store index is read-only and cannot perform DML operations, we need to disable before insert and enable back after insert.

What are the best possible ways to perform this task?

Answer

Derrick Moeller picture Derrick Moeller · Nov 11, 2016

Simply disable the index and perform a rebuild. https://msdn.microsoft.com/en-us/library/dn589806(v=sql.120).aspx

ALTER INDEX mycolumnstoreindex ON mytable DISABLE;  
-- update mytable --  
ALTER INDEX mycolumnstoreindex on mytable REBUILD

Related questions

What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
List of all index & index columns in SQL Server DB

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on …

Read More
Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More