table index for DISTINCT values

Question

In my stored procedure, I need "Unique" values of one of the columns. I am not sure if I should and if I should, what type of Index I should apply on the table for better performance. No being very specific, the same case happens when I retrieve distinct values of multiple columns. The column is of String(NVARCHAR) type.

e.g.

select DISTINCT Column1 FROM Table1;

OR

select DISTINCT Column1, Column2, Column3 FROM Table1;

e.g.

select DISTINCT Column1 FROM Table1;

OR

select DISTINCT Column1, Column2, Column3 FROM Table1;

user_7125938 · Answer

I recently had the same issue and found it could be overcome using a Columnstore index:

    CREATE NONCLUSTERED COLUMNSTORE INDEX [CI_TABLE1_Column1] ON [TABLE1]
    ([Column1])
    WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)

table index for DISTINCT values

2 Answers