Why does the presence of a primary key in a table significantly increase the performance of column indexes?

Question

I tried to see how performance column index indices can appear on a table. The table has about 3.7 million rows, 11 columns and is stored as a heap (without a primary key). I create the index of the storage table in the table and run the following query:

SELECT 
    [Area], [Family],
    AVG([Global Sales Value]) AS [Average GlobalSalesValue],
    COUNT([Projected Sales])
FROM 
    dbo.copy_Global_Previous5FullYearSales
WHERE 
    [Year] > 2012  
GROUP BY 
    [Area], [Family]

The create table statement looks like this:

CREATE TABLE [dbo].[copy_Global_Previous5FullYearSales]
(
    [SBU] [NVARCHAR](10) NULL,
    [Year] [INT] NULL,
    [Global Sales Value] [MONEY] NULL,
    [Area] [NVARCHAR](50) NULL,
    [Sub Area] [NVARCHAR](50) NULL,
    [Projected Sales] [MONEY] NULL,
    [Family] [NVARCHAR](50) NULL,
    [Sub Family 1] [NVARCHAR](50) NULL,
    [Sub Family 2] [NVARCHAR](50) NULL,
    [Manufacturer] [NVARCHAR](40) NULL,
    [rowguid] [UNIQUEIDENTIFIER] NOT NULL,
    [ID] [INT] IDENTITY(1,1) NOT NULL,

    PRIMARY KEY CLUSTERED ([ID] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In this case, the performance obtained from the column store index is negligible. A query with a column store index is almost as slow as an original query without an index, in some cases even slower, although batch mode processing is also used.

, - , 15- 3- .

, , . , , .

Answer 1

a . , . , , :

. , , , . , , , , , .

- ID, , .