Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

Question

My Table tbl_FieldeData_ColStore Data Example:

enter image description here

/NonClustred Column Store Index on Table/

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID )

enter image description here

/Below Select was using Non CLustered Column Store Index/

SELECT * FROM tbl_FieldeData_ColStore WHERE PDID = 46222 AND FieldID = 102582

Plan for Select Statement

https://www.brentozar.com/pastetheplan/?id=HJKsB0MT_

/ Not Using Above Created NonClustered ColumnStore Index, when i run below Update / Delete statement /

UPDATE tbl_FieldeData_ColStore SET InActive = 0 WHERE PDID = 46222 AND FieldID = 102582

DELETE tbl_FieldeData_ColStore WHERE PDID = 46222 AND FieldID = 102582

Plan for Update Statement:

https://www.brentozar.com/pastetheplan/?id=rkFADT-pu

Plan for Delete Statement:

https://www.brentozar.com/pastetheplan/?id=S1DCcpbpO

May I know Why This Happening With Non Clustered Column Store Index?

Is this way Non Clustered Column store Index Works?

Please Help me, Want to know How Non Clustered Column Store Index Works For Update / Delete Statements.

• J.D.: What index does it use instead? How many records does your example WHERE clause result in? Can you link the actual execution plan (you can use Paste The Plan) in both cases? (Please update your post with the answers to these questions.)
• gopikrishna: Query Updates / Deletes only Single Row, but it Results in Table Scan because i did not created clustered index on the table.
• gopikrishna: Added Plans for 2 Statements
• mustaccio: I don't think that a columnstore index is an appropriate option for your use case (single-row updates and deletes).
• gopikrishna: but i want to know why not using index with Update / Delete statements using for select statements. could you please tell me why ?

Answer 1

Based on the execution plans you provided, it appears that your table doesn't have a clustered index of any sorts, as noticeable by the RID Lookup operation. As a result, for the SELECT query, my guess is the SQL Engine is thinking using any available index to scan in conjunction with a RID Lookup would be the most performant operation for locating the data.

Conversely, for your data manipulation queries (the UPDATE and DELETE statements) the SQL Engine believes just a single Table Scan operation would be the better choice for the most efficient execution plan.

Why the SQL Engine made these choices is difficult to say based on the limited information we have to go off of, as it's based on your data, the statistics for that data, how your table is architected (e.g. what other indexes exist), and how the SQL Engine was programmed. Again, the only interesting thing I noticed is your table lacks a clustered index. Adding one (especially on the fields in your predicates, PDID and FieldID) might yield you different results.

Also, as mentioned in the comments, a columnstore index might not be the best choice for your use cases. Usually they're helpful for running OLAP type queries against large amounts of data, which doesn't seem to be your use case. You may want to consider completely switching from a nonclustered columnstore index to a regular clustered rowstore index.