Sql-server – What should be the size of nonclustered index

clustered-indexindexnonclustered-indexsql server

I was experimenting with nonclustered indexes, especially their sizes. My aim was to understand how the amount of index keys in Clustered index affects the final size of Nonclustered index. I performed 4 scenarios. 3 of the them gave me a clue that by increasing the amount of index keys in clustered index will also increase the size of your nonclustered index because of the fact that clustered index keys plays a role of the row locator in all nonclustered indexes that we create.

--Table definition
CREATE TABLE [dbo].[FactOnlineSales2](
    [OnlineSalesKey] [int] IDENTITY(1,1) NOT NULL,
    [DateKey] [datetime] NOT NULL,
    [StoreKey] [int] NOT NULL,
    [ProductKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [CustomerKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar](20) NOT NULL,
    [SalesOrderLineNumber] [int] NULL,
    [SalesQuantity] [int] NOT NULL,
    [SalesAmount] [money] NOT NULL,
    [ReturnQuantity] [int] NOT NULL,
    [ReturnAmount] [money] NULL,
    [DiscountQuantity] [int] NULL,
    [DiscountAmount] [money] NULL,
    [TotalCost] [money] NOT NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
) ON [PRIMARY]

--Experiment 1

    --2023792 KB ~ 1976 MB
    create clustered index cx_index on [dbo].[FactOnlineSales2] (OnlineSalesKey)


    --743512 KB ~ 726 MB
    create nonclustered index ncx_1 on [dbo].[FactOnlineSales2] (SalesAmount, 
                                                                 ReturnQuantity, 
                                                                 ReturnAmount, 
                                                                 DiscountQuantity, 
                                                                 DiscountAmount, 
                                                                 TotalCost, 
                                                                 UnitCost)


--Experiment 2

    --2042032 KB ~ 1994 MB
    create clustered index cx_index on [dbo].[FactOnlineSales2] (OnlineSalesKey,
                                                                 DateKey,
                                                                 StoreKey,
                                                                 ProductKey,
                                                                 PromotionKey,
                                                                 CurrencyKey,
                                                                 CustomerKey,
                                                                 SalesOrderNumber,
                                                                 SalesOrderLineNumber,
                                                                 SalesQuantity)


    --1630808 KB ~ 1592 MB
    create nonclustered index ncx_2 on [dbo].[FactOnlineSales2] (SalesAmount, 
                                                                 ReturnQuantity, 
                                                                 ReturnAmount, 
                                                                 DiscountQuantity, 
                                                                 DiscountAmount, 
                                                                 TotalCost, 
                                                                 UnitCost)


--Experiment 3

    --2023784 KB ~ 1976 MB
    create clustered index cx_index on [dbo].[FactOnlineSales2] (OnlineSalesKey)


    --1630808 KB ~ 1592 MB
    create nonclustered index ncx_3 on [dbo].[FactOnlineSales2] (DateKey,
                                                                 StoreKey,
                                                                 ProductKey,
                                                                 PromotionKey,
                                                                 CurrencyKey,
                                                                 CustomerKey,
                                                                 SalesOrderNumber,
                                                                 SalesOrderLineNumber,
                                                                 SalesQuantity,
                                                                 SalesAmount, 
                                                                 ReturnQuantity, 
                                                                 ReturnAmount, 
                                                                 DiscountQuantity, 
                                                                 DiscountAmount, 
                                                                 TotalCost, 
                                                                 UnitCost)

enter image description here

enter image description here

enter image description here

In my 4th experiment I got confused. For the 4th experiment I created clustered index with the first 16 columns and then created nonclustered index with the same 16 columns. In this case the size of nonclustered index became 1492 MB which I didn't expect. I don't understand what is the catch in this experiment because I expected the size be higher than 1592 MB. Could someone explain this behavior?

--Experiment 4

    --2052616 KB ~ 2004 MB
    create clustered index cx_index on [dbo].[FactOnlineSales2] (OnlineSalesKey,
                                                                 DateKey,
                                                                 StoreKey,
                                                                 ProductKey,
                                                                 PromotionKey,
                                                                 CurrencyKey,
                                                                 CustomerKey,
                                                                 SalesOrderNumber,
                                                                 SalesOrderLineNumber,
                                                                 SalesQuantity,
                                                                 SalesAmount, 
                                                                 ReturnQuantity, 
                                                                 ReturnAmount, 
                                                                 DiscountQuantity, 
                                                                 DiscountAmount, 
                                                                 TotalCost)


    --1531832 KB ~ 1495 MB
    create nonclustered index ncx_4 on [dbo].[FactOnlineSales2] (OnlineSalesKey,
                                                                 DateKey,
                                                                 StoreKey,
                                                                 ProductKey,
                                                                 PromotionKey,
                                                                 CurrencyKey,
                                                                 CustomerKey,
                                                                 SalesOrderNumber,
                                                                 SalesOrderLineNumber,
                                                                 SalesQuantity,
                                                                 SalesAmount, 
                                                                 ReturnQuantity, 
                                                                 ReturnAmount, 
                                                                 DiscountQuantity, 
                                                                 DiscountAmount, 
                                                                 TotalCost)

Best Answer

please take a look at this.

How to estimate the size of a NCI, you will find all key factors playing role on the final index size.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-nonclustered-index?view=sql-server-ver15

Related Question