The short answer is, of course, none of them, but testing is the only way to be sure.
I was asked, what happens when you run â€˜SELECT *â€™ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different).
Letâ€™s start with the simplest:
SELECTÂ Â Â * FROMÂ Â Â Production.ProductListPriceHistory AS plph;
This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered indexâ€¦ well, weâ€™ll have to cheat and itâ€™ll look silly, but letâ€™s be fair. Hereâ€™s my new index:
CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate);
When I rerun the query it results in an index scan, non-clustered, with 5 logical reads. Granted, this is stupid. Instead, letâ€™s do this. Weâ€™ll create a meaningful non-clustered index and then force itâ€™s use:
CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ListPrice);
Then run this:
SELECTÂ Â Â * FROMÂ Â Â Production.ProductListPriceHistory AS plph WITH (INDEX(TestIndex));
Now thatâ€™s using a non-clustered index in a â€˜SELECT *â€™ situation. It results in 794 logical reads and this execution plan:
Clearly, this is not an improvement. Finally, letâ€™s get rid of the non-clustered index and put this columnstore index in place:
CREATE NONCLUSTERED columnstore INDEX xtest ON production.ProductListPriceHistory (productid,startdate,enddate,listprice,modifieddate); GO
Now when I run the â€˜SELECT *â€™ query I have 37 reads and this execution plan:
This means the columnstore index is being used, but, if you look at the properties, youâ€™ll see that the execution mode on this one is Rows, which is not the preferred use youâ€™ll want out of a columnstore index. You want to see the execution mode be Batch.
If we stopped here, the answer is simple, a clustered index scan is better. But what if we add filtering? Letâ€™s modify the query to look like this:
SELECTÂ Â Â * FROMÂ Â Â Production.ProductListPriceHistory AS plph WHEREÂ Â Â plph.ListPrice = 23.5481;
Now, Iâ€™ll go back and set up the table so that it has just the clustered index, a non-clustered index, or the columnstore index. The results for the clustered index are identical. Since the column, ListPrice, is not part of the clustered key, a scan is necessary and the results are 5 reads and an execution time of about 1ms (the data is cached). The non-clustered index resulted in the same execution plan as before, but only 8 reads. But, the execution time was 42ms, so the added processing of getting the data put together from the key lookup was a little costly. Finally, the columnstore index results in 42 reads and an execution time of 4ms. The execution mode of the columnstore index was still Row.
Whatâ€™s all this mean? Not much since you shouldnâ€™t be using â€˜SELECT *â€™ anyway, but the main takeaway Iâ€™d suggest is that columnstore indexes are not magic. They donâ€™t replace traditional indexing. Further, if youâ€™re going to use them, be sure that youâ€™re really using them correctly. Just because you see the columnstore operator in the execution plan doesnâ€™t mean youâ€™re taking advantage of all the fantastic benefits they offer. Dive down into the properties and check the execution mode to ensure youâ€™re getting a Batch execution. Then youâ€™ll know that youâ€™re benefiting from the columnstore index.