jeudi 4 décembre 2014

Which Indexes in Witch situations is more efficacy in SQL Server 2012


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





Aucun commentaire:

Enregistrer un commentaire