Hi @Vyshnavi gv ,
Thank you for posting query in Microsoft Q&A Platform.
Usually Clustered Column store indexes are best for overall query performance. But as you mentioned these indexes will not support varchar(max).
If you can avoid having vcarchar(max), nvarchar(max) and varbinary(max) type columns in your table, then I will suggest to go with Clustered Column Store index.
If you cannot avoid above column types on your table then consider heap or clustered index instead.
If your table is small table with less than 60 million rows then considering heap is good option.
Good thing about Clustered indexes is that, they may outperform clustered columnstore tables when a single row needs to be quickly retrieved. For queries where a single or very few row lookup is required to perform with extreme speed, consider a clustered index or nonclustered secondary index.
The disadvantage to using a clustered index is that only queries that benefit are the ones that use a highly selective filter on the clustered index column. To improve filter on other columns, a nonclustered index can be added to other columns. However, each index that is added to a table adds both space and processing time to loads.
So, I feel clustered index will be better choice in your case as data is going to be more than 60 millions and your table is going to contain varchar(max) columns too. And if need to improve performance on other columns too then consider having nonclustered index on other columns.
Click here to know more about table indexes in Synapse.
Hope this will help. Please let us know if any further queries.
Please consider hitting
Accept Answer button. Accepted answers helps community as well.
Okay, let me go with clustered index and i will monitor the table performance