1

I created two scenarios and expected to see the same result but the results were noticeably different. I would like to know where I did wrong.

Scenario 1

Create table mytable1 with clustered index and nonclustered index on [OnlineSalesKey] and [DateKey] columns correspondingly. The total number of rows I inserted: 12627608 rows. Pay attention that [OnlineSalesKey] data type is INT:

enter image description here

create clustered index clustered_index_1 on [mytable1]([OnlineSalesKey])
create nonclustered index nonclustered_index_1 on [mytable1]([DateKey])

enter image description here

Scenario 2

Create table mytable1 with clustered index and nonclustered index on [OnlineSalesKey] and [DateKey] columns correspondingly. The total number of rows I inserted: 12627608 rows. Pay attention that [OnlineSalesKey] data type is INT:

enter image description here

create clustered index clustered_index_1_2 on [mytable1.2]([OnlineSalesKey])
create nonclustered index nonclustered_index_1_2 on [mytable1.2]([DateKey])

enter image description here

Expected result:

both nonclustered indexes should have the same size because clustered index column's data type is INT, so its size is 4 bytes.

Actual result:

enter image description here

Could you please explain why these sizes are so different?

UPDATED

scripts for creating tables:

CREATE TABLE [dbo].[mytable1](  
[OnlineSalesKey] [int] 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] 
GO
 
CREATE TABLE [dbo].[mytable1.2](    
[OnlineSalesKey] [int] 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] 
GO
3
  • Did you check the page fullness for those indexes' pages? I.e., using sys.dm_db_index_physical_stats(...). Sep 8 at 15:48
  • @TiborKaraszi yes. Firstly I created a heap and populated data and afterwards created the mentioned indexes. So, all pages are filled.
    – RaufDBA
    Sep 8 at 17:40
  • You could have half full pages, or particlly used extents. Without the output from the dmv I mentioned, we are left with speculation. Sep 8 at 17:58

Your Answer

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

Browse other questions tagged or ask your own question.