0

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)
5
  • Can you post the table/index definitions instead of screenshots? – bbaird Mar 30 at 13:12
  • 1
    If you can check the pages themselves, you'll probably find the secondary index doesn't duplicate columns shared with the clustered index. – bbaird Mar 30 at 13:15
  • You edit the question to include the table definition and each set of index definitions, otherwise there are variables at play we have to guess about. For instance the order of the keys in the CI and the NCI could make a difference to whether they need to be stored twice in the NCI. – David Spillett Mar 30 at 13:34
  • ok. I will post required definitions – RaufDBA Mar 30 at 13:39
  • @DavidSpillett done! – RaufDBA Mar 30 at 14:47
1

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

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.