1

I use SQL Server 2012.

I want know when I want to know in what situation I have to use CREATE NONCLUSTERED COLUMNSTORE INDEX .... and in what situation I have to use Create NonClustered Index... Include and in what situation I have to use Create NonClustered Index... with all columns.

What are the advantage and disadvantage relative to the other

Exemple :

Use [AdventureWorksDW2012]
Go

CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales
ON dbo.FactResellerSales
(ProductKey, UnitPrice, CustomerPONumber, OrderDate);

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;

enter image description here

Drop Index csi_FactResellerSales On [dbo].[FactResellerSales] 
Create NonClustered Index Ardalan
    On [dbo].[FactResellerSales] (ProductKey) include(UnitPrice, CustomerPONumber, 
                                                      OrderDate)

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;

enter image description here

Drop Index Ardalan On [dbo].[FactResellerSales]
Create NonClustered Index Ardalan1
    On [dbo].[FactResellerSales] (ProductKey,UnitPrice, CustomerPONumber, OrderDate)

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate
FROM dbo.FactResellerSales;
Drop Index Ardalan1 On [dbo].[FactResellerSales]

enter image description here

2

I think you are already on the right track here - and I encourage you to keep experimenting with different options and see for yourself what the differences to performance are. But, here are my thoughts below:

Columnstore Indexes
These perform really well when you are genuinely interested in all (or most) of the data in a column. If you are keen to see the distribution of data in a column, e.g. number of sales per order or location data, then a column store is a highly performant way to access the whole column.

Writing to columnstore indexes is slower than writing to a row. I would not use a columnstore index if you have a high-velocity OLTP system. Would also avoid columnstore indexes if you are interested in filtering down to small result sets.

Covering Indexes
Use these when you have wide tables and queries that regularly access a small number of the columns in the table. Covering indexes are a superb way to remove clustered index scans, reduce your overall IO and improve concurrency.

Covering indexes over all the columns of a table
Never. I cannot see a good reason to do this. Am happy to be corrected though. Perhaps... maybe... still not convinced... you might do this if you are implementing something like a filtered index. This would essentially chunk you table into discrete ranges. But I am not convinced this is a good method.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.